3

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 ?

  • 1
    This could be a hash collision - the value you see with %%lockres%% is a hash of the key and it is possible but rare for two hashes to be equal - a collision. Your deadlock could be the result of such a collision. – Mister Magoo Jan 25 '18 at 1:27
  • What are the query plans for the deletes? – Erik Darling Jan 25 '18 at 2:07
  • I think this blog post explains your case. web.archive.org/web/20130806221413/http:/… . Another tip here to use index hint with %%lockers%%. – SqlWorldWide Jan 25 '18 at 2:19
  • Why all the parens? And will process the same without. – paparazzo Jan 25 '18 at 11:25
  • @MisterMagoo, how can i find out for sure whether it is due to hash collision or not ? – d-_-b Jan 25 '18 at 13:45
0

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.

  • this code is coming from Eclipse LINQ generated query. I have seen trancount=2 even when there is only 1 begin tran - think you can test this with SSMS. Is it absolutely necessary to have 2 delete statements ? Can two single delete statements deadlock because they're holding a lock when traversing the B-tree, not just on the final record ? – d-_-b Jan 25 '18 at 13:49
  • Isn't trancount of 2 indicative of an explicit transaction, where 0 would indicate an implicit tran? So this could be the entire code but with a transaction declared on the connection. – Mister Magoo Jan 25 '18 at 13:51
  • >>>Isn't trancount of 2 indicative of an explicit transaction, where 0 would indicate an implicit tran?<<< Yes, exactly so. So there is at least explicit begin tran in the code that is not shown here. But since there are 2 different rows envolved in the deadlock, both transactions access them, but in different order – sepupic Jan 26 '18 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.