两张无关联表间发生死锁问题求助

问题背景 #

Test.dbo.Stats与Test.dbo.Rates两张表仅存在同名的Code字段，无外键关联且无触发器，但偶尔发生死锁。死锁涉及以下两个索引：

• UIXC_Stats：Stats表的唯一聚集索引，包含Code等多个字段
• PK_Rates：Rates表的聚集主键，包含Code等两个字段

死锁结构图 #

<deadlock>
  <victim-list>
    <victimProcess id="process1c48ce29468" />
  </victim-list>
  <process-list>
    <process id="process1c48ce29468" taskpriority="0" logused="2628" waitresource="KEY: 7:72057595777318912 (a48b1d843aaa)" waittime="4028" ownerId="67284120064" transactionname="UPDATE" lasttranstarted="2023-09-19T05:00:03.417" XDES="0x1c56475c460" lockMode="RangeS-S" schedulerid="2" kpid="12224" status="suspended" spid="173" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-19T05:00:03.417" lastbatchcompleted="2023-09-19T05:00:03.417" lastattention="1900-01-01T00:00:00.417" clientapp="Node1" hostname="NG-LOT-B1" hostpid="6315" loginname="NEO\CGS_Backend_UI" isolationlevel="read committed (2)" xactid="67284120064" currentdb="7" currentdbname="Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="Test.dbo.procRatesSet" line="157" stmtstart="10644" stmtend="11418" sqlhandle="0x0300070094bb7a6b44b071006cb0000001000000000000000">
UPDATE  cr 
    SET     Rate            = new.Rate, 
            OperatorID      = new.OperatorID, 
            LastUpdateDate  = new.LastUpdateDate
    FROM    dbo.Rates AS cr
            INNER JOIN (    SELECT  RateDay, Code, Rate, OperatorID, LastUpdateDate
                            FROM    @ToUpdate AS cr 
                                    CROSS JOIN @DaysToUpdate AS d
                        ) AS new ON cr.RateDay = new.RateDay AND cr.Code = new.Code    
        </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 7 Object Id = 1803205524]   
      </inputbuf>
    </process>
    <process id="process1c48ce3dc28" taskpriority="0" logused="24108" waitresource="KEY: 7:72057595988475904 (71ffa95c3f24)" waittime="4023" ownerId="67284113571" transactionname="MERGE" lasttranstarted="2023-09-19T05:00:02.947" XDES="0x1c55bbf8460" lockMode="S" schedulerid="4" kpid="12240" status="suspended" spid="124" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-19T05:00:02.700" lastbatchcompleted="2023-09-19T05:00:02.700" lastattention="1900-01-01T00:00:00.700" clientapp="SQLAgent - TSQL JobStep (Job 0x277A6ABC1DF7 : Step 3)" hostname="NG-LOT-S1" hostpid="1235" loginname="NEO\sqlservice" isolationlevel="read committed (2)" xactid="67284113571" currentdb="7" currentdbname="Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="Test.dbo.procStatsMerge" line="47" stmtstart="2846" stmtend="16400" sqlhandle="0x0300070078a7821287967e0062af">
WITH DailyStatisticsSource AS
    (
        SELECT  ID,Reference,Code,Total    
        </frame>
        <frame procname="adhoc" line="3" stmtstart="114" stmtend="326" sqlhandle="0x0100080016aa6109a04b4292c501">
EXEC Test.dbo.procStatsMerge @ErrorCode=@ErrorCode1 OUTPUT,@ErrorMessage=@ErrorMessage1 OUTPU    
        </frame>
      </executionStack>
      <inputbuf>
DECLARE @ErrorCode1 int, @ErrorMessage1 varchar(4000)
EXEC Test.dbo.procStatsMerge @ErrorCode=@ErrorCode1 OUTPUT,@ErrorMessage=@ErrorMessage1 OUTPUT
SELECT @ErrorCode1, @ErrorMessage1
      </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057595777318912" dbid="7" objectname="Test.dbo.Stats" indexname="UIXC_Stats" id="lock1c58b047b00" mode="X" associatedObjectId="72057595777318912">
      <owner-list>
        <owner id="process1c48ce3dc28" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process1c48ce29468" mode="RangeS-S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057595988475904" dbid="7" objectname="Test.dbo.Rates" indexname="PK_Rates" id="lock1c9387c0280" mode="X" associatedObjectId="72057595988475904">
      <owner-list>
        <owner id="process1c48ce29468" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process1c48ce3dc28" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>   
</deadlock>

死锁原因分析 #

从死锁图可明确两个进程的循环阻塞关系：

1. 进程1（procRatesSet的UPDATE操作）：
   • 已持有Rates表PK_Rates索引的排他锁（X锁）
   • 等待Stats表UIXC_Stats索引的范围共享锁（RangeS-S锁）
2. 进程2（procStatsMerge的MERGE操作）：
   • 已持有Stats表UIXC_Stats索引的排他锁（X锁）
   • 等待Rates表PK_Rates索引的共享锁（S锁）

这种循环等待满足死锁的四个必要条件（互斥、请求与保持、不剥夺、循环等待），从而触发死锁。

解决建议 #

• 统一表访问顺序：修改两个存储过程的逻辑，确保所有事务访问两张表的顺序完全一致（比如先访问Stats表，再访问Rates表），打破循环等待链
• 缩短事务时长：检查事务边界，将不必要的操作移到事务外，减少锁的持有时间，降低冲突概率
• 启用读提交快照隔离：开启数据库的READ_COMMITTED_SNAPSHOT选项，让读操作使用行版本而非加锁，避免RangeS-S锁的产生

  ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT ON;
  

• 优化查询逻辑：
  • 对于procRatesSet中的UPDATE语句，检查@ToUpdate与@DaysToUpdate的交叉连接是否生成了冗余数据，添加过滤条件减少更新行数，降低锁的数量
  • 对于procStatsMerge的MERGE操作，确保源数据的查询有合适的索引覆盖，避免全表扫描导致的锁升级
• 调整锁升级策略：若操作涉及大量行，可将表的LOCK_ESCALATION设置为AUTO或DISABLE，避免从行锁升级为表锁（需评估对性能的影响）

  ALTER TABLE Test.dbo.Stats SET LOCK_ESCALATION = AUTO;
  ALTER TABLE Test.dbo.Rates SET LOCK_ESCALATION = AUTO;
  

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