使用CTE避免SQL Server唯一约束冲突：并发执行是否保证一致性？

我在dbo.tb_Department表的department列上设置了唯一约束，希望执行更新操作时，若目标department值已存在于其他行中，则不执行更新，避免触发唯一约束冲突错误。现使用如下CTE实现该逻辑，请问当多个用户/连接同时执行这段代码时，是否能保证数据一致性？

declare @piDepartment varchar(20) = 'test' 
declare @piDepartmentSid int = 1105; 

WITH CTE as ( 
    select top 1 1 [exists] 
    from dbo.tb_Department 
    where Department = @piDepartment 
      and [Department_SID] <> @piDepartmentSid 
) 
update D set Department = @piDepartment 
FROM dbo.tb_Department D 
left join CTE on 1 = CTE.[exists] 
where D.Department_SID = @piDepartmentSid 
  and CTE.[exists] is null;

很遗憾，这段代码没办法在高并发场景下保证数据一致性，依然有可能触发唯一约束冲突的错误。

问题根源：竞态条件 #

你的CTE查询和后续的更新操作不是原子性的——两者之间存在一个时间窗口。举个典型的并发场景：

1. 会话A执行CTE查询，发现目标department值不存在于其他行，准备执行更新；
2. 就在会话A的更新完成前，会话B也执行了同样的CTE查询，此时会话A的更新还没落地，所以会话B也判定目标值可用；
3. 两个会话先后执行更新操作，最终因为违反唯一约束报错。

这种情况就是典型的竞态条件（Race Condition），因为查询和更新的分离给了其他会话插入/修改数据的机会。

可靠的解决方案 #

想要彻底避免并发下的约束冲突，有两种更稳妥的方式：

方案1：直接尝试更新，捕获约束冲突错误

虽然看起来有点“简单粗暴”，但数据库本身的约束是最可靠的最后一道防线。我们可以用TRY/CATCH块捕获唯一约束的错误：

BEGIN TRY
    UPDATE dbo.tb_Department
    SET Department = @piDepartment
    WHERE Department_SID = @piDepartmentSid;
END TRY
BEGIN CATCH
    -- 唯一约束冲突的错误号是2601或2627
    IF ERROR_NUMBER() IN (2601, 2627)
    BEGIN
        PRINT '该部门名称已被其他行使用，无法完成更新';
        -- 这里也可以添加日志记录或返回自定义提示
    END
    ELSE
    BEGIN
        -- 非约束类错误，重新抛出
        THROW;
    END
END CATCH

方案2：加锁提示确保查询与更新的原子性

通过UPDLOCK和HOLDLOCK锁提示，让CTE查询时就锁定相关数据，直到整个事务结束，避免其他会话在间隙中修改数据：

DECLARE @piDepartment varchar(20) = 'test';
DECLARE @piDepartmentSid int = 1105;

WITH CTE AS (
    SELECT 1 [exists]
    FROM dbo.tb_Department WITH (UPDLOCK, HOLDLOCK)
    WHERE Department = @piDepartment 
      AND [Department_SID] <> @piDepartmentSid
)
UPDATE D 
SET Department = @piDepartment 
FROM dbo.tb_Department D
LEFT JOIN CTE ON 1 = CTE.[exists]
WHERE D.Department_SID = @piDepartmentSid 
  AND CTE.[exists] IS NULL;

• UPDLOCK：获取更新锁，阻止其他会话对这些行做修改；
• HOLDLOCK：将锁的持有时间延长到事务结束，确保查询到更新的整个过程中数据不会被篡改。

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