0

DELETE queries in my production SQL server are causing deadlocks. I know that DELETE is causing this because i checked the Extended Events and checked the deadlock XML and found out that this DELETE is blocking and this eventually leads to deadlocks.

So DELETE is happening on two tables,tb1 and tb2. Primary key of tb1 is availabe as foreign key in tb2 and CASCADE DELETE is used in tb2 foreign key.

I even made Allow Page Locks to FALSE for Clustered Index in tb1 and tb2 and still no luck.

I want to try all other options before i try READ COMMITTED SNAPSHOT in my database.

Any help will appreciated.

Additional Info:

Using READ COMMITTED SNAPSHOT is also a challenge as,it has risk involved.

  • My DB size is huge (~1.6 TB) and RAM is 128 GB.
  • Using SQL Server 2016

Here is the XDL

<deadlock>
 <victim-list>
  <victimProcess id="process257a65d6ca8" />
 </victim-list>
 <process-list>
  <process id="process257a65d6ca8" taskpriority="0" logused="6024" waitresource="KEY: 6:72057794784329728 (bb7a6e52eae1)" waittime="4485" ownerId="45816472292" transactionname="user_transaction" lasttranstarted="2019-01-08T11:30:06.837" XDES="0x24883ec2a70" lockMode="RangeS-U" schedulerid="26" kpid="39320" status="suspended" spid="217" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-08T11:30:06.883" lastbatchcompleted="2019-01-08T11:30:06.870" lastattention="1900-01-01T00:00:00.870" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-01" hostpid="5356" loginname="pasl_msp" isolationlevel="read committed (2)" xactid="45816472292" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="42" stmtend="116" sqlhandle="0x0200000032f3ca31d40d7b71d6e79a1044a3a86617a5be1e0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@0 uniqueidentifier)DELETE [dbo].[Form]
WHERE ([Id] = @0)   </inputbuf>
  </process>
  <process id="process252a472e8c8" taskpriority="0" logused="7552" waitresource="KEY: 6:72057794784329728 (1a150171029d)" waittime="706" ownerId="45816472158" transactionname="user_transaction" lasttranstarted="2019-01-08T11:30:06.733" XDES="0x23eb032a430" lockMode="RangeS-U" schedulerid="30" kpid="66604" status="suspended" spid="283" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-08T11:30:06.827" lastbatchcompleted="2019-01-08T11:30:06.803" lastattention="1900-01-01T00:00:00.803" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="6216" loginname="pasl_msp" isolationlevel="read committed (2)" xactid="45816472158" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="42" stmtend="116" sqlhandle="0x0200000032f3ca31d40d7b71d6e79a1044a3a86617a5be1e0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@0 uniqueidentifier)DELETE [dbo].[Form]
WHERE ([Id] = @0)   </inputbuf>
  </process>
  <process id="process245373d2ca8" taskpriority="0" logused="77556" waitresource="KEY: 6:72057794784329728 (bb7a6e52eae1)" waittime="3216" ownerId="45816432209" transactionname="user_transaction" lasttranstarted="2019-01-08T11:30:00.563" XDES="0x255954753d0" lockMode="RangeS-U" schedulerid="33" kpid="19100" status="suspended" spid="381" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-08T11:30:08.130" lastbatchcompleted="2019-01-08T11:30:08.120" lastattention="1900-01-01T00:00:00.120" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="6216" loginname="pasl_msp" isolationlevel="read committed (2)" xactid="45816432209" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="42" stmtend="116" sqlhandle="0x0200000032f3ca31d40d7b71d6e79a1044a3a86617a5be1e0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@0 uniqueidentifier)DELETE [dbo].[Form]
WHERE ([Id] = @0)   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057794784329728" dbid="6" objectname="iyeTek.Services.MSP.AICS.dbo.FormNarrative" indexname="PK_FormNarrative" id="lock24c95634480" mode="RangeS-U" associatedObjectId="72057794784329728">
   <owner-list>
    <owner id="process252a472e8c8" mode="RangeS-U" />
   </owner-list>
   <waiter-list>
    <waiter id="process257a65d6ca8" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057794784329728" dbid="6" objectname="iyeTek.Services.MSP.AICS.dbo.FormNarrative" indexname="PK_FormNarrative" id="lock251d3ea0c00" mode="RangeX-X" associatedObjectId="72057794784329728">
   <owner-list>
    <owner id="process245373d2ca8" mode="RangeX-X" />
   </owner-list>
   <waiter-list>
    <waiter id="process252a472e8c8" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057794784329728" dbid="6" objectname="iyeTek.Services.MSP.AICS.dbo.FormNarrative" indexname="PK_FormNarrative" id="lock24c95634480" mode="RangeS-U" associatedObjectId="72057794784329728">
   <owner-list>
    <owner id="process257a65d6ca8" mode="RangeS-U" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process245373d2ca8" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>
  • 1
    Share the XDL. A delete generally won't cause a deadlock on its own (just blocking) unless it is involved in a larger transaction and/or it's competing with another process that locks multiple objects in a different order. – Aaron Bertrand Jan 8 at 18:45
  • Also to have more control over order of operations I suggest launching the ON CASCADE options into orbit. – Aaron Bertrand Jan 8 at 18:46
  • @AaronBertrand : Added the deadlock XML. – user9516827 Jan 8 at 18:51
5

If you're using cascading deletes, then yes, they automatically escalate to serializable locks as shown in the lockMode="RangeS-U" part of your deadlock graph, which pretty much blocks everybody else on the child table. Changing the read committed snapshot won't change anything.

You'll want to change the code to do deletes on the child table first, and then the parent, rather than relying on cascading deletes. One of those great examples of something that works great on the desktop, and falls apart at scale.

0

Delete is not blocking now after i did the below changes.

  • Removed page level locking on the index from both parent and child table.
  • There was only a clustered index on the child table.Added Non clustered index on the foreign key from the parent table.
  • Since page level locking is removed,reorganize index won't work on those indexes ,so added a index Rebuild in the maintenance plan.
0

I had a similar scenario where I was deleting child records through a cascading delete and then repopulating child records in the same transaction. Multiple concurrent transactions were deadlocking.

Turns out it was the fact the child tables had no primary key. I added a composite primary key to the child tables and the problem disappeared, so it would seem clear that without some sort of indexing, SQL server will take coarser locks (page? table?) on the child tables.

It's also worth noting this affects SQL Azure.

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.