Welcome to Aussie SQL Server Bloggers Sign in | Join | Help

A good article on trouble-shooting deadlocks

Mitch Wheat pointed out a good article on his blog recently on how to troubleshoot deadlocks, written by Microsoft PSS escalation engineer Bart Duncan.

This article is definitely worth a read, but two observations I'd make about this article are:

(a) I'd perform step 7 first, as query inefficiencies should always be inspected when analysing deadlock scenarios & are most likely to fix the problem if identified. Simply using the DTA as Bart suggests leaves you having learned little from the experience if it does manage to solve the problem

(b) I think Barrt is incorrect in point 8 by saying that -T1204 only reports on the two closing statements. I have seen many deadlock graphs from this trace event that showed more than two contributing nodes.. afaik, -T1204 is a concise report on ALL nodes in a deadlock.

Other than these relatively minor points this is an excellent article, covering all the major steps you need to follow to identify the cause of deadlocks & resolve them.

Published Wednesday, 20 September 2006 12:01 AM by Greg_Linwood
Filed Under:

Comments

# re: A good article on trouble-shooting deadlocks

Hi Mitch!  I'll buy (a), but I think comment (b) may be based on a misreading/misunderstanding (maybe my post was ambiguous).  Trace flag 1204 (and trace flag 1222) definitely only show the final T-SQL statement -- the one that got blocked -- when multi-statement transactions are involved.  If a prior statement that was exectuted within the same transaction acquired one of the locks that ended up becoming a blocking lock, you are left to guess about which statement may have been the one that acquired the lock.  

Note that I agree with your statement that "T1204 is a concise report on ALL nodes in a deadlock".  But, a node in the wait graph just represents a lock resource.  Query text is only available for the currently-executing statement for each spid, not for each node.

Put another way, suppose you had this deadlock:

Step 1:
  Spid 50: BEGIN TRAN
  Spid 51: BEGIN TRAN

Step 2:
  Spid 50: DELETE A
  Spid 51: UPDATE B SET ...

Step 3:
  Spid 50: SELECT * FROM B (blocked by Spid 51)
  Spid 51: SELECT * FROM A (blocked by Spid 50)

The -T1222/-T1204 output would *only* show the two SELECT statements, not the DELETE or the UPDATE statements that acquired the blocking locks.  If you didn't know your app well enough to know that the DELETE/UPDATE preceded the two blocked SELECTs, you'd have to capture a profiler trace to get a full accounting of the deadlock.

Thx!
Bart
Thursday, 27 December 2007 4:31 PM by bartduncan
Anonymous comments are disabled