Search code examples
sql-serverdeadlocktransactionscope

Delete Stored Proc Deadlock in Sql Server

I am having the following deadlock in SQL Server 2005 with a specific delete stored proc and I can't figure out what I need to do to remedy it.

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

  • You're missing an index:

    create index idxSecuritiesAccountFiling 
  on tSecuritiesRecords (AccountID, FillingID);

    Dues to the lack of index both DELETEs have to do range scans and acquire serializable locks to stabilize the DELETE operation. When they turn around and try to convert the S locks into X locks, they deadlock. With an index in place, the range locks acquired would not overlap.