只有插入行（如果没有） [英] Only inserting a row if it's not already there

问题描述

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

I had always used something similar to the following to achieve it:

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

... ，发生主键冲突。这是唯一插入到这个表中的语句。那么这是否意味着上面的语句不是原子的？

...but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic?

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

The problem is that this is almost impossible to recreate at will.

也许我可以把它改成像下面这样的东西：

Perhaps I could change it to the something like the following:

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)

错误的锁或使用太多锁定或某事。

Although, maybe I'm using the wrong locks or using too much locking or something.

我已经看到stackoverflow.com的其他问题的答案建议IF（选择计数（*）... INSERT等，但我总是在一个单一的SQL语句将是原子的（也许不正确）假设。

I have seen other questions on stackoverflow.com where answers are suggesting a "IF (SELECT COUNT(*) ... INSERT" etc., but I was always under the (perhaps incorrect) assumption that a single SQL statement would be atomic.

有没有任何想法？

Does anyone have any ideas?

推荐答案

JFDI 模式？

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

很明显，这是最快的，并发无锁，特别是在大量。
如果UPDLOCK升级并且整个表被锁定，该怎么办？

Seriously, this is quickest and the most concurrent without locks, especially at high volumes. What if the UPDLOCK is escalated and the whole table is locked?

阅读第4课

这篇关于只有插入行（如果没有）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！