删除Sql Server中的存储过程死锁

Question

我在 SQL Server 2005 中使用特定的删除存储过程遇到以下死锁，并且我不知道需要做什么来补救它。

<deadlock-list>
 <deadlock victim="processf3a868">
  <process-list>
   <process id="processcae718" taskpriority="0" logused="0" waitresource="KEY: 7:72057594340311040 (b50041b389fe)" waittime="62" ownerId="1678098" transactionguid="0x950057256032d14db6a2c553a39a8279" transactionname="user_transaction" lasttranstarted="2010-05-26T13:45:23.517" XDES="0x8306c370" lockMode="RangeS-U" schedulerid="1" kpid="2432" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-05-26T13:45:23.717" lastbatchcompleted="2010-05-26T13:45:23.717" clientapp=".Net SqlClient Data Provider" hostname="DEVELOPER01" hostpid="28104" loginname="DEVELOPER01\ServiceUser" isolationlevel="serializable (4)" xactid="1678098" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="DB.dbo.sp_DeleteSecuritiesRecords" line="13" stmtstart="708" stmtend="918" sqlhandle="0x030007008b6b662229b10c014f9d00000100000000000000">
DELETE FROM tSecuritiesRecords WHERE [FilingID] = @filingID AND [AccountID] = @accountID     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 577137547]    </inputbuf>
   </process>
   <process id="processf3a868" taskpriority="0" logused="0" waitresource="KEY: 7:72057594340311040 (4f00409af90f)" waittime="93" ownerId="1678019" transactionguid="0xb716547a8f7fdd40b342e5db6b3699fb" transactionname="user_transaction" lasttranstarted="2010-05-26T13:45:21.543" XDES="0x92617130" lockMode="X" schedulerid="3" kpid="13108" status="suspended" spid="57" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-05-26T13:45:23.717" lastbatchcompleted="2010-05-26T13:45:23.717" clientapp=".Net SqlClient Data Provider" hostname="DEVELOPER01" hostpid="28104" loginname="DEVELOPER01\ServiceUser" isolationlevel="serializable (4)" xactid="1678019" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="DB.dbo.sp_DeleteSecuritiesRecords" line="13" stmtstart="708" stmtend="918" sqlhandle="0x030007008b6b662229b10c014f9d00000100000000000000">
DELETE FROM tSecuritiesRecords WHERE [FilingID] = @filingID AND [AccountID] = @accountID     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 577137547]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594340311040" dbid="7" objectname="DB.dbo.tSecuritiesRecords" indexname="PK_tTransactions" id="lock82416380" mode="RangeS-U" associatedObjectId="72057594340311040">
    <owner-list>
     <owner id="processf3a868" mode="RangeS-U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processcae718" mode="RangeS-U" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594340311040" dbid="7" objectname="DB.dbo.tSecuritiesRecords" indexname="PK_tTransactions" id="lock825fd380" mode="RangeS-U" associatedObjectId="72057594340311040">
    <owner-list>
     <owner id="processcae718" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processf3a868" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Answer 1

您缺少索引：

create index idxSecuritiesAccountFiling 
  on tSecuritiesRecords (AccountID, FillingID);

由于缺少索引，两个 DELETE 都必须进行范围扫描并获取可序列化锁以稳定 DELETE 操作。当他们转身尝试将 S 锁转换为 X 锁时，他们陷入了死锁。有了索引，获取的范围锁就不会重叠。