How to prevent deadlocks

Question

How to prevent deadlocks

mbender

SSCrazy

Points: 2595
More actions
July 31, 2012 at 1:00 pm

#264409

We have been noticing more deadlocks in our database what kind of steps could we do to identify why?
Thanks for any help you could give.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Try running a trace on the server using the locks template this should give u the deadlock graph and help identify the tables that are causing it. After that you can look at the query , indexes etc to try and reduce the occurence

Jayanth Kurup[/url]

mbender SSCrazy Points: 2595 More actions · Answer 2

mbender

SSCrazy

Points: 2595

July 31, 2012 at 2:56 pm

#1519983

awesome thanks

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

No need for a trace. On SQL 2008 deadlock graphs are written into the extended events system health session.

Post the deadlock graph here, we can give you some suggestions on why it's happening.

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

mbender SSCrazy Points: 2595 More actions · Answer 4

GilaMonster (7/31/2012)
No need for a trace. On SQL 2008 deadlock graphs are written into the extended events system health session.
Post the deadlock graph here, we can give you some suggestions on why it's happening.

Where do i find this?

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

Books Online -> extended events, or read over Jonathan Kehayias's blog

A very quick google search turned up:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/13/an-xevent-a-day-13-of-31-the-system-health-session.aspx

http://msdn.microsoft.com/en-us/library/bb630282.aspx

http://msdn.microsoft.com/en-us/library/dd822788%28v=sql.100%29.aspx

and of particular relevance

http://www.sqlskills.com/BLOGS/PAUL/post/Getting-historical-deadlock-info-using-extended-events.aspx

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

mbender SSCrazy Points: 2595 More actions · Answer 6

mbender

SSCrazy

Points: 2595

July 31, 2012 at 3:17 pm

#1519995

Thanks reading it now.

mbender SSCrazy Points: 2595 More actions · Answer 7

here is an example graph

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1

KEY: 7:72057594123321344 (91002937eff3) CleanCnt:2 Mode:U Flags: 0x1

Grant List 1:

Owner:0x000000009566DB00 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:51 ECID:0 XactLockInfo: 0x000000022E5C43F0

SPID: 51 ECID: 0 Statement Type: MERGE Line #: 3

Input Buf: Language Event: DECLARE @IgsPKey INT; SET @IgsPKey=207808 DECLARE @SessionVariableUpdate TABLE ( VariablePKey INT IDENTITY, VariableName VARCHAR(500), VariableData VARCHAR(4000) ) BEGIN INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES

Requested by:

ResType:LockOwner Stype:'OR'Xdes:0x00000000AB3A3970 Mode: U SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000003394A0538) Value:0x9535a300 Cost:(0/0)

Node:2

KEY: 7:72057594123321344 (06005a3afcd0) CleanCnt:2 Mode:U Flags: 0x1

Grant List 2:

Owner:0x0000000137A4CDC0 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x00000000AB3A39B0

SPID: 63 ECID: 0 Statement Type: MERGE Line #: 3

Input Buf: Language Event: DECLARE @IgsPKey INT; SET @IgsPKey=207798 DECLARE @SessionVariableUpdate TABLE ( VariablePKey INT IDENTITY, VariableName VARCHAR(500), VariableData VARCHAR(4000) ) BEGIN INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES

Requested by:

ResType:LockOwner Stype:'OR'Xdes:0x000000022E5C43B0 Mode: U SPID:51 BatchID:0 ECID:0 TaskProxy:(0x000000030D0B4538) Value:0x92f3c2c0 Cost:(0/0)

Victim Resource Owner:

ResType:LockOwner Stype:'OR'Xdes:0x00000000AB3A3970 Mode: U SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000003394A0538) Value:0x9535a300 Cost:(0/0)

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

That's the output given by traceflag 1204, which is a SQL 2000 and before trace flag. Near-impossible to interpret and missing information.

Please get the deadlock graph from the extended events system health session (as per articles linked earlier) or turn traceflag 1204 off and turn traceflag 1222 on and post the deadlock graph from that.

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

mbender SSCrazy Points: 2595 More actions · Answer 9

mbender

SSCrazy

Points: 2595

August 14, 2012 at 9:10 am

#1525590

Ok will do

mbender SSCrazy Points: 2595 More actions · Answer 10

Based on the following query:

SELECT CAST (

REPLACE (

XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),

'<victim-list>', '<deadlock><victim-list>'),

'<process-list>', '</victim-list><process-list>')

AS XML) AS DeadlockGraph

FROM (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') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

It created:

<deadlock-list>

<deadlock>

<victim-list>

<victimProcess id="process45f3dc8" />

</victim-list>

<process-list>

<executionStack>

<frame procname="" line="3" stmtstart="18250" sqlhandle="0x020000003fb8481540037b7c772ceebac4d87e1d489f1e8d" />

</executionStack>

<inputbuf>

DECLARE @IgsPKey INT; SET @IgsPKey=207798 DECLARE @SessionVariableUpdate TABLE ( VariablePKey INT IDENTITY, VariableName VARCHAR(500), VariableData VARCHAR(4000) ) BEGIN INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('ContentPath','http://www.exampleintranet.com') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeSessionID','825614') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeUsername','jdoe1') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeNumber','9998') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeName','John Doe 1') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeEmail','jdoe1@email.com') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeHomeOffice','Cincinnati') INSE </inputbuf>

</process>

<executionStack>

<frame procname="" line="3" stmtstart="15274" sqlhandle="0x020000001ef1e900202b46da52796d9699d3707a96886c78" />

</executionStack>

<inputbuf>

DECLARE @IgsPKey INT; SET @IgsPKey=207808 DECLARE @SessionVariableUpdate TABLE ( VariablePKey INT IDENTITY, VariableName VARCHAR(500), VariableData VARCHAR(4000) ) BEGIN INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('ContentPath','http://www.seitrakker.com/intranet/') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeSessionID','825625') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeUsername','jdoe2') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeNumber','9999') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeName','Randy von Steinen') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeEmail','jdoe2@email.com') INSERT INTO @SessionVariableUpdate (VariableName,VariableData) VALUES ('EmployeeHomeOffice','Grand </inputbuf>

</process>

</process-list>

<resource-list>

<keylock hobtid="72057594123321344" dbid="7" objectname="" indexname="" id="lock9461fa00" mode="U" associatedObjectId="72057594123321344">

<owner-list>

<owner id="process9cddebc8" mode="U" />

</owner-list>

<waiter-list>

<waiter id="process45f3dc8" mode="U" requestType="wait" />

</waiter-list>

</keylock>

<keylock hobtid="72057594123321344" dbid="7" objectname="" indexname="" id="lock9480d580" mode="U" associatedObjectId="72057594123321344">

<owner-list>

<owner id="process45f3dc8" mode="U" />

</owner-list>

<waiter-list>

<waiter id="process9cddebc8" mode="U" requestType="wait" />

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

Greg Bonham Newbie Points: 7 More actions · Answer 11

I'm having a similar issue trying to resolve deadlocks. Any ideas based off what mbender posted?