为何带重复排除逻辑的SQL插入仍触发主键约束冲突？

在多用户并发场景下，你的插入语句存在竞态条件，具体流程如下：

1. 事务A执行查询部分（where subQuery.ID not in (...)），此时查到一批可插入的ID，但还未执行插入操作。
2. 同一时间事务B执行相同的查询，由于事务A的插入尚未提交，事务B也会查到和事务A完全相同的那批ID。
3. 事务A先完成插入并提交，随后事务B执行插入时，就会因主键重复抛出约束异常。
   你的where子句只能保证查询瞬间没有重复数据，但无法保证从查询到插入的这段窗口时间内，其他事务不会插入相同的主键组合。

1. 使用MERGE原子操作（优先推荐） #

MERGE语句可以在同一个原子步骤中完成"检查是否存在+插入"的操作，数据库会自动处理并发锁定，避免竞态条件：

MERGE INTO ResultsStore AS target
USING (
    -- 替换为你的大型复杂慢查询
    SELECT subQuery.ID, @bar AS Bar
    FROM (/* large complex slow query */) subQuery
) AS source (Foo, Bar)
ON target.Foo = source.Foo AND target.Bar = source.Bar
WHEN NOT MATCHED THEN
    INSERT (Foo, Bar) VALUES (source.Foo, source.Bar)
OUTPUT inserted.*;

2. 添加锁提示阻止并发写入 #

在查询ResultsStore时添加UPDLOCK和HOLDLOCK锁提示，确保事务期间锁定符合条件的行，阻止其他事务插入相同的主键组合：

INSERT ResultsStore (Foo, Bar) 
OUTPUT inserted.* 
SELECT subQuery.ID, @bar
FROM (/* large complex slow query */) subQuery
WHERE subQuery.ID NOT IN ( 
    SELECT Foo 
    FROM ResultsStore WITH (UPDLOCK, HOLDLOCK)
    WHERE Bar = @bar 
);

• UPDLOCK：获取更新锁，允许其他事务读取但阻止修改或加更新锁
• HOLDLOCK：将锁持有到事务结束，避免幻读问题

3. 捕获主键冲突异常（兜底方案） #

如果并发冲突频率不高，且不想影响原有查询性能，可以在SQL中捕获主键冲突异常（错误码2627），忽略重复插入的情况：

BEGIN TRY
    INSERT ResultsStore (Foo, Bar) 
    OUTPUT inserted.* 
    SELECT subQuery.ID, @bar
    FROM (/* large complex slow query */) subQuery
    WHERE subQuery.ID NOT IN ( 
        SELECT Foo 
        FROM ResultsStore 
        WHERE Bar = @bar 
    );
END TRY
BEGIN CATCH
    -- 仅处理主键冲突，其他异常正常抛出
    IF ERROR_NUMBER() = 2627
        PRINT '已忽略重复的主键组合插入'
    ELSE
        THROW;
END CATCH

4. 显式事务配合锁提示 #

将整个操作包装在显式事务中，结合锁提示延长锁持有时间，确保查询和插入在同一个事务周期内完成：

BEGIN TRANSACTION;

INSERT ResultsStore (Foo, Bar) 
OUTPUT inserted.* 
SELECT subQuery.ID, @bar
FROM (/* large complex slow query */) subQuery
WHERE subQuery.ID NOT IN ( 
    SELECT Foo 
    FROM ResultsStore WITH (UPDLOCK, HOLDLOCK)
    WHERE Bar = @bar 
);

COMMIT TRANSACTION;

注意：如果你的慢查询耗时较长，这种方法会导致锁持有时间过长，可能影响其他业务的并发性能，需谨慎使用。

内容的提问来源于stack exchange，提问作者Keith