Understanding Deadlock

Question

Understanding Deadlock

Rechana Rajan

SSCertifiable

Points: 7721
More actions
June 26, 2018 at 12:12 am

#371326

Dear Experts,
Myself created a deadlock situation to better understand it. The process created a deadlock involving multiple process but I run commands from only two query windows. Can anyone help understand the below deadlock graph?
<deadlock>
<victim-list>
<victimProcess id="process694076108" />
</victim-list>
<process-list>
<process id="process694076108" taskpriority="0" logused="0" waitresource="PAGE: 8:1:41648 " waittime="7688" ownerId="2948637736" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:37.480" XDES="0xc9b2e5ca0" lockMode="U" schedulerid="16" kpid="40332" status="suspended" spid="186" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:37.480" lastbatchcompleted="2018-06-24T11:09:37.480" lastattention="1900-01-01T00:00:00.480" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948637736" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="ADMIN.dbo.DEADLOCKSample" line="5" stmtstart="180" stmtend="396" sqlhandle="0x0300080067faae6bb0abb40009a9000001000000000000000000000000000000000000000000000000000000">
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=81 </frame>
<frame procname="adhoc" line="1" stmtstart="6" sqlhandle="0x01000800b2ebe610407ecdd80100000000000000000000000000000000000000000000000000000000000000">
exec DEADLOCKSample </frame>
</executionStack>
<inputbuf>
exec DEADLOCKSample </inputbuf>
</process>
<process id="process85a5bf088" taskpriority="0" logused="0" waitresource="PAGE: 8:1:35392 " waittime="7692" ownerId="2948637736" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:37.480" XDES="0xea7857c20" lockMode="U" schedulerid="4" kpid="50340" status="suspended" spid="186" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:37.480" lastbatchcompleted="2018-06-24T11:09:37.480" lastattention="1900-01-01T00:00:00.480" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948637736" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="ADMIN.dbo.DEADLOCKSample" line="5" stmtstart="180" stmtend="396" sqlhandle="0x0300080067faae6bb0abb40009a9000001000000000000000000000000000000000000000000000000000000">
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=81 </frame>
<frame procname="adhoc" line="1" stmtstart="6" sqlhandle="0x01000800b2ebe610407ecdd80100000000000000000000000000000000000000000000000000000000000000">
exec DEADLOCKSample </frame>
</executionStack>
<inputbuf>
exec DEADLOCKSample </inputbuf>
</process>
<process id="process6cca89468" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35696 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0xde1e7fca0" lockMode="U" schedulerid="2" kpid="48916" status="suspended" spid="171" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process7fd40e108" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35984 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0xea228b8e0" lockMode="U" schedulerid="3" kpid="53496" status="suspended" spid="171" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process2b69848c8" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35760 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0xe64bb7ca0" lockMode="U" schedulerid="11" kpid="47256" status="suspended" spid="171" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process315c4e8" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35920 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0xad2218c90" lockMode="U" schedulerid="1" kpid="53720" status="suspended" spid="171" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process854a0d848" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35856 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0xea787bc20" lockMode="U" schedulerid="13" kpid="52420" status="suspended" spid="171" sbid="0" ecid="8" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process28653a4e8" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35472 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0x528a83ca0" lockMode="U" schedulerid="5" kpid="48832" status="suspended" spid="171" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process3e8a27848" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35632 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0x4b3565590" lockMode="U" schedulerid="8" kpid="38732" status="suspended" spid="171" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="processaeb46d088" taskpriority="0" logused="1356" waitresource="PAGE: 8:1:35568 " waittime="764" ownerId="2948632673" transactionname="user_transaction" lasttranstarted="2018-06-24T11:11:34.397" XDES="0x29fbadca0" lockMode="U" schedulerid="10" kpid="47728" status="suspended" spid="171" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="process1e63468" taskpriority="0" logused="10000" waittime="765" schedulerid="9" kpid="49952" status="suspended" spid="171" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-06-24T11:11:34.397" lastbatchcompleted="2018-06-24T11:09:34.873" lastattention="1900-01-01T00:00:00.873" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" loginname="xrx\gfernandes" isolationlevel="read committed (2)" xactid="2948632673" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="26" stmtend="258" sqlhandle="0x020000005aadae1481153bdc1ae0a54a642ffd71e04fd97a0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="6" stmtstart="548" stmtend="764" sqlhandle="0x020000004ac9773a021044c22f776d192a90efc63b1589120000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=816
WAITFOR DELAY '00:00:10'
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=817
COMMIT TRAN
</inputbuf>
</process>
<process id="processa85a9c4e8" taskpriority="0" logused="10000" waittime="7686" schedulerid="6" kpid="53052" status="suspended" spid="186" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-06-24T11:11:37.480" lastbatchcompleted="2018-06-24T11:09:37.480" lastattention="1900-01-01T00:00:00.480" clientapp="Microsoft SQL Server Management Studio - Query" hostname="XRXCOK001" hostpid="9848" loginname="xrx\gfernandes" isolationlevel="read committed (2)" xactid="2948637736" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="ADMIN.dbo.DEADLOCKSample" line="5" stmtstart="180" stmtend="396" sqlhandle="0x0300080067faae6bb0abb40009a9000001000000000000000000000000000000000000000000000000000000">
UPDATE [ADMIN].[DBO].[TempTable1] SET NAME=NAME + NAME WHERE ID=81 </frame>
<frame procname="adhoc" line="1" stmtstart="6" sqlhandle="0x01000800b2ebe610407ecdd80100000000000000000000000000000000000000000000000000000000000000">
exec DEADLOCKSample </frame>
</executionStack>
<inputbuf>
exec DEADLOCKSample </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="41648" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="locke4b027680" mode="UIX" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process7fd40e108" mode="U" />
<owner id="process1e63468" mode="UIX" />
</owner-list>
<waiter-list>
<waiter id="process694076108" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35392" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock3480a8a00" mode="UIX" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process7fd40e108" mode="U" />
<owner id="process1e63468" mode="UIX" />
</owner-list>
<waiter-list>
<waiter id="process85a5bf088" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35696" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock9b9843200" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="process694076108" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process6cca89468" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35984" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lockc8b447180" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process7fd40e108" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35760" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock9cda89f80" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process2b69848c8" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35920" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock587a58800" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process315c4e8" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35856" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="locke1b0c7600" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process854a0d848" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35472" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock27c2048200" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process28653a4e8" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35632" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock4a4743080" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process3e8a27848" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="35568" dbid="8" subresource="FULL" objectname="ADMIN.DBO.TempTable1" id="lock1d8796600" mode="U" associatedObjectId="72057594046185472">
<owner-list>
<owner id="processa85a9c4e8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="processaeb46d088" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe87610ea80" WaitType="e_waitPipeGetRow" nodeId="1">
<owner-list>
<owner id="process28653a4e8" />
<owner id="processaeb46d088" />
<owner id="process3e8a27848" />
<owner id="process7fd40e108" />
<owner id="process6cca89468" />
<owner id="process315c4e8" />
<owner id="process2b69848c8" />
<owner id="process854a0d848" />
</owner-list>
<waiter-list>
<waiter id="process1e63468" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe348654a80" WaitType="e_waitPipeGetRow" nodeId="1">
<owner-list>
<owner id="process85a5bf088" />
<owner id="process694076108" />
</owner-list>
<waiter-list>
<waiter id="processa85a9c4e8" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

It says right at the top that the process with an id equal to this value process694076108 was chosen as a victim. You can then find the input buffers for that process to see which queries were being run. You can also see the other process and it's queries being run. You have multiple statements attempting to access the same resources. What's not immediately visible are statements prior to these within the batches being called which are doubtless taking out locks on other resources leading to the deadlock.

What specifically is your question?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 2

Grant Fritchey - Tuesday, June 26, 2018 5:11 AM
It says right at the top that the process with an id equal to this value process694076108 was chosen as a victim. You can then find the input buffers for that process to see which queries were being run. You can also see the other process and it's queries being run. You have multiple statements attempting to access the same resources. What's not immediately visible are statements prior to these within the batches being called which are doubtless taking out locks on other resources leading to the deadlock.
What specifically is your question?

Thanks Grant,

I can see around 10 processes in Process list, can you help me understand why these 10 processes when I ran only 2 queries?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Did either of the queries execute in parallel?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 4

Grant Fritchey - Wednesday, June 27, 2018 5:27 AM
Did either of the queries execute in parallel?

Yes the update is using parallelism..

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

There you go. Multiple threads.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning