仅在不存在的情况下插入一行 [英] 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 (SELECT COUNT(*) ... 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.

有人有什么想法吗?

推荐答案

"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 课:

第 4 课:在调整索引之前开发 upsert 过程时，我首先相信 If Exists(Select...) 行会为任何项目触发，并且禁止重复.纳达.在短时间内有数千个重复项，因为同一个项目会在同一毫秒内命中 upsert，并且两个事务都会看到不存在并执行插入.经过多次测试，解决方案是使用唯一索引，捕获错误，然后重试允许事务查看该行并执行更新而不是插入.

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.

这篇关于仅在不存在的情况下插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！