SQL Server并发更新异常：为何会重复更新同一ID？

这个问题的核心是并发场景下的事务隔离级别和子查询的执行逻辑，我来帮你拆解清楚：

问题根源 #

你的UPDATE语句里的子查询SELECT TOP (1) id FROM TABLE WHERE STATUS = 0 AND NOT EXISTS(SELECT * FROM TABLE WHERE STATUS = 1)...，在SQL Server默认的READ COMMITTED隔离级别下，两个并发执行的事务会各自独立执行这个子查询。

当第一次UPDATE还没提交事务时，它修改的status=1的记录对第二个事务是不可见的（因为READ COMMITTED只能看到已提交的数据）。所以第二个事务的子查询会依然认为表中没有status=1的记录，同时所有id的status都是0，于是两次子查询都返回了id=1，最终导致两次UPDATE都修改了同一条记录。

这里要澄清：UPDATE本身是具备事务性的，但问题出在并发时子查询的读取没有加锁，导致两个事务获取了相同的目标记录。

解决方案 #

要解决这个并发竞争问题，你需要在子查询中添加锁提示，确保同一时间只有一个事务能选中并修改目标记录，常见的两种方式：

方式1：给子查询添加UPDLOCK, HOLDLOCK锁提示 #

修改后的SQL语句如下：

UPDATE TABLE 
SET status = 1, mod = GETDATE() 
OUTPUT INSERTED.id 
WHERE id = ( 
    SELECT TOP (1) id 
    FROM TABLE WITH (UPDLOCK, HOLDLOCK)
    WHERE STATUS = 0 
      AND NOT EXISTS(SELECT * FROM TABLE WHERE STATUS = 1) 
      AND COALESCE(runat, GETDATE()) <= GETDATE() 
    ORDER BY ID ASC
)

• UPDLOCK：给选中的记录添加更新锁，其他事务无法对这些记录加更新锁或排他锁，只能读取，直到当前事务提交或回滚。
• HOLDLOCK：等同于SERIALIZABLE隔离级别的范围锁，会锁定查询的范围，防止其他事务插入/修改符合条件的记录，确保子查询的结果在事务期间不会变化。

方式2：使用CTE+UPDATE TOP的原子操作 #

另一种更直观的写法是用CTE来包装要更新的记录，避免子查询的竞争：

WITH TargetRecords AS (
    SELECT TOP (1) id, status, mod 
    FROM TABLE WITH (UPDLOCK, HOLDLOCK)
    WHERE STATUS = 0 
      AND NOT EXISTS(SELECT * FROM TABLE WHERE STATUS = 1) 
      AND COALESCE(runat, GETDATE()) <= GETDATE() 
    ORDER BY ID ASC
)
UPDATE TargetRecords 
SET status = 1, mod = GETDATE() 
OUTPUT INSERTED.id

这种方式同样通过锁提示确保并发安全，同时逻辑更清晰。

验证效果 #

当你用以上两种方式执行并发UPDATE时，第一个事务会锁定id=1的记录并完成更新，第二个事务的子查询会因为锁的存在，要么等待第一个事务提交后，发现已经有status=1的记录，从而返回null；要么等待期间无法选中id=1，也找不到其他符合条件的记录，最终返回null。

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