Deadlock Graphs

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 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’

Traceflag 1204

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:

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.

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:

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

Share this:

12 Comments

Raj27 January 2009 at 15:42

Hi,

Nice article..rather nice articles 🙂

I tried the DBCC TRACEON(1204,-1).
I added it on the query analyzer as first stmt and after that on the same connection(Query analyzer window) i created a deadlock and made that process crash.
My errorlog just has this info

‘DBCC TRACEON 1204, server process ID (SPID) 55’

Thats it and Not all the useful info you have mentioned.
Is there anyother way i need to see the error log. I am seeing if from EM( also checked C:\Program Files\Microsoft SQL Server\MSSQL\LOG folder) no luck..

Please help where can i see the deadlock graph as i need it badly..thanks in advance..

Does it have to do anythng with SQL versions?
I use sql2000 developer version..

Yes I did get the error. Let me briefly explain how i mananged to create the deadlock.

t1,t2 are my tables with a single column called id
On the first query analyzer I had the following script
**************************
dbcc traceon(1204,-1)
begin tran
update t2 set id = 50 where id = 5
select * from t1
commit
********************
I executed first 3 stmts that is till ‘update’

2nd qry window had the following
**************************************

Try enabling the traceflag in a separate window, before doing the updates.

The -1 parameter means the flag is enabled globally (for all sessions), so there’s no need to do it in both and, in fact no point, as it’s the system spids that detect deadlocks and generate the graph, not your user connections

Thank you for the aticle.I have been using 1222 for a while now,but i do not know how to make use of the sqlhandle being displayed.How do i make use of it to see more details?Do I run something like select * from sys.dm_exec_sql_text (sql_handle)?

You can run that. It’ll only show results if the plan for that query is still in cache. If not then the sql_handle’s fairly useless and you’ll have to go on what pieces of the query are shown in the deadlock graph.

I haven’t seen a performance degradation from these and I used to work on a very busy, very critical trading system that had traceflag 1204 enabled permanently. I’d say unless you’re having multiple deadlocks a sec there shouldn’t be an impact. If you are having multiple deadlocks a sec, then I would suggest there’s a bigger problem at hand.