主键索引死锁问题排查与解决求助

死锁原因 #

你的死锁核心由两个关键因素共同导致：

1. Serializable隔离级别的范围锁机制：死锁图显示两个进程都使用了Serializable（序列化）隔离级别。这个级别为了防止幻读，会对查询涉及的索引范围添加RangeS-S（范围共享锁）。即使SELECT的目标是Division B的记录，如果查询计划采用索引扫描而非精准查找（Seek），它会锁定扫描路径上的所有索引键范围——包括Division A的行。而UPDATE进程在更新Division A的行时会添加X（排他锁），两种锁类型互斥，形成等待基础。
2. 循环更新拉长锁持有时间：UPDATE进程在单个事务内循环更新多条记录，持续持有X锁的时间被拉长，大幅提升了锁冲突概率。加上SELECT的范围锁意外覆盖了UPDATE的目标行，最终形成循环等待链：SELECT持有部分Division A行的RangeS-S锁，等待UPDATE持有的其他行的X锁；UPDATE持有部分Division A行的X锁，等待SELECT持有的其他行的RangeS-S锁，触发死锁。

解决方案 #

• 降低隔离级别：将Serializable替换为Read Committed（SQL Server默认隔离级别）；若需避免读脏数据，开启数据库的READ_COMMITTED_SNAPSHOT选项。此配置下SELECT会通过行版本读取数据，不会添加共享锁，从根源消除锁冲突。
• 优化查询计划：为SELECT查询创建覆盖索引，确保数据库能通过索引精准定位目标行（Seek）而非全索引扫描。例如针对存储过程中的查询，创建包含Requestor_Division、Requestor_Section、Item_Division及所有输出列的非聚集索引，缩小锁的覆盖范围。
• 替换循环更新为批量操作：把逐条循环更新的逻辑改为单条UPDATE语句批量处理所有目标行，减少事务内锁的持有时间和冲突点：

BEGIN TRAN
UPDATE request_table 
SET status = 'Acknowledged' 
WHERE Requestor_Division = 'Division A' 
  AND Requestor_Section = 'Section 1' 
  AND Item_Division = 'Division Z'  
  AND Item_Code IN ('ItemA', 'ItemB', 'ItemC') -- 批量指定需更新的Item_Code
COMMIT TRAN

• 缩短事务生命周期：确保事务仅包含必要的数据库操作，避免在事务内执行非数据库逻辑，快速提交事务以减少锁的持有时长。

内容的提问来源于stack exchange，提问作者Kenneth.F