Azure SQL/SQL Server并发事务重复插入问题与锁机制应用咨询

问题回顾 #

原始事务流程存在并发冲突：

• 事务步骤：启动事务 → 更新指定ID的活跃行为非活跃 → 插入新行（audit_id为更新行的ID）→ 提交事务
• 并发问题：两个事务同时执行时，会重复更新同一行并插入两条audit_id相同的记录，最终数据如下：

id x_id is_active audit_id status
1   9    false     null     inprogress
2   9    true      1        done
3   9    true      1        done

你的方案分析 #

你提出的HOLDLOCK方案无法彻底解决问题：

• HOLDLOCK会为查询行添加共享锁并保持到事务结束，但共享锁允许多个事务同时持有。两个并发事务仍能同时执行步骤2的SELECT语句，拿到同一个ID，后续都会执行UPDATE和INSERT操作，最终还是会生成重复的audit_id记录。

• 你对UPDLOCK的理解有误：UPDLOCK会在SELECT时为行添加更新锁，更新锁与其他更新锁互斥。第一个事务执行SELECT id FROM tableA WHERE x_id = 1 AND is_active = true AND status = 'inprogress' WITH (UPDLOCK)后，第二个事务执行相同语句会被阻塞，直到第一个事务提交/回滚。待第一个事务完成UPDATE和INSERT并提交后，第二个事务的SELECT将无法找到符合is_active = true条件的行，自然不会执行后续的更新和插入，从根源避免重复。

优化后的可行方案 #

推荐两种更可靠的实现方式：

方式一：使用UPDLOCK（可选搭配HOLDLOCK） #

事务流程调整为：

1. 启动事务
2. 执行查询并加更新锁：

   SELECT id FROM tableA WHERE x_id = 1 AND is_active = true AND status = 'inprogress' WITH (UPDLOCK, HOLDLOCK)
   

   （HOLDLOCK用于防止幻读，确保事务期间不会有新的符合条件的行被插入，可选）
3. 如果查询到有效ID，执行更新：

   UPDATE tableA SET is_active = false WHERE id = [步骤2获取的ID]
   

4. 插入新行：

   INSERT INTO tableA (x_id, is_active, audit_id, status) VALUES (1, true, [步骤2获取的ID], 'done')
   

5. 提交事务

方式二：直接使用UPDATE+OUTPUT子句（更高效） #

无需单独SELECT，直接通过UPDATE获取目标ID并完成更新，减少锁的持有时间：

1. 启动事务
2. 执行更新并输出更新的ID：

   DECLARE @UpdatedId INT;
   UPDATE tableA 
   SET is_active = false 
   OUTPUT INSERTED.id INTO @UpdatedId
   WHERE x_id = 1 AND is_active = true AND status = 'inprogress';
   

3. 如果@UpdatedId有值，执行插入：

   INSERT INTO tableA (x_id, is_active, audit_id, status) VALUES (1, true, @UpdatedId, 'done')
   

4. 提交事务
   这种方式利用UPDATE的排他锁特性，确保同一时间只有一个事务能更新目标行，避免并发冲突。

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