March 7, 2024 at 3:03 am
I am facing few deadlock issue recently and when i tried to debug the issue using the XML file generated from the Extended event, i was not able to figure out the exact issue. The text seems very generic to me. Below the content of the XML file
<deadlock>
<victim-list>
<victimProcess id="process24ce76d7c28" />
</victim-list>
<process-list>
<process id="process24ce76d7c28" taskpriority="0" logused="5544" waitresource="KEY: 21:281474978938880 (0b3f3f3d03be)" waittime="8542" ownerId="384181886" transactionname="user_transaction" lasttranstarted="2024-03-07T11:33:56.220" XDES="0x25c48a4c470" lockMode="S" schedulerid="2" kpid="16148" status="suspended" spid="114" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-03-07T11:33:56.657" lastbatchcompleted="2024-03-07T11:33:56.643" lastattention="1900-01-01T00:00:00.643" clientapp="python3.9" hostname="abcd45c9bfda" hostpid="77" loginname="saoretaadmin" isolationlevel="read committed (2)" xactid="384181886" currentdb="21" currentdbname="ServiceAppDbV2_Staging_Pipeline" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<stackFrames>
<frame id="00" address="0x7FF90C563A34" pdb="ntdll.pdb" age="1" guid="228099F2-6365-CE11-E1B3-02137708A97A" module="ntdll" rva="0xA3A34" />
<frame id="01" address="0x7FF909DF6DF9" pdb="kernelbase.pdb" age="1" guid="6619167E-4788-070A-35F4-27E9E026B8D6" module="kernelbase" rva="0xC6DF9" />
<frame id="02" address="0x7FF8AFA163E7" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x63E7" />
<frame id="03" address="0x7FF8AFA162FE" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x62FE" />
<frame id="04" address="0x7FF8AFA11B2B" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x1B2B" />
<frame id="05" address="0x7FF8AFA135D7" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x35D7" />
<frame id="06" address="0x7FF8AC14F749" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F749" />
<frame id="07" address="0x7FF8AC14F557" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F557" />
<frame id="08" address="0x7FF8ABFDE9C4" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xE9C4" />
<frame id="09" address="0x7FF8ABFE7F78" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F78" />
<frame id="10" address="0x7FF8AC07B3FB" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xAB3FB" />
<frame id="11" address="0x7FF8ABFE8124" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x18124" />
<frame id="12" address="0x7FF8ABFDFDD0" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xFDD0" />
<frame id="13" address="0x7FF8ABFF23FB" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x223FB" />
<frame id="14" address="0x7FF8ABFF2316" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22316" />
<frame id="15" address="0x7FF8AC1B7CBF" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x1E7CBF" />
<frame id="16" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="17" address="0x7FF8ABFF6C1C" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x26C1C" />
<frame id="18" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="19" address="0x7FF8ABFF6451" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x26451" />
<frame id="20" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="21" address="0x7FF8AC0397F6" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x697F6" />
<frame id="22" address="0x7FF8AC039896" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x69896" />
<frame id="23" address="0x7FF8AC01D18C" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x4D18C" />
<frame id="24" address="0x7FF8A914B8C7" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xB8C7" />
<frame id="25" address="0x7FF8A914B5CF" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xB5CF" />
<frame id="26" address="0x7FF8A9152838" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x12838" />
<frame id="27" address="0x7FF8A9152A2E" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x12A2E" />
<frame id="28" address="0x7FF8A914D18F" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xD18F" />
<frame id="29" address="0x7FF8A914D5DC" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xD5DC" />
<frame id="30" address="0x7FF8A914CEF8" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xCEF8" />
<frame id="31" address="0x7FF8A914C45C" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xC45C" />
<frame id="32" address="0x7FF8A9F6EC9C" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE2EC9C" />
<frame id="33" address="0x7FF8A9F6E77D" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE2E77D" />
<frame id="34" address="0x7FF8A9F91078" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE51078" />
<frame id="35" address="0x7FF8A9F91851" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE51851" />
<frame id="36" address="0x7FF8A9157A85" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x17A85" />
</stackFrames>
<executionStack>
<frame procname="adhoc" queryhash="0x2e20f3dae991fbf0" queryplanhash="0x9efa265f12e2b42a" line="1" stmtstart="74" stmtend="660" sqlhandle="0x020000001f2a0e075b69ca2a243279c4c4834633565a80770000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 nvarchar(max),@P2 nvarchar(max))SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(@P1 AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(@P2 AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] </inputbuf>
</process>
<process id="process251a7113848" taskpriority="0" logused="5805996" waitresource="KEY: 21:281474978938880 (0b3f3f3d03be)" waittime="4376" ownerId="383672145" transactionname="user_transaction" lasttranstarted="2024-03-07T11:20:03.500" XDES="0x25148b78470" lockMode="S" schedulerid="4" kpid="10908" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-03-07T11:34:00.823" lastbatchcompleted="2024-03-07T11:34:00.800" lastattention="1900-01-01T00:00:00.800" clientapp="python3.9" hostname="abcd45c9bfda" hostpid="77" loginname="saoretaadmin" isolationlevel="read committed (2)" xactid="383672145" currentdb="21" currentdbname="ServiceAppDbV2_Staging_Pipeline" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<stackFrames>
<frame id="00" address="0x7FF90C563A34" pdb="ntdll.pdb" age="1" guid="228099F2-6365-CE11-E1B3-02137708A97A" module="ntdll" rva="0xA3A34" />
<frame id="01" address="0x7FF909DF6DF9" pdb="kernelbase.pdb" age="1" guid="6619167E-4788-070A-35F4-27E9E026B8D6" module="kernelbase" rva="0xC6DF9" />
<frame id="02" address="0x7FF8AFA163E7" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x63E7" />
<frame id="03" address="0x7FF8AFA162FE" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x62FE" />
<frame id="04" address="0x7FF8AFA11B2B" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x1B2B" />
<frame id="05" address="0x7FF8AFA135D7" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x35D7" />
<frame id="06" address="0x7FF8AC14F749" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F749" />
<frame id="07" address="0x7FF8AC14F557" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F557" />
<frame id="08" address="0x7FF8ABFDE9C4" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xE9C4" />
<frame id="09" address="0x7FF8ABFE7F78" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F78" />
<frame id="10" address="0x7FF8AC07B3FB" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xAB3FB" />
<frame id="11" address="0x7FF8ABFE8124" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x18124" />
<frame id="12" address="0x7FF8ABFDFDD0" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xFDD0" />
<frame id="13" address="0x7FF8ABFF23FB" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x223FB" />
<frame id="14" address="0x7FF8ABFF2316" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22316" />
<frame id="15" address="0x7FF8AC1B7CBF" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x1E7CBF" />
<frame id="16" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="17" address="0x7FF8ABFF6C1C" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x26C1C" />
<frame id="18" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="19" address="0x7FF8ABFF6451" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x26451" />
<frame id="20" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="21" address="0x7FF8AC0397F6" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x697F6" />
<frame id="22" address="0x7FF8AC039896" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x69896" />
<frame id="23" address="0x7FF8AC01D18C" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x4D18C" />
<frame id="24" address="0x7FF8A914B8C7" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xB8C7" />
<frame id="25" address="0x7FF8A914B5CF" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xB5CF" />
<frame id="26" address="0x7FF8A9152838" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x12838" />
<frame id="27" address="0x7FF8A9152A2E" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x12A2E" />
<frame id="28" address="0x7FF8A914D18F" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xD18F" />
<frame id="29" address="0x7FF8A914D5DC" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xD5DC" />
<frame id="30" address="0x7FF8A914CEF8" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xCEF8" />
<frame id="31" address="0x7FF8A914C45C" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xC45C" />
<frame id="32" address="0x7FF8A9F6EC9C" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE2EC9C" />
<frame id="33" address="0x7FF8A9F6E77D" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE2E77D" />
<frame id="34" address="0x7FF8A9F91078" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE51078" />
<frame id="35" address="0x7FF8A9F91851" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE51851" />
<frame id="36" address="0x7FF8A9157A85" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x17A85" />
</stackFrames>
<executionStack>
<frame procname="adhoc" queryhash="0x2e20f3dae991fbf0" queryplanhash="0x9efa265f12e2b42a" line="1" stmtstart="74" stmtend="660" sqlhandle="0x020000001f2a0e075b69ca2a243279c4c4834633565a80770000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 nvarchar(max),@P2 nvarchar(max))SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(@P1 AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(@P2 AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] </inputbuf>
</process>
<process id="process2580ce96108" taskpriority="0" logused="10618488" waitresource="KEY: 21:281474978938880 (70df5c5bd799)" waittime="4123" ownerId="383954886" transactionname="user_transaction" lasttranstarted="2024-03-07T11:30:02.197" XDES="0x24cf00ac470" lockMode="S" schedulerid="4" kpid="13048" status="suspended" spid="138" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-03-07T11:34:01.083" lastbatchcompleted="2024-03-07T11:34:01.040" lastattention="1900-01-01T00:00:00.040" clientapp="python3.9" hostname="abcd45c9bfda" hostpid="77" loginname="saoretaadmin" isolationlevel="read committed (2)" xactid="383954886" currentdb="21" currentdbname="ServiceAppDbV2_Staging_Pipeline" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<stackFrames>
<frame id="00" address="0x7FF90C563A34" pdb="ntdll.pdb" age="1" guid="228099F2-6365-CE11-E1B3-02137708A97A" module="ntdll" rva="0xA3A34" />
<frame id="01" address="0x7FF909DF6DF9" pdb="kernelbase.pdb" age="1" guid="6619167E-4788-070A-35F4-27E9E026B8D6" module="kernelbase" rva="0xC6DF9" />
<frame id="02" address="0x7FF8AFA163E7" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x63E7" />
<frame id="03" address="0x7FF8AFA162FE" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x62FE" />
<frame id="04" address="0x7FF8AFA11B2B" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x1B2B" />
<frame id="05" address="0x7FF8AFA135D7" pdb="SqlDK.pdb" age="2" guid="B7FC05ED-228A-48B4-BC44-3F0D4663E9CF" module="SqlDK" rva="0x35D7" />
<frame id="06" address="0x7FF8AC14F749" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F749" />
<frame id="07" address="0x7FF8AC14F557" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F557" />
<frame id="08" address="0x7FF8ABFDE9C4" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xE9C4" />
<frame id="09" address="0x7FF8ABFE7F78" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x17F78" />
<frame id="10" address="0x7FF8AC07B3FB" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xAB3FB" />
<frame id="11" address="0x7FF8ABFE8124" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x18124" />
<frame id="12" address="0x7FF8ABFDFDD0" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0xFDD0" />
<frame id="13" address="0x7FF8ABFF23FB" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x223FB" />
<frame id="14" address="0x7FF8ABFF2316" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22316" />
<frame id="15" address="0x7FF8AC1B7CBF" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x1E7CBF" />
<frame id="16" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="17" address="0x7FF8ABFF6C1C" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x26C1C" />
<frame id="18" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="19" address="0x7FF8ABFF6451" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x26451" />
<frame id="20" address="0x7FF8ABFF2479" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x22479" />
<frame id="21" address="0x7FF8AC0397F6" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x697F6" />
<frame id="22" address="0x7FF8AC039896" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x69896" />
<frame id="23" address="0x7FF8AC01D18C" pdb="sqlmin.pdb" age="2" guid="305B8C47-24EE-4521-AC4F-191D62EF2604" module="sqlmin" rva="0x4D18C" />
<frame id="24" address="0x7FF8A914B8C7" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xB8C7" />
<frame id="25" address="0x7FF8A914B5CF" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xB5CF" />
<frame id="26" address="0x7FF8A9152838" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x12838" />
<frame id="27" address="0x7FF8A9152A2E" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x12A2E" />
<frame id="28" address="0x7FF8A914D18F" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xD18F" />
<frame id="29" address="0x7FF8A914D5DC" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xD5DC" />
<frame id="30" address="0x7FF8A914CEF8" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xCEF8" />
<frame id="31" address="0x7FF8A914C45C" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xC45C" />
<frame id="32" address="0x7FF8A9F6EC9C" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE2EC9C" />
<frame id="33" address="0x7FF8A9F6E77D" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE2E77D" />
<frame id="34" address="0x7FF8A9F91078" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE51078" />
<frame id="35" address="0x7FF8A9F91851" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0xE51851" />
<frame id="36" address="0x7FF8A9157A85" pdb="sqllang.pdb" age="2" guid="A3178E23-7717-4447-81B8-DB4DA8A05088" module="sqllang" rva="0x17A85" />
</stackFrames>
<executionStack>
<frame procname="adhoc" queryhash="0x2e20f3dae991fbf0" queryplanhash="0x9efa265f12e2b42a" line="1" stmtstart="74" stmtend="660" sqlhandle="0x020000001f2a0e075b69ca2a243279c4c4834633565a80770000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 nvarchar(max),@P2 nvarchar(max))SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(@P1 AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(@P2 AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="281474978938880" dbid="21" objectname="e38d98d9-c55a-4980-b324-151a9e8f8e12.sys.sysschobjs" indexname="clst" id="lock24f87ec3280" mode="X" associatedObjectId="281474978938880">
<owner-list>
<owner id="process2580ce96108" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process24ce76d7c28" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="281474978938880" dbid="21" objectname="e38d98d9-c55a-4980-b324-151a9e8f8e12.sys.sysschobjs" indexname="clst" id="lock24f87ec3280" mode="X" associatedObjectId="281474978938880">
<owner-list>
<owner id="process24ce76d7c28" mode="S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process251a7113848" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="281474978938880" dbid="21" objectname="e38d98d9-c55a-4980-b324-151a9e8f8e12.sys.sysschobjs" indexname="clst" id="lock24fa3ee1a00" mode="X" associatedObjectId="281474978938880">
<owner-list>
<owner id="process251a7113848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2580ce96108" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Can any one help me resolving this issue.
March 8, 2024 at 3:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 8, 2024 at 3:05 pm
Save the xml in an .XDL file and open the deadlock graph using Solarwinds Plan Explorer.
It has nice replay features to help you detangle it all
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 8, 2024 at 3:42 pm
Hi,
Could you please install sp_BlitzLock and run it for a specific duration based on the size of your database? For example, if your database is a VLDB (Very Large Database), consider running it for the past 24 hours.
By analyzing the output, we might be able to identify patterns of deadlock occurrences. Additionally, sp_BlitzLock will provide insights into the queries that are victims of deadlock. This information could potentially help optimize performance through better indexing.
Here is the reference for Brent's script
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit
Thanks,
Abhinav
March 11, 2024 at 1:55 pm
Just trying to eyeball your deadlock, it looks like the deadlock is caused by a python script. Quick review of the deadlock XML, it kind of looks like you are running the python script more than once at the same time and it is requesting an exclusive lock on a table (sys.sysschobjs) and only 1 running query can have an exclusive lock on a table at a time. Normally, that would cause blocking, but deadlocks can also occur if you get into a situation where 2 or more queries are holding exclusive locks on objects and they are waiting on each other to finish to free up the lock (hence a deadlock).
I would look into the python script and schedule for it to see if it is running three times in a very short period of time (5 seconds approximately... if I am reading things right). Is this the expected behavior?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 11, 2024 at 10:41 pm
From ChatGPT 4:
The provided deadlock XML contains a lot of information, but let's focus on the core elements to diagnose and address the deadlock issue.
First, let's break down the XML content related to the deadlock:
1. Processes Involved:
- There are three processes involved in the deadlock: process24ce76d7c28, process251a7113848, and process2580ce96108.
2. Victim:
- The process chosen as the deadlock victim is process24ce76d7c28.
3. Resources:
- All three processes are waiting for a key lock on the same object "e38d98d9-c55a-4980-b324-151a9e8f8e12.sys.sysschobjs" which is an internal SQL Server table that stores object-related metadata.
4. Lock Modes:
- process24ce76d7c28 is requesting an S (Shared) lock but is blocked by process2580ce96108, which already has an X (Exclusive) lock.
- process251a7113848 is also requesting an S lock and is blocked by process24ce76d7c28, which has an S lock request but is waiting for the X lock held by process2580ce96108.
- process2580ce96108 is waiting for an S lock held by process251a7113848.
Here's a breakdown of the deadlock situation:
- process2580ce96108 holds X lock and waits for S lock (held by process251a7113848).
- process24ce76d7c28 (victim) is waiting for S lock (held by process2580ce96108).
- process251a7113848 holds X lock and waits for S lock (held by process24ce76d7c28).
These circular dependencies create a deadlock situation.
To resolve such deadlock issues, consider the following strategies:
1. Indexing: Make sure there are proper indexes on the tables being queried, which could reduce the time locks are held and decrease the likelihood of deadlocks.
2. Query Optimization: Sometimes rewriting the query can solve deadlock problems. For instance, ensuring that all queries access tables in the same order can prevent deadlocks.
3. Reducing Transaction Scope: Keep transactions as short as possible. The longer a transaction runs, the higher the chances it will be involved in a deadlock.
4. Locking Hints: Use SQL Server locking hints like ROWLOCK to force row-level locking rather than page-level or table-level might also help, but be cautious as this can lead to increased locking overhead.
5. Application Logic: Ensure that your application logic handles deadlocks appropriately. This usually involves retrying the transaction that was chosen as the victim.
6. Isolation Level: Evaluate if changing the transaction isolation level could prevent deadlocks. However, this could have other consequences on application concurrency which need to be carefully considered.
7. Concurrency Mechanisms: Implement optimistic concurrency control where possible, which could involve using SNAPSHOT isolation or implementing your application logic to handle versioning and conflicts.
8. Monitoring and Analysis: Keep monitoring deadlocks with extended events or SQL Server Profiler to understand patterns and root causes.
9. Escalated Locks Prevention: Ensure that your application's database operations do not unintentionally escalate locks from row-level to page-level or table-level.
10. Database Schema Review: Review the database schema for any denormalization that could reduce the complexity of transactions and thus the risk of deadlocks.
You might need a combination of these strategies to prevent deadlocks effectively. It is critical to understand the workload and access patterns of your application to choose the appropriate strategy or combination of strategies that fit your scenario. Always test the impact of these changes in a staging environment before deploying them to production.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply