SQL UPDATE语句WHERE子句如何读取脏值以规避多进程更新锁等待

• READ UNCOMMITTED 事务隔离级别仅对读操作生效，允许读取未提交的脏数据，但不会改变DML（更新/删除）操作的锁申请逻辑
• UPDATE语句执行时需要先扫描符合WHERE条件的行，对所有命中的行申请排他锁，默认遇到已经被其他事务锁定的行时会进入等待状态，直到锁释放
• 第一个事务已经锁定了所有执行时刻存在的status='Waiting'的行，第二个事务扫描到这些行时就会触发等待，不会继续处理后续新增的符合条件的行

方案1：使用READPAST查询提示（最适配多进程作业队列场景） #

READPAST提示会让SQL Server在扫描数据时直接跳过已经被其他事务锁定的行，不会进入等待，直接处理未被锁定的符合条件的行（包括第一个事务启动后新增的status='Waiting'的行），完全符合多进程并行拾取作业的需求。

修改后的代码如下：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- 无需使用READ UNCOMMITTED
BEGIN TRANSACTION
-- 新增READPAST、ROWLOCK查询提示
UPDATE dbo.Jobs WITH (READPAST, ROWLOCK)
SET [status] = 'Running'
OUTPUT Inserted.*
WHERE [status] = 'Waiting'
-- 业务逻辑执行完成后提交事务
COMMIT TRANSACTION

补充说明：

• ROWLOCK提示是建议SQL Server使用行级锁，避免锁升级到页锁/表锁导致更多不必要的等待，适合小批量拾取作业的场景
• 如果每次只需要拾取少量作业，可以加上TOP (N)限制更新行数，进一步降低锁的影响，示例：UPDATE TOP (10) dbo.Jobs WITH (READPAST, ROWLOCK)

方案2：新增筛选索引优化性能 #

如果Jobs表数据量很大，可以针对待拾取的作业创建筛选索引，加快UPDATE的扫描速度，减少锁的持有时间：

CREATE NONCLUSTERED INDEX IX_Jobs_Status_Waiting ON dbo.Jobs ([status])
INCLUDE (/* 你需要OUTPUT返回的其他列 */)
WHERE [status] = 'Waiting'

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