deadlock victim checking?

Question

Post reply

deadlock victim checking?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 18, 2012 at 5:53 am

#267874

Hi,
first enable DBCC traceon (1204, -1), deadlock trace flag.
I have tested deadlock victim manually
catpture the deadlock by using extent event script
SELECT CAST(event_data.value('(event/data/value)[1]',
'varchar(max)') AS XML) AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS event_data
FROM ( -- Cast the target_data to XML
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
) AS Data -- Split out the Event Nodes
CROSS APPLY TargetData.nodes('RingBufferTarget/
event[@name="xml_deadlock_report"]')
AS XEventData ( XEvent )
) AS tab ( event_data )
<deadlock>
<victim-list>
<victimProcess id="process38f558" />
</victim-list>
<process-list>
<process id="process38f558" taskpriority="0" logused="296" waitresource="KEY: 2:1008806317064323072 (010086470766)" waittime="1083" ownerId="229254" transactionname="user_transaction" lasttranstarted="2012-09-18T16:59:05.733" XDES="0x89535a0" lockMode="U" schedulerid="2" kpid="2304" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2012-09-18T16:59:22.443" lastbatchcompleted="2012-09-18T16:59:05.733" clientapp="Microsoft SQL Server Management Studio - Query" hostname="W7JMN0120753" hostpid="4936" loginname="IN\ananda.narayanan" isolationlevel="read committed (2)" xactid="229254" currentdb="9" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="" line="2" stmtstart="32" sqlhandle="0x02000000ccbdad23abdeaef9a6ee3fbbadda4932c1987192" />
<frame procname="" line="2" stmtstart="32" sqlhandle="0x02000000961a7e1361e91bbf3e1924ad606322a106ceebf2" />
</executionStack>
<inputbuf>
BEGIN TRAN
UPDATE ##test2 SET id = 1 </inputbuf>
</process>
<process id="process38fc78" taskpriority="0" logused="296" waitresource="KEY: 2:936748722985435136 (010086470766)" waittime="11499" ownerId="229272" transactionname="user_transaction" lasttranstarted="2012-09-18T16:59:12.050" XDES="0x8953be8" lockMode="U" schedulerid="2" kpid="4708" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T16:59:12.043" lastbatchcompleted="2012-09-18T16:58:04.347" clientapp="Microsoft SQL Server Management Studio - Query" hostname="W7JMN0120753" hostpid="4936" loginname="IN\ananda.narayanan" isolationlevel="read committed (2)" xactid="229272" currentdb="9" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="" line="3" stmtstart="94" sqlhandle="0x02000000cc337b2daf8db0a7ef1b889e2b35e2ee49a07ed2" />
<frame procname="" line="3" stmtstart="94" sqlhandle="0x02000000f9ae99176f1093b47914fea4afdbb0d63db66cef" />
</executionStack>
<inputbuf>
BEGIN TRAN
UPDATE ##test2 SET id = 2
UPDATE ##test1 SET id = 2
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="1008806317064323072" dbid="2" objectname="" indexname="" id="lock5242480" mode="X" associatedObjectId="1008806317064323072">
<owner-list>
<owner id="process38fc78" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process38f558" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="936748722985435136" dbid="2" objectname="" indexname="" id="lock7266c00" mode="X" associatedObjectId="936748722985435136">
<owner-list>
<owner id="process38f558" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process38fc78" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
here there is no object id, then how to resolve this deadlock?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You don't need traceflag 1204 if you're using extended events to get the deadlock.

There's probably no object name because it's a temp table and dropped before the deadlock graph was fetched.

You can see from the update statements which tables are involved.

##test2

##test1

Why global temp tables? What's the entire block of code (looks like ad-hoc or dynamic SQL. Probably ad-hoc)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

ok, thanks for reply... I learned today about traceflag no use, if using exented events in SQL 2008.

Pl. confirm...

Does Background process LOCK MONITOR to handle the deadlock and detect automatically when deadlock happend. so ne need to user action.

In this cause checking this extent event script if no deadlock graph comming in xml format, does it means deadlock automatically cleared through LOCK MONITOR process.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Not following you.

Deadlock detection and resolution is automatic.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Should I run extented event script at production database? is there any restriction?

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Depends. Are you seeing deadlocks on production that you want to investigate and resolve?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass