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>