SQLException :处理两个不同表时的死锁错误

Question

我有死锁错误：

事务(进程ID 91)在锁定资源时与另一个进程陷入僵局，并被选择为死锁牺牲品。重新运行事务。

图像死锁(https://i.stack.imgur.com/rDyxP.png)

Sp 91是商店Katzkin.dbo.ItemsAirBags_DX_IdItem：

delete ItemsAirBags
from ItemsAirBags  
where IdItem = @IdItem

Sp291是商店Katzkin.dbo.Items_TX_Description：

select *
from items  
where description = @description or left(description,5) = @description

我从redgate下载的完整deadlock.xml：

 <deadlock>
  <victim-list>
    <victimProcess id="process366a0a5848" />
  </victim-list>
  <process-list>
    <process id="process366a0a5848" taskpriority="0" logused="6424" waitresource="KEY: 6:72057609466478592 (56dfc8bc1bd3)" waittime="134" ownerId="2620704196" transactionname="user_transaction" lasttranstarted="2021-01-25T10:48:53.610" XDES="0x4462dd4490" lockMode="U" schedulerid="11" kpid="14292" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-01-25T10:48:53.690" lastbatchcompleted="2021-01-25T10:48:53.687" lastattention="2021-01-25T08:32:04.567" clientapp=".Net SqlClient Data Provider" hostname="SQLSERVER" hostpid="17172" loginname="KatCa" isolationlevel="read committed (2)" xactid="2620704196" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="Katzkin.dbo.ItemsAirBags_DX_IdItem" line="2" stmtstart="136" stmtend="258" sqlhandle="0x030006006b1f922228e7730050ab000001000000000000000000000000000000000000000000000000000000">
delete ItemsAirBags from ItemsAirBags  
where IdItem = @IdIte    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 6 Object Id = 580001643]   </inputbuf>
    </process>
    <process id="process3dc4191468" taskpriority="0" logused="34720" waitresource="KEY: 6:72057609931325440 (b015ce3d2676)" waittime="62" ownerId="2620704065" transactionname="user_transaction" lasttranstarted="2021-01-25T10:48:53.500" XDES="0x3d45478490" lockMode="S" schedulerid="6" kpid="1500" status="suspended" spid="291" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-01-25T10:48:53.760" lastbatchcompleted="2021-01-25T10:48:53.760" lastattention="2021-01-25T08:30:50.683" clientapp=".Net SqlClient Data Provider" hostname="SQLSERVER" hostpid="30552" loginname="KatCa" isolationlevel="read committed (2)" xactid="2620704065" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="Katzkin.dbo.Items_TX_Description" line="4" stmtstart="172" stmtend="348" sqlhandle="0x030006008513045381f88c002da3000001000000000000000000000000000000000000000000000000000000">
Select * from items  
where description=@description or left(description,5)=@descriptio    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 6 Object Id = 1392776069]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057609466478592" dbid="6" objectname="Katzkin.dbo.ItemsAirBags" indexname="PK_ItemsAirBags" id="lock3ca39db480" mode="X" associatedObjectId="72057609466478592">
      <owner-list>
        <owner id="process3dc4191468" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process366a0a5848" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057609931325440" dbid="6" objectname="Katzkin.dbo.Items" indexname="PK_Items" id="lock3f52da2100" mode="X" associatedObjectId="72057609931325440">
      <owner-list>
        <owner id="process366a0a5848" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process3dc4191468" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

我们在两个不同的表上，但是导致了一个死锁，我不明白原因以及如何修复它。对于如何解决这些问题，有什么想法吗？

Answer 1

如死锁图所示，您有两个存储过程，它们都试图更新/访问相同的行，但由于某种原因，一个是首先更新/访问ItemsAirBags表，其次是Items表，另一个是相反顺序的存储过程。这就是造成僵局的原因。如果使两个存储过程都以相同的顺序更新/访问两个表，则将消除死锁。

当您处理父级子级情况时，有时需要在更新父级和子级之前对您计划更新的子行进行更新锁。

您希望确保始终按照相同的顺序更新/访问表，因为这样，第一个存储过程将阻塞第二个存储过程(这正是您想要的)，而不是死锁。

注意:在这些存储过程中发生的情况肯定比代码显示的要多，因为按照目前的情况，不会出现死锁。process process3dc4191468在ItemsAirBags上有一个独占锁，这不是所显示的SQL的一部分。