sql - 仅在行尚不存在时插入行

Question

我一直使用类似于以下的东西来实现它:

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 语句会是原子的。

有人有什么想法吗？

Answer 1

"JFDI"怎么样？模式？

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

说真的，这是最快、最并发的无锁方式，尤其是在高容量的情况下。如果UPDLOCK升级并且整个表被锁定怎么办？

Read lesson 4 :

Lesson 4: When developing the upsert proc prior to tuning the indexes, I first trusted that the If Exists(Select…) line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.