SQL Server表并发插入更新时如何改写UPDATE避免阻塞插入操作

• 原语句中加在Tab上的NOLOCK提示对写操作的锁行为完全无效：该提示仅作用于语句的源数据读取、生成结果集阶段，UPDATE执行到实际修改数据的环节时，必然对被修改的目标行加排他锁，无法通过NOLOCK提示规避核心锁逻辑。
• 真正的阻塞来源是扫描阶段的锁范围冲突：原语句的过滤条件A.Status IS NULL会引导优化器选择IX_Status非聚集索引作为Tab的访问路径，对该索引上所有Status为NULL的键范围加更新锁（U锁）。而P1批量插入的新记录Status默认为NULL，插入时必然要往IX_Status的同一范围写入新索引键，哪怕P1、P2操作的主键完全不重叠，只要索引键落在同一个加U锁的页/锁范围内，就会产生锁等待。

核心思路是彻底避开扫描IX_Status的大范围索引区间，通过聚集主键索引精准定位待更新的行，最小化锁的持有范围和时间：

• 调整关联顺序，先从Buf表拿到所有待处理的目标Id集合，不提前触碰Tab的非聚集索引
• 强制Tab的关联走聚集主键索引的精准Seek，杜绝大范围扫描加U锁的行为
• 加行锁提示避免锁升级，进一步缩小锁的影响范围

;WITH TargetBufIds AS (
    -- 仅扫描Buf表的主键拿待处理Id，无Tab表扫描逻辑
    SELECT Id
    FROM dbo.Buf WITH (READUNCOMMITTED)
)
UPDATE A
SET A.Status = 1
FROM dbo.Tab A WITH (ROWLOCK, INDEX = PK_Id) -- 强制走主键聚集索引、锁粒度为行
INNER JOIN TargetBufIds B
    ON A.Id = B.Id -- 等值匹配直接触发聚集索引Seek，定位具体行
WHERE A.Status IS NULL

改写后的效果 #

• 执行计划层面完全取消了对IX_Status索引的扫描动作，所有Tab表的访问都通过聚集主键索引的单点查找完成，根本不会在IX_Status的公共插入范围提前加U锁，从根源上消除对P1插入操作的阻塞。
• 锁范围从“覆盖所有Status为NULL的大范围索引页”缩小到“和Buf Id精准匹配的零散行键锁”，且这些键的位置分散在聚集索引结构中，和P1插入新行的写入路径不存在锁范围重叠。

• 当Buf表单次待处理的Id量较大时，可分批提取Id执行更新（比如每次处理1000条），进一步缩短单条语句的锁持有时间，降低锁冲突概率。
• 不要为了规避锁冲突随意给UPDATE加NOLOCK提示，这类提示无法规避写操作的必要排他锁，还可能导致数据一致性问题（比如重复更新、跳过符合条件的行）。

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