Sql-server – Delete queries blocks other concurrent transactions

deadlockdeletelockingsql serversql-server-2016

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>

Best Answer

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.

Related Question