仅在行不存在时才插入行

2022-01-30 00:00:00 sql concurrency locking tsql sql-server

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

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 课:在调整索引之前开发 upsert proc 时，我首先相信 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.

相关文章