January 24, 2018 at 4:57 pm
We're seeing many deadlocks, where different sessions are running the exact same delete query from the same host.
DELETE FROM FormFilingsDue WHERE (((CompanyId = @p1 ) AND (FormId = @p2 )) AND (PeriodEndDate = @p3 ))
The table has 1 clustered index (XCU_FormFilingsDue_ComIdFormIdPeriodenddate) on those 3 columns (CompanyId, FormId, PeriodEndDate), and a non-clustered index on the same 3 columns but different order of columns.
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 where %%lockres%% = '(b19380f04e08)'
select * from FormFilingsDue where %%lockres%% = '(a0c4bd49664c)'
both of them have records in the table.
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 ?
January 24, 2018 at 8:21 pm
Having different RECORDS doesn't mean they don't have one or more identical VALUES in the 3 indexed fields. You can still get a collision that way, no?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 25, 2018 at 1:37 am
TheSQLGuru - Wednesday, January 24, 2018 8:21 PMHaving different RECORDS doesn't mean they don't have one or more identical VALUES in the 3 indexed fields. You can still get a collision that way, no?
No, not with row locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2018 at 1:40 am
sqld-_-ba - Wednesday, January 24, 2018 4:57 PMThe 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 ?
Before we start theorising about lock collisions (which can happen, but very rarely), these deletes are part of a larger transaction. Please find what else is running in that transaction and post the info here.
The deadlock's unlikely to be between the two deletes (since they affect different rows), but more likely to be between the delete and something that ran earlier in the transaction and took locks that the deletes want
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2018 at 7:02 am
@TheSQLGuru (Kevin), The values are also different. It is a unique clustered index. the companyid and periodenddate are same, but formid is different.
@Gila, this code is Eclipse ORM generated LINQ, i'm checking with developers to see what else it's doing.
Regardless, is it possible for Deletes on different records to deadlock if they're holding locks when traversing the B-tree, instead of holding lock only on the final leaf-record to be deleted ?
January 25, 2018 at 7:19 am
It's deadlocking because there's some statement earlier in the transaction holding other locks. You need to identify what those other statements are before you can solve this deadlock problem.
Don't go looking for reasons based on internals when it's very likely that the problem is just the other code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply