{"id":126,"date":"2008-09-16T21:30:25","date_gmt":"2008-09-16T19:30:25","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=126"},"modified":"2008-09-16T21:30:25","modified_gmt":"2008-09-16T19:30:25","slug":"deadlock-graphs","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/09\/16\/deadlock-graphs\/","title":{"rendered":"Deadlock Graphs"},"content":{"rendered":"

A deadlock can appear to be a very obtuse error, as the only advice offered with the error message is an instruction to ‘rerun the transaction’, and there’s no apparent way to find the cause of the deadlock.<\/p>\n

There are in fact several ways to get information on what caused the deadlock and on what resources the deadlock occurred. This information is generally referred to as the ‘deadlock graph’<\/p>\n

Traceflag 1204<\/strong><\/p>\n

This is the oldest of the ways to get at the deadlock graph and it’s the main way used on SQL 2000. If traceflag 1204 is enabled server-wide, either by adding it as a startup parameter or by using the TraceOn command (DBCC TRACEON(1204,-1)), then anytime the SQL deadlock detector detects and resolves a deadlock, the information on that is written into the SQL Server error log. It looks something like this:<\/p>\n

[source:XML] Deadlock encountered …. Printing deadlock information
\nWait-for graph<\/p>\n

Node:1<\/p>\n

OBJECT: 12:629577281:0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CleanCnt:3 Mode:S Flags: 0x1
\nGrant List 2:
\nOwner:0x000000008673B900 Mode: S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Flg:0x40 Ref:2 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x0000000085F64ED0
\nSPID: 52 ECID: 0 Statement Type: UPDATE Line #: 11
\nInput Buf: Language Event: exec CauseDeadlock
\nRequested by:
\nResType:LockOwner Stype:’OR’Xdes:0x0000000085F65740 Mode: IX SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0000000085F5C538) Value:0x85b66a00 Cost:(0\/0)<\/p>\n

Node:2<\/p>\n

OBJECT: 12:629577281:0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CleanCnt:3 Mode:S Flags: 0x1
\nGrant List 2:
\nOwner:0x00000000867FA180 Mode: S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Flg:0x40 Ref:2 Life:02000000 SPID:53 ECID:0 XactLockInfo: 0x0000000085F65780
\nSPID: 53 ECID: 0 Statement Type: UPDATE Line #: 11
\nInput Buf: Language Event: exec CauseDeadlock
\nRequested by:
\nResType:LockOwner Stype:’OR’Xdes:0x0000000085F64E90 Mode: IX SPID:52 BatchID:0 ECID:0 TaskProxy:(0x00000000810B8538) Value:0x85b66a40 Cost:(0\/0)<\/p>\n

Victim Resource Owner:
\nResType:LockOwner Stype:’OR’Xdes:0x0000000085F65740 Mode: IX SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0000000085F5C538) Value:0x85b66a00 Cost:(0\/0)\u00a0[\/source]<\/p>\n

It’s hard to read, but all the information on what statement finally caused the deadlock and what locks were held and requested is there.<\/p>\n

In more recent version of SQL, there are other ways to get at that info, some giving more info, some just giving it in different ways.<\/p>\n

Traceflag 1222<\/strong><\/p>\n

Much the same as the previous one, enabling traceflag 1222 server-wide, either as a startup parameter or using the TRACEON command, writes deadlock info into the error log.<\/p>\n

TraceFlag 1222 puts far more information into the error log than 1204 did, and as such is preferred over 1204 on SQL 2005 and higher.<\/p>\n

[source:XML]
\ndeadlock-list
\n deadlock victim=process4aedb88
\n process-list
\n process id=process4aedb88 taskpriority=0 logused=0 waitresource=OBJECT: 12:629577281:0 waittime=4370 ownerId=7679 transactionname=user_transaction lasttranstarted=2008-09-16T20:16:58.470 XDES=0x80692e90 lockMode=IX schedulerid=4 kpid=4460 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=3 lastbatchstarted=2008-09-16T21:26:27.507 lastbatchcompleted=2008-09-16T20:17:03.160 lastattention=2008-09-16T20:16:59.430 clientapp=Microsoft SQL Server Management Studio – Query hostname=MYRLIN hostpid=4328 loginname=AVALON\\Gail isolationlevel=repeatable read (3) xactid=7679 currentdb=12 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
\n executionStack
\n frame procname=Testing.dbo.CauseDeadlock line=11 stmtstart=330 stmtend=470 sqlhandle=0x03000c00a54369232632d600169b00000100000000000000
\nupdate LargeTable Set SomeString = ‘hys’ where SomeString like ‘a%’
\n frame procname=adhoc line=1 sqlhandle=0x01000c00e5ffdf06d08a0e80000000000000000000000000
\nexec CauseDeadlock
\n inputbuf
\nexec CauseDeadlock
\n process id=process806fce08 taskpriority=0 logused=0 waitresource=OBJECT: 12:629577281:0 waittime=3213 ownerId=19635 transactionname=user_transaction lasttranstarted=2008-09-16T21:26:23.457 XDES=0x84e09970 lockMode=IX schedulerid=4 kpid=2016 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2008-09-16T21:26:23.457 lastbatchcompleted=2008-09-16T21:26:20.057 clientapp=Microsoft SQL Server Management Studio – Query hostname=MYRLIN hostpid=4328 loginname=AVALON\\Gail isolationlevel=repeatable read (3) xactid=19635 currentdb=12 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
\n executionStack
\n frame procname=Testing.dbo.CauseDeadlock line=11 stmtstart=330 stmtend=470 sqlhandle=0x03000c00a54369232632d600169b00000100000000000000
\nupdate LargeTable Set SomeString = ‘hys’ where SomeString like ‘a%’
\n frame procname=adhoc line=1 sqlhandle=0x01000c00bf7e3f20a0890e80000000000000000000000000
\nexec CauseDeadlock
\n inputbuf
\nexec CauseDeadlock
\n resource-list
\n objectlock lockPartition=0 objid=629577281 subresource=FULL dbid=12 objectname=Testing.dbo.LargeTable id=lock80106f00 mode=S associatedObjectId=629577281
\n owner-list
\n owner id=process806fce08 mode=S
\n waiter-list
\n waiter id=process4aedb88 mode=IX requestType=convert
\n objectlock lockPartition=0 objid=629577281 subresource=FULL dbid=12 objectname=Testing.dbo.LargeTable id=lock80106f00 mode=S associatedObjectId=629577281
\n owner-list
\n owner id=process4aedb88 mode=S
\n waiter-list
\n waiter id=process806fce08 mode=IX requestType=convert
\n[\/source]
\nThere’s a lot more info in there than in the output via traceflag 1204, including the SQL handles (useful for extracting info via the sys.dm_exec_sql_text DMV), the isolation level, the times of the batches, the client app, hostname and login name.<\/p>\n

Profiler<\/strong><\/p>\n

The other way to get deadlock info, especially if the deadlock can be reproduced on demand, is to use SQL Profiler and capture the deadlock graph event (new in SQL 2005). In older versions of SQL, the deadlock chain event can be used, but it’s a little obtuse.<\/p>\n

When profiler records a deadlock graph event, it can write out a deadlock file (.xdl) which can be opened in management studio and examined. The graphs produced look something like this:<\/p>\n

\"\"<\/p>\n

I tend to prefer the output of traceflag 1222 when working with deadlocks. all the information is there and is easy to see and copy<\/p>\n","protected":false},"excerpt":{"rendered":"

A deadlock can appear to be a very obtuse error, as the only advice offered with the error message is an instruction to ‘rerun the transaction’, and there’s no apparent way to find the cause of the deadlock. There are… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15],"tags":[],"class_list":["post-126","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-22","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=126"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/126\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}