Deadlock detection in Azure SQLDB

Vijay Kumar 2,036

Hi Team,

We are frequently getting deadlock in One our Azure SQL DB.
Please help you suggestion to fix. Please let me know if more info needed.

Please find the xml code

<deadlock>  
	<victim-list>  
		<victimProcess id="process2c79a377848" />  
	</victim-list>  
	<process-list>  
		<process id="process2c79a377848" taskpriority="0" logused="1124" waitresource="KEY: 6:72057596997664768 (4262567d03c0)" waittime="723" ownerId="17201459451" transactionname="implicit_transaction" lasttranstarted="2022-12-17T09:31:39.477" XDES="0x3176c664470" lockMode="X" schedulerid="32" kpid="47832" status="suspended" spid="436" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-12-17T09:31:39.660" lastbatchcompleted="2022-12-17T09:31:39.657" lastattention="1900-01-01T00:00:00.657" clientapp="Microsoft JDBC Driver for SQL Server" hostname="autobillrcdservice-deployment-7dd9976974-9nqp6" hostpid="0" loginname="DBORRBSCC" isolationlevel="read committed (2)" xactid="17201459451" currentdb="6" currentdbname="CABSPROD" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">  
			<stackFrames>  
				<frame id="00" address="0x7FF90D2C3CF4" pdb="ntdll.pdb" age="1" guid="FFD2B4B4-2A9A-80A8-C15B-866B7801E6A9" module="ntdll" rva="0xA3CF4" />  
				<frame id="01" address="0x7FF909CFA97F" pdb="kernelbase.pdb" age="1" guid="D358F47F-6BA7-7D66-234E-173B96AE50AC" module="kernelbase" rva="0xFA97F" />  
				<frame id="02" address="0x7FF8B54A68E7" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x68E7" />  
				<frame id="03" address="0x7FF8B54A680B" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x680B" />  
				<frame id="04" address="0x7FF8B54A1FB7" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x1FB7" />  
				<frame id="05" address="0x7FF8B54A3FB7" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x3FB7" />  
				<frame id="06" address="0x7FF8A264F8A5" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x7F8A5" />  
				<frame id="07" address="0x7FF8A264F6A0" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x7F6A0" />  
				<frame id="08" address="0x7FF8A25DEBF4" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0xEBF4" />  
				<frame id="09" address="0x7FF8A25E9AE8" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x19AE8" />  
				<frame id="10" address="0x7FF8A25EE25E" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1E25E" />  
				<frame id="11" address="0x7FF8A25EA74A" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1A74A" />  
				<frame id="12" address="0x7FF8A25EA46F" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1A46F" />  
				<frame id="13" address="0x7FF8A25EA553" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1A553" />  
				<frame id="14" address="0x7FF8A2642569" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x72569" />  
				<frame id="15" address="0x7FF8A5D91C67" pdb="SqlTsEs.pdb" age="2" guid="04CB4999-75C8-4F28-B9A9-6ED3EDB90853" module="SqlTsEs" rva="0x1C67" />  
				<frame id="16" address="0x7FF8A262CAC9" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x5CAC9" />  
				<frame id="17" address="0x7FF8A263E8C9" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x6E8C9" />  
				<frame id="18" address="0x7FF8A25FC8CF" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x2C8CF" />  
				<frame id="19" address="0x7FF89A930308" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x10308" />  
				<frame id="20" address="0x7FF89A94F4CC" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x2F4CC" />  
				<frame id="21" address="0x7FF89A94F22A" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x2F22A" />  
				<frame id="22" address="0x7FF89A92F35F" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xF35F" />  
				<frame id="23" address="0x7FF89A92F7DC" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xF7DC" />  
				<frame id="24" address="0x7FF89A92F081" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xF081" />  
				<frame id="25" address="0x7FF89A92E6F1" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE6F1" />  
				<frame id="26" address="0x7FF89B72D63E" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE0D63E" />  
				<frame id="27" address="0x7FF89B754FFF" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE34FFF" />  
				<frame id="28" address="0x7FF89B75585A" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE3585A" />  
				<frame id="29" address="0x7FF89A938DB3" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x18DB3" />  
				<frame id="30" address="0x7FF89A92BBCF" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xBBCF" />  
				<frame id="31" address="0x7FF89A92BCF0" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xBCF0" />  
				<frame id="32" address="0x7FF8B54A89DB" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x89DB" />  
			</stackFrames>  
			<executionStack>  
				<frame procname="unknown" queryhash="0x573569da062e8ba7" queryplanhash="0x50adc75bcaa1a4fc" line="1" stmtstart="38" stmtend="214" sqlhandle="0x02000000bd04612c72eb04a966feeedf06bf1a8220f5c2a10000000000000000000000000000000000000000">  
					unknown  
				</frame>  
				<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">  
					unknown  
				</frame>  
			</executionStack>  
			<inputbuf>  
				(@P0 decimal(38,0))delete  
				FROM  
				DBORRBSCC.EXCPT_RPT_DTL WITH (UPDLOCK)  
				WHERE  
				EXCPT_RPT_DTL_SK IN (@P0)  
			</inputbuf>  
		</process>  
		<process id="process2df3863cca8" taskpriority="0" logused="1124" waitresource="KEY: 6:72057596997664768 (1b5cde9362f5)" waittime="759" ownerId="17201459367" transactionname="implicit_transaction" lasttranstarted="2022-12-17T09:31:39.433" XDES="0x31edaa50470" lockMode="X" schedulerid="28" kpid="29224" status="suspended" spid="649" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-12-17T09:31:39.623" lastbatchcompleted="2022-12-17T09:31:39.623" lastattention="1900-01-01T00:00:00.623" clientapp="Microsoft JDBC Driver for SQL Server" hostname="autobillrcdservice-deployment-7dd9976974-9nqp6" hostpid="0" loginname="DBORRBSCC" isolationlevel="read committed (2)" xactid="17201459367" currentdb="6" currentdbname="CABSPROD" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">  
			<stackFrames>  
				<frame id="00" address="0x7FF90D2C3CF4" pdb="ntdll.pdb" age="1" guid="FFD2B4B4-2A9A-80A8-C15B-866B7801E6A9" module="ntdll" rva="0xA3CF4" />  
				<frame id="01" address="0x7FF909CFA97F" pdb="kernelbase.pdb" age="1" guid="D358F47F-6BA7-7D66-234E-173B96AE50AC" module="kernelbase" rva="0xFA97F" />  
				<frame id="02" address="0x7FF8B54A68E7" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x68E7" />  
				<frame id="03" address="0x7FF8B54A680B" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x680B" />  
				<frame id="04" address="0x7FF8B54A1FB7" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x1FB7" />  
				<frame id="05" address="0x7FF8B54A3FB7" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x3FB7" />  
				<frame id="06" address="0x7FF8A264F8A5" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x7F8A5" />  
				<frame id="07" address="0x7FF8A264F6A0" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x7F6A0" />  
				<frame id="08" address="0x7FF8A25DEBF4" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0xEBF4" />  
				<frame id="09" address="0x7FF8A25E9AE8" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x19AE8" />  
				<frame id="10" address="0x7FF8A25EE25E" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1E25E" />  
				<frame id="11" address="0x7FF8A25EA74A" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1A74A" />  
				<frame id="12" address="0x7FF8A25EA46F" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1A46F" />  
				<frame id="13" address="0x7FF8A25EA553" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x1A553" />  
				<frame id="14" address="0x7FF8A2642569" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x72569" />  
				<frame id="15" address="0x7FF8A5D91C67" pdb="SqlTsEs.pdb" age="2" guid="04CB4999-75C8-4F28-B9A9-6ED3EDB90853" module="SqlTsEs" rva="0x1C67" />  
				<frame id="16" address="0x7FF8A262CAC9" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x5CAC9" />  
				<frame id="17" address="0x7FF8A263E8C9" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x6E8C9" />  
				<frame id="18" address="0x7FF8A25FC8CF" pdb="sqlmin.pdb" age="2" guid="6B54B94D-99BC-4EEB-80E2-8BBC250BE957" module="sqlmin" rva="0x2C8CF" />  
				<frame id="19" address="0x7FF89A930308" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x10308" />  
				<frame id="20" address="0x7FF89A94F4CC" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x2F4CC" />  
				<frame id="21" address="0x7FF89A94F22A" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x2F22A" />  
				<frame id="22" address="0x7FF89A92F35F" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xF35F" />  
				<frame id="23" address="0x7FF89A92F7DC" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xF7DC" />  
				<frame id="24" address="0x7FF89A92F081" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xF081" />  
				<frame id="25" address="0x7FF89A92E6F1" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE6F1" />  
				<frame id="26" address="0x7FF89B72D63E" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE0D63E" />  
				<frame id="27" address="0x7FF89B754FFF" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE34FFF" />  
				<frame id="28" address="0x7FF89B75585A" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xE3585A" />  
				<frame id="29" address="0x7FF89A938DB3" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0x18DB3" />  
				<frame id="30" address="0x7FF89A92BBCF" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xBBCF" />  
				<frame id="31" address="0x7FF89A92BCF0" pdb="sqllang.pdb" age="2" guid="1F43299A-D745-4CE5-9918-D4BFA5245DDE" module="sqllang" rva="0xBCF0" />  
				<frame id="32" address="0x7FF8B54A89DB" pdb="SqlDK.pdb" age="2" guid="103BE624-88B4-42DC-8330-283E144A0444" module="SqlDK" rva="0x89DB" />  
			</stackFrames>  
			<executionStack>  
				<frame procname="unknown" queryhash="0x573569da062e8ba7" queryplanhash="0x50adc75bcaa1a4fc" line="1" stmtstart="38" stmtend="214" sqlhandle="0x02000000bd04612c72eb04a966feeedf06bf1a8220f5c2a10000000000000000000000000000000000000000">  
					unknown  
				</frame>  
				<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">  
					unknown  
				</frame>  
			</executionStack>  
			<inputbuf>  
				(@P0 decimal(38,0))delete  
				FROM  
				DBORRBSCC.EXCPT_RPT_DTL WITH (UPDLOCK)  
				WHERE  
				EXCPT_RPT_DTL_SK IN (@P0)  
			</inputbuf>  
		</process>  
	</process-list>  
	<resource-list>  
		<keylock hobtid="72057596997664768" dbid="6" objectname="1888ce7f-0668-40ad-8ed5-0e3a48d084a9.DBORRBSCC.EXCPT_RPT_DTL" indexname="EXCPT_RPT_DTL_PK" id="lock2ce10d51b80" mode="X" associatedObjectId="72057596997664768">  
			<owner-list>  
				<owner id="process2df3863cca8" mode="X" />  
			</owner-list>  
			<waiter-list>  
				<waiter id="process2c79a377848" mode="X" requestType="wait" />  
			</waiter-list>  
		</keylock>  
		<keylock hobtid="72057596997664768" dbid="6" objectname="1888ce7f-0668-40ad-8ed5-0e3a48d084a9.DBORRBSCC.EXCPT_RPT_DTL" indexname="EXCPT_RPT_DTL_PK" id="lock2fb84d5ef00" mode="X" associatedObjectId="72057596997664768">  
			<owner-list>  
				<owner id="process2c79a377848" mode="X" />  
			</owner-list>  
			<waiter-list>  
				<waiter id="process2df3863cca8" mode="X" requestType="wait" />  
			</waiter-list>  
		</keylock>  
	</resource-list>  
</deadlock>

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2023-01-02T05:04:26.347+00:00

Hi, @Vijay Kumar Please let us know if you find the below reply useful. If yes, do click on the 'Mark as answer'
link in the above reply. This will help other community members facing similar queries to refer to this solution

Regards
Geetha

1 answer

Alberto Morillo 34,451 Reputation points MVP

2022-12-18T23:48:17.443+00:00

I see you tried WITH (UPDLOCK) hint. Have you tried with OPTION (MAXDOP 1) hint?
Please sign in to rate this answer.
Vijay Kumar 2,036 Reputation points

2022-12-19T03:33:40.443+00:00

Hi Alberto,

Do you mean like this?

delete
FROM
DBORRBSCC.EXCPT_RPT_DTL WITH (UPDLOCK) OPTION (MAXDOP 1)

Alberto Morillo 34,451 Reputation points MVP

2022-12-19T04:05:53.223+00:00

Remove the WITH (UPDLOCK) hint. With the OPTION (MAXDOP 1) hint the statement will have more elapsed execution time but it will serialize the plan and avoid the deadlock

Vijay Kumar 2,036 Reputation points

2022-12-19T04:07:23.203+00:00

is it correct?

DBORRBSCC.EXCPT_RPT_DTL WITH OPTION (MAXDOP 1)

Alberto Morillo 34,451 Reputation points MVP

2022-12-19T04:44:02.873+00:00

Yes. That is correct
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Deadlock detection in Azure SQLDB

1 answer

Your answer