Help with deadlock

[g***@gmail.com]

Hi, there is a deadlock happening from time to time. I got the deadlock xml but still could not figure out the issue, anyone can help? Thanks in advance.

<deadlock>
<victim-list>
<victimProcess id="process5809288" />
</victim-list>
<process-list>
<process id="process583abc8" taskpriority="0" logused="10000" waittime="3153" schedulerid="6" kpid="4860" status="suspended" spid="119" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-06-12T15:50:49.810" lastbatchcompleted="2014-06-12T15:50:12.293" clientapp="Microsoft Office 2003" hostname="JJGSUANGONG2" hostpid="492" loginname="sql_jjg_zht" isolationlevel="read committed (2)" xactid="1964467" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="" line="51" stmtstart="4160" stmtend="4304" sqlhandle="0x03000700a18ba90e759f1401caa200000000000000000000" />
<frame procname="" line="1" stmtstart="204" sqlhandle="0x02000000ddf989041086f85ba929e56bccdb4f895b446db9" />
<frame procname="" line="1" stmtstart="44" stmtend="338" sqlhandle="0x02000000faf253002a880beae495040f98641c8ea0baaf9b" />
</executionStack>
<inputbuf>
begin try; begin tran;update dbo.myTable set my_col_1 = '10.8', modified_by='jjg_zht',my_col_4=0,my_col_3=4.51999998092651,modified_date='2014-6-12 15:52:06', skip_rule=1 where id=112674 and serial_no=4; commit tran; update dbo.SQL_execution_table set status='ok',err_msg='' where type='JJGSUANGONG2'; end try begin catch; declare @msg nvarchar(500); select @msg='Error:'+ERROR_MESSAGE(); RAISERROR 50001 @msg; rollback; insert into dbo.SQL_error_table(name,mySql,comment,last_modified,modified_by, computer) select 'myTable_data_modified','update dbo.myTable set my_col_1 = ''10.8'', modified_by=''jjg_zht'',my_col_4=0,my_col_3=4.51999998092651,modified_date=''2014-6-12 15:52:06'', skip_rule=1 where id=112674 and serial_no=4; commit tran; end try begin catch; declare @msg nvarchar(500); select @msg=''Error:''+ERROR_MESSAGE(); RAISERROR 50001 @msg; rollback; end catch;',@msg,getdate(),'jjg_zht','JJGSUANGONG2'; if exists(select 1 from dbo.SQL_execution_table with(nolock) where type='JJGSUANGONG2') begin update dbo.SQL_execution_table set status='error',err_msg=@msg where type='JJGSUANGONG2' end else insert into dbo.SQL_execution_table(status, err_msg, type) select 'error', @msg, 'JJGSUANGONG2'; end catch;
</inputbuf>
</process>
<process id="process581ce08" taskpriority="0" logused="10000" waittime="3148" schedulerid="3" kpid="4828" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-06-12T15:50:49.797" lastbatchcompleted="2014-06-12T15:50:42.900" clientapp="Microsoft Office 2003" hostname="DUANLIMEI" hostpid="3160" loginname="sql_jjg_dlm" isolationlevel="read committed (2)" xactid="1964465" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="" line="51" stmtstart="4160" stmtend="4304" sqlhandle="0x03000700a18ba90e759f1401caa200000000000000000000" />
<frame procname="" line="1" stmtstart="204" sqlhandle="0x02000000bd4bd915025b92ea8601872d7c1137175f13b441" />
<frame procname="" line="1" stmtstart="44" stmtend="352" sqlhandle="0x02000000472cf804fc3fc68eacfe495096c5ae75979732ea" />
</executionStack>
<inputbuf>
begin try; begin tran;update dbo.myTable set my_col_2 = '740', modified_by='jjg_dlm',my_col_4=65535,my_col_3=1.60000002384186,modified_date='2014-6-12 15:50:09', skip_rule=1 where id=112526 and serial_no=5; commit tran; update dbo.SQL_execution_table set status='ok',err_msg='' where type='DUANLIMEI'; end try begin catch; declare @msg nvarchar(500); select @msg='Error:'+ERROR_MESSAGE(); RAISERROR 50001 @msg; rollback; insert into dbo.SQL_error_table(name,mySql,comment,last_modified,modified_by, computer) select 'myTable_data_modified','update dbo.myTable set my_col_2 = ''740'', modified_by=''jjg_dlm'',my_col_4=65535,my_col_3=1.60000002384186,modified_date=''2014-6-12 15:50:09'', skip_rule=1 where id=112526 and serial_no=5; commit tran; end try begin catch; declare @msg nvarchar(500); select @msg=''Error:''+ERROR_MESSAGE(); RAISERROR 50001 @msg; rollback; end catch;',@msg,getdate(),'jjg_dlm','DUANLIMEI'; if exists(select 1 from dbo.SQL_execution_table with(nolock) where type='DUANLIMEI') begin update dbo.SQL_execution_table set status='error',err_msg=@msg where type='DUANLIMEI' end else insert into dbo.SQL_execution_table(status, err_msg, type) select 'error', @msg, 'JJGSUANGONG2'; end catch;
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="335743" dbid="7" objectname="" id="lock6e6e900" mode="U" associatedObjectId="72057594901168128">
<owner-list>
<owner id="process581ce08" mode="U" />
<owner id="process581ce08" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process5830748" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="333477" dbid="7" objectname="" id="lock6ac5e00" mode="IX" associatedObjectId="72057594901168128">
<owner-list>
<owner id="process583abc8" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process5827048" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe12df98780" WaitType="e_waitPipeGetRow" nodeId="3">
<owner-list>
<owner id="process5813048" />
<owner id="process5827288" />
<owner id="process5845708" />
<owner id="process584fdc8" />
<owner id="process5830748" />
<owner id="process583b4c8" />
<owner id="process5809288" />
<owner id="process581db88" />
</owner-list>
<waiter-list>
<waiter id="process583abc8" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1aec3e780" WaitType="e_waitPipeGetRow" nodeId="3">
<owner-list>
<owner id="process5827048" />
</owner-list>
<waiter-list>
<waiter id="process581ce08" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>

[Erland Sommarskog]

Post by g***@gmail.com
Hi, there is a deadlock happening from time to time. I got the deadlock
xml but still could not figure out the issue, anyone can help? Thanks in
advance.

It seems that the first UPDATE statement in the batch fires a trigger, which
either fires a new trigger or call a stored procedure. And when execution
reaces line 51 in that third-level object there is a deadlock, because the
processes are locking each other's resources.

This SELECT should reveal the exact statements, provided that the plans
are still in cache:

SELECT object_name(objectid), substring(text, 4160/2 + 1, 4304/2 + 1)
FROM
sys.dm_exec_sql_text(0x03000700a18ba90e759f1401caa200000000000000000000)