Sql-server – Deadlock during Delete on Clustered Index

deadlocksql server

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?

Is it possible that the %%lockres%% id is reused by another record, meaning the deadlock xml's waitresource is out-of-date?
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 ?

Best Answer

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.

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

Sql-server – Deadlock on Delete Statement

What I am looking for is why I am getting a DELETE/DELETE deadlock.

It appears the deadlock occurs because:

spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422
spid 166 ecid 3 requests an update (U) page lock on the same page, and is blocked
spid 54 ecid 2 requests an update (U) page lock on the same page...

Pages are being prefetched for the query, with update locks acquired by ecid 0. That is step 1 above. In step 3, a child thread of the same parallel query (ecid 2) requests the same lock. Normally this would not be a problem. SQL Server knows ecid 0 and ecid 2 are threads of the same parent process. Unfortunately, step 2 gets in the way of this, and a deadlock results.

That said, you shouldn't really care much about why the deadlock occurs, the important question is how do you avoid it. The answer is to provide an efficient access path for the DELETE. The statement needs to find rows WHERE Username = @P1 AND UserDate = @P2, so you should have an index keyed on these columns.

And of course you do have such an index. The real question is why your problems started occurring after you added filtered indexes.

The answer to that is extra column information is needed to locate the filtered index rows to delete (and to check their predicates). If the query uses a narrow/per-row execution plan, the execution engine is unable to fetch the extra columns in the Clustered Index Delete operator, as it would in a wide/per-index plan.

You can find more details about that, and a worked example in this blog post.

In this case, the column information needs to come from the part of the plan to the right of the Clustered Index Delete, and so a parallel clustered index scan is used, and you get a slow query with high deadlock potential.

The answer is to do one of the following:

Remove the filtered indexes
Add filtered index key/include/predicate columns to the existing name/date index
Force a wide update plan (no supported way to do this)
Run the query under snapshot isolation (not RCSI)

Option 2 would be my strong preference.

Option 4 (thanks Jack Douglas) has the advantage of removing deadlocks, and ought not cause any "update conflicts", given the disjoint nature of the changes, but it does require enabling snapshot isolation at the database level, explicitly changing the isolation level, and will not fix the underlying issue: you'll still end up with a wasteful parallel table scan, where a nice index seek is what you really want.

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Sql-server – Deadlock on Delete Statement

Related Question