两个不相关的表之间的死锁

问：

有时会在 2 张桌子上发生死锁，但我看不出它们之间有任何关系。

它们具有名为“代码”的相同列名，但这 2 列之间没有关系。 桌子上没有触发器。 锁位于索引 UIXC_Stats 和 PK_Rates 上。

在表 Test.dbo.Stats 中UIXC_Stats其唯一的聚集索引，该索引包括多个列，包括两个表中都存在的列 Code。 PK_Rates其聚集主键，其中包括表 Test.dbo.Rates 中的 2 列，其中之一是代码列。

这是死锁架构：

 <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>