Deadlock during Delete on Clustered Index

Question

We're seeing many deadlocks, where different sessions are running the exact same delete query (generated by Eclipse LINQ) from the same host:

DELETE FROM FormFilingsDue 
WHERE (((CompanyId =  @p1 ) 
  AND (FormId = @p2 )) 
  AND (PeriodEndDate =  @p3 ))

The table has 1 unique clustered index (XCU_FormFilingsDue_ComIdFormIdPeriodenddate) on those 3 columns (CompanyId, FormId, PeriodEndDate), and 1 unique non-clustered index on the same 3 columns but different order. No Primary Key.

Looking at the xml waitresource, clearly they are deleting different records (b19380f04e08) vs (a0c4bd49664c).

So why are they deadlocking with each other ?

<?xml version="1.0" encoding="UTF-8"?>
<deadlock victim="process667fc38">
   <process-list>
      <process id="process667fc38" taskpriority="0" logused="424" waitresource="KEY: 5:72057596424683520 (b19380f04e08)" waittime="3" ownerId="94427218017" transactionname="user_transaction" lasttranstarted="2018-01-24T13:14:39.947" XDES="0x27dd8f2040" lockMode="X" schedulerid="60" kpid="167084" status="suspended" spid="3508" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-24T13:14:39.960" lastbatchcompleted="2018-01-24T13:14:39.950" lastattention="2018-01-24T01:36:54.267" clientapp="inet" hostname="pprdiopam" hostpid="21" loginname="pub" isolationlevel="read committed (2)" xactid="94427218017" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="main">
         <executionStack>
            <frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x020000006824803bfb935b2dabd13e6752d213b770063de80000000000000000000000000000000000000000">DELETE FROM FormFilingsDue WHERE (((CompanyId =  @p1 ) AND (FormId =  @p2 )) AND (PeriodEndDate =  @p3 ))</frame>
            <frame procname="mssqlsystemresource.sys.sp_execute" line="1" stmtstart="-1" sqlhandle="0x0400ff7f47dacef5010000000000000000000000000000000000000000000000000000000000000000000000">sp_execute</frame>
         </executionStack>
         <inputbuf>(@p1 bigint,@p2 bigint,@p3 datetime2)DELETE FROM FormFilingsDue WHERE (((CompanyId =  @p1 ) AND (FormId =  @p2 )) AND (PeriodEndDate =  @p3 ))</inputbuf>
      </process>
      <process id="process464ccf8" taskpriority="0" logused="424" waitresource="KEY: 5:72057596424683520 (a0c4bd49664c)" waittime="1" ownerId="94427217809" transactionname="user_transaction" lasttranstarted="2018-01-24T13:14:39.947" XDES="0x18e335e0460" lockMode="X" schedulerid="31" kpid="216572" status="suspended" spid="6364" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-24T13:14:39.963" lastbatchcompleted="2018-01-24T13:14:39.950" lastattention="1900-01-01T00:00:00.950" clientapp="inet" hostname="pprdiopam" hostpid="748" loginname="pub" isolationlevel="read committed (2)" xactid="94427217809" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="main">
         <executionStack>
            <frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x020000006824803bfb935b2dabd13e6752d213b770063de80000000000000000000000000000000000000000">DELETE FROM FormFilingsDue WHERE (((CompanyId =  @p1 ) AND (FormId =  @p2 )) AND (PeriodEndDate =  @p3 ))</frame>
            <frame procname="mssqlsystemresource.sys.sp_execute" line="1" stmtstart="-1" sqlhandle="0x0400ff7f47dacef5010000000000000000000000000000000000000000000000000000000000000000000000">sp_execute</frame>
         </executionStack>
         <inputbuf>(@p1 bigint,@p2 bigint,@p3 datetime2)DELETE FROM FormFilingsDue WHERE (((CompanyId =  @p1 ) AND (FormId =  @p2 )) AND (PeriodEndDate =  @p3 ))</inputbuf>
      </process>
   </process-list>
   <resource-list>
      <keylock hobtid="72057596424683520" dbid="5" objectname="main.dbo.FormFilingsDue" indexname="XCU_FormFilingsDue_ComIdFormIdPeriodenddate" id="lock13c5db4780" mode="X" associatedObjectId="72057596424683520">
         <owner-list>
            <owner id="process464ccf8" mode="X" />
         </owner-list>
         <waiter-list>
            <waiter id="process667fc38" mode="X" requestType="wait" />
         </waiter-list>
      </keylock>
      <keylock hobtid="72057596424683520" dbid="5" objectname="main.dbo.FormFilingsDue" indexname="XCU_FormFilingsDue_ComIdFormIdPeriodenddate" id="lockaddcfe8800" mode="X" associatedObjectId="72057596424683520">
         <owner-list>
            <owner id="process667fc38" mode="X" />
         </owner-list>
         <waiter-list>
            <waiter id="process464ccf8" mode="X" requestType="wait" />
         </waiter-list>
      </keylock>
   </resource-list>
</deadlock>

Also, what is weirder is that when I run

select * from FormFilingsDue with (nolock index=XCU_FormFilingsDue_ComIdFormIdPeriodenddate) where %%lockres%% = '(b19380f04e08)'
select * from FormFilingsDue with (nolock index=XCU_FormFilingsDue_ComIdFormIdPeriodenddate) where %%lockres%% = '(a0c4bd49664c)'

both of them have records in the table. The CompanyId and PeriodEndDate are the same while FormId is different. This is the case for several deadlocks which happen each day.

The deadlock happened 30 minutes back on Production, and I'm checking the table now.‌ If only one of the spids was the victim, the survivor should have deleted the record. Yet I see it in the table. How come?

1. Is it possible that the %%lockres%% id is reused by another record, meaning the deadlock xml's waitresource is out-of-date?

2. Next, is it possible that these 2 delete statements are the only statements in the transaction (i.e no other statements before it), and even though they're deleting different records, they step on each other's toes by holding locks while traversing the B-tree, instead of holding lock only on the final leaf record to be deleted ?

Answer 1

Looking at the xml waitresource, clearly they are deleting different records (b19380f04e08) vs (a0c4bd49664c).

So why are they deadlocking with each other ?

Your code is executed in transactions. Deadlock graph shows trancount="2" for both processes.

The code you posted is only a part of each transaction, you should find the whole transaction code.

In every transaction MORE then 1 row is deleted, and these 2 transactions access these rows in different order, so your complete code schematically looks like this:

begin tran
   sp_execute  'DELETE FROM FormFilingsDue WHERE (((CompanyId = @p1 ) AND (FormId = @p2 )) AND (PeriodEndDate = @p3 ))' ,  @p1 = 1,  @p2 = 2, @p3 = 3;
   sp_execute  'DELETE FROM FormFilingsDue WHERE (((CompanyId = @p1 ) AND (FormId = @p2 )) AND (PeriodEndDate = @p3 ))' ,  @p1 = 4,  @p2 = 5, @p3 = 6; 
end

begin tran
   sp_execute  'DELETE FROM FormFilingsDue WHERE (((CompanyId = @p1 ) AND (FormId = @p2 )) AND (PeriodEndDate = @p3 ))' ,  @p1 = 4,  @p2 = 5, @p3 = 6;
   sp_execute  'DELETE FROM FormFilingsDue WHERE (((CompanyId = @p1 ) AND (FormId = @p2 )) AND (PeriodEndDate = @p3 ))' ,  @p1 = 1,  @p2 = 2, @p3 = 3; 
end

So the first transaction holds X lock on row1 until the end of the transaction while wants to delete row2, and the second transaction holds X on row2 and wants to delete row1.