SQL Server使用子查询/CTE执行更新的竞态条件安全性对比

1. CTE的执行时机 #

CTE是所属UPDATE原子操作的一部分，并非定义时单独执行。在SQL Server中，可更新CTE本质是临时的视图别名，CTE的查询逻辑和后续的UPDATE操作会被整合到同一个执行计划中，整个语句是原子执行的，不存在CTE先查完、间隔一段时间再执行更新的情况。

2. 两种方案的竞态条件风险 #

两种方案在默认隔离级别（读提交）下的竞态风险完全一致：

• 默认情况下，查询行时加的共享锁会在读取完成后立即释放，若两个进程同时执行存储过程，可能同时读取到同批SyncRAGStatus = 'R'的行，最终会出现只有一个进程成功更新这些行，另一个进程更新行数为0的情况，极端场景下可能出现死锁。
• 若要完全避免竞态，两种方案都需要在查询部分加锁提示：WITH (UPDLOCK, READPAST, HOLDLOCK)，UPDLOCK会在读取时就加更新锁避免其他进程同时拿更新锁，READPAST会跳过已经被其他进程加锁的行不会阻塞，HOLDLOCK会保持锁到事务结束。

3. 两种方案的优劣对比 #

你倾向选择CTE方案是完全合理的，二者对比如下：

语法可读性 #

CTE方案优势明显：将筛选逻辑和更新逻辑拆分得更清晰，后续维护修改更方便，不容易出现表别名冲突（WHERE IN版本里子查询和外层UPDATE都用了PV作为PriceValues的别名，很容易出错）。

执行效率 #

多数场景下CTE方案执行效率更高：可更新CTE直接对筛选出的行做更新，不需要像WHERE IN版本那样再做一次外层PriceValues表和子查询结果的ID匹配，减少了一次表关联开销，查询优化器生成的执行计划也会更简洁。

功能扩展性 #

CTE方案扩展性更强：如果后续需要加更多筛选条件、或者需要更新关联表的字段，直接修改CTE的查询逻辑即可，不需要调整外层UPDATE的结构。

对应方案代码 #

WHERE IN版本 #

CREATE TABLE #RowsIWant (PriceValueId BIGINT)
    
UPDATE PV
SET SyncRAGStatus = 'A',
    AuditUser = pv.AuditUser
OUTPUT Inserted.Id INTO #RowsIWant
FROM PriceValues AS PV 
WHERE PV.Id IN (
    SELECT TOP (@batchSize) PV.Id
    FROM Prices AS P
    INNER JOIN PriceValues AS PV ON PV.PriceId = P.Id
    WHERE P.PriceListId = @priceListId
    AND PV.SyncRAGStatus = 'R'
    ORDER BY PV.UpdateInsertStatus DESC
)

CTE版本（添加锁提示避免竞态） #

;WITH TopNRowsINeed AS 
(
    SELECT TOP (@batchSize) PV.Id AS PriceValueId, PV.SyncRAGStatus, PV.AuditUser
    FROM Prices AS P WITH (UPDLOCK, READPAST, HOLDLOCK)
    INNER JOIN PriceValues  AS PV ON PV.PriceId = P.Id
    WHERE P.PriceListId = @priceListId 
      AND PV.SyncRAGStatus = 'R'
    ORDER BY PV.UpdateInsertStatus DESC
)
UPDATE TopNRowsINeed
SET SyncRAGStatus = 'A'
OUTPUT Inserted.PriceValueId INTO #RowsIWant

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