仅在尚未插入的行插入

我一直使用类似于以下内容的方法来实现它：

INSERT INTO TheTable
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WHERE
        PrimaryKey = @primaryKey)

...但是一旦加载，就会发生主键冲突。这是唯一插入到该表中的唯一语句。那么这是否意味着上面的声明不是原子的？

问题是几乎不可能随意重新创建。

也许我可以将其更改为类似以下内容：

INSERT INTO TheTable
WITH
    (HOLDLOCK,
    UPDLOCK,
    ROWLOCK)
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WITH
        (HOLDLOCK,
        UPDLOCK,
        ROWLOCK)
    WHERE
        PrimaryKey = @primaryKey)

虽然，也许我使用了错误的锁或使用了过多的锁之类的东西。

我见过其他关于stackoverflow.com的问题，其中的答案提示“ IF（SELECT COUNT（*）... INSERT”等），但我始终处于（也许不正确的）假设中，即单个SQL语句是原子的。
有人有什么想法吗？

您是否尝试过使用不带WHEN MATCHED子句的合并？

您正在使用哪个版本的SQL Server？

它取决于客户端。 2000 R2和2008 R2之间的任何值。尽管最初编写该声明时我们可能已经是7岁了！

我必须看看这个新的（对我来说）MERGE语句。在这种情况下，效果是否更好？

我看不出重点！只需插入您的数据，如果PK已经存在，则插入将失败，并且可以。还是我想念什么？

#1 楼

那么“ JFDI”模式又如何呢？

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

严重的是，这是最快且最无锁的并发操作，尤其是在大容量情况下。
如果UPDLOCK会怎样？

阅读第4课：

第4课：在调整索引之前开发upsert proc时，我首先信任该If Exists(Select…)行将触发任何项目，并禁止重复。娜达短时间内有数千个重复项，因为同一项将在同一毫秒内达到高价，并且两个事务都将看到不存在并执行插入。经过大量测试之后，解决方案是使用唯一索引，捕获错误，然后重试以使事务查看行并执行更新，而不是插入。

谢谢-好的，我同意这可能是我最终将要使用的，并且是对实际问题的答案。

–亚当
2010年8月4日在19:58

我知道依靠这样的错误是不好的，但是我想知道仅使用直接的INSERT（不使用EXISTS）这样做是否会更好（即无论如何尝试插入并忽略错误2627）。

–亚当
2010年8月4日在20:02

这取决于您是插入大部分不存在的值还是插入大多数存在的值。在后一种情况下，我认为由于将引发和忽略大量异常，因此性能会较差。

–GSerg
2010年8月4日在21:11

@Gserg：正确。但是，OP可能会发布一个INSERT / UPDATE问题，而不是测试惰性。我们用它每天过滤掉几千万个新行中的数千个重复项

– gbn
2010年8月5日下午4:19

@学生35k tps =“每秒35000个事务”。通过捕获唯一约束冲突错误（错误号2627）并忽略它，TRY CATCH防止插入重复的条目。如果不是2627，则CATCH只会抛出该错误。此代码段存在问题，因为唯一索引冲突是错误2601。因此，您必须检查这两个代码。该解决方案也仅适用于单行INSERT。如果尝试从一个表插入到另一个表，则需要不同的策略。

– Jim
16-11-25在11:53

#2 楼

我添加了最初不存在的HOLDLOCK。请忽略没有该提示的版本。

就我而言，这应该足够了：

INSERT INTO TheTable 
SELECT 
    @primaryKey, 
    @value1, 
    @value2 
WHERE 
    NOT EXISTS 
    (SELECT 0
     FROM TheTable WITH (UPDLOCK, HOLDLOCK)
     WHERE PrimaryKey = @primaryKey)

此外，如果您实际上想更新一行（如果存在），如果不行则插入，您可能会发现此问题很有用。

当该行不存在时，您要锁定什么？

–马丁·史密斯
2010年8月4日在17:04

索引中的相关范围（在这种情况下为主键）。

–GSerg
2010年8月4日在17:05

@GSerg同意。 select语句的悲观/乐观锁定需要一个指令。

– DaveWilliamson
2010年8月4日在17:06

测试表明，两个选择“完成”。在哪里存在（从foo_testing中选择0（其中id = 4的updlock）；如果id = 4不存在，则不要互相冲突，这意味着我的原始答案实际上是错误的。解决方案是添加HOLDLOCK提示。查看编辑后的答案。感谢您让我烦恼:)

–GSerg
2010年8月5日，12：01

在Daniel对我的问题（非常相似）的回答中，有一个很好的解释说明为什么需要此锁定：stackoverflow.com/questions/3789287/…

–伊恩
2010-09-25 8:35

#3 楼

您可以使用MERGE：

MERGE INTO Target
USING (VALUES (@primaryKey, @value1, @value2)) Source (key, value1, value2)
ON Target.key = Source.key
WHEN MATCHED THEN
    UPDATE SET value1 = Source.value1, value2 = Source.value2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, ReasonType) VALUES (@primaryKey, @value1, @value2)

在这种情况下，您可以删除“匹配时”，因为亚当只需要在丢失时插入，而无需重新插入。

–伊恩
2011年4月21日在16:22

抱歉，但是没有在您的merge语句中添加保持锁定提示，您将遇到OP所关心的确切问题。

– EBarr
2011年6月11日在21:53

有关@EBarr的观点，请参见本文

–马丁·史密斯
2011-12-29 16:03

@MartinSmith-这是我遇到此问题时所读的确切文章！感谢您的参考。

– EBarr
2011-12-30在2:04

MSDN文档指出（在性能提示中），除非需要复杂性，否则应在不存在的地方使用插入而不是合并，除非需要复杂...msdn.microsoft.com/zh-cn/library/…

–默登·米哈伊洛维奇（Mladen Mihajlovic）
17年1月27日在13:44

#4 楼

我不知道这是否是“官方”方式，但是您可以尝试INSERT，如果失败则退回到UPDATE。

#5 楼

首先，对我们的人@gbn对社区的贡献大声疾呼。甚至无法开始解释我听他的建议的频率。

无论如何，足够的狂热爱好者。

要稍微补充一下他的回答，也许可以“增强”它。对于像我这样的人，在<> 2627场景中该做什么感到不安（并且没有空的CATCH是不可行的选择）。我从technet找到了这个小块。

    BEGIN TRY
       INSERT etc
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627
          BEGIN
                DECLARE @ErrorMessage NVARCHAR(4000);
                DECLARE @ErrorSeverity INT;
                DECLARE @ErrorState INT;

                SELECT @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

                    RAISERROR (
                        @ErrorMessage,
                        @ErrorSeverity,
                        @ErrorState
                    );
          END
    END CATCH

这恰好是我在上一个答案中没有方向的地方。 +1给你们俩！

– T0t3sMcG0t3s
18年5月5日在15:04

#6 楼

我过去使用不同的方法进行了类似的操作。首先，我声明一个变量来保存主键。然后，我使用select语句的输出填充该变量，该语句查找包含这些值的记录。然后我和IF做声明。如果主键为null，则插入，否则返回一些错误代码。

     DECLARE @existing varchar(10)
    SET @existing = (SELECT primaryKey FROM TABLE WHERE param1field = @param1 AND param2field = @param2)

    IF @existing is not null
    BEGIN
    INSERT INTO Table(param1Field, param2Field) VALUES(param1, param2)
    END
    ELSE
    Return 0
END

为什么不这样做：如果不存在（从表中选择*，那么param1field = @ param1和param2field = @ param2）开始插入表（param1Field，param2Field）VALUES（param1，param2）END

– Vidar Nordnes
2010年8月4日在17:29

是的，但是这似乎对并发问题开放（即，如果您的选择和插入之间的另一个连接发生了什么怎么办？）

–亚当
2010年8月4日在17:31

上面的@Adam Marc的代码对于避免锁定问题没有任何好处。处理并发问题的唯一两种方法是使用WITH（UPDLOCK，HOLDLOCK）进行锁定或处理插入错误并将其转换为更新。

– ErikE
10-10-2在1:06

编程黑洞网