Deadlock Troubleshooting (2)

http://blogs.msdn.com/b/bartd/archive/2006/09/13/751343.aspx

In this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in Deadlock Troubleshooting, Part 1 so you can see them in action.  This is a simplified version of a deadlock scenario that an internal customer here at Microsoft called us for help with.  To set up the scenario, run this:

 

       -- Batch #1

       CREATE DATABASE deadlocktest

       GO

       USE deadlocktest

       SET NOCOUNT ON

       DBCC TRACEON ( 1222, - 1)

       GO

       IF OBJECT_ID ( 't1' ) IS NOT NULL DROP TABLE t1

       IF OBJECT_ID ( 'p1' ) IS NOT NULL DROP PROC p1

       IF OBJECT_ID ( 'p2' ) IS NOT NULL DROP PROC p2

       GO

       CREATE TABLE t1 ( c1 int , c2 int , c3 int , c4 char ( 5000))

       GO

       DECLARE @x int

       SET @x = 1

       WHILE ( @x <= 1000) BEGIN

         INSERT INTO t1 VALUES ( @x* 2, @x* 2, @x* 2, @x* 2)

         SET @x = @x + 1

       END

       GO

       CREATE CLUSTERED INDEX cidx ON t1 ( c1)

       CREATE NONCLUSTERED INDEX idx1 ON t1 ( c2)

       GO

       CREATE PROC p1 @p1 int AS

       SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+ 1

       GO

       CREATE PROC p2 @p1 int AS

       UPDATE t1 SET c2 = c2+ 1 WHERE c1 = @p1

       UPDATE t1 SET c2 = c2- 1 WHERE c1 = @p1

       GO

 

Now, run this from another connection:

       -- Batch #2

       USE deadlocktest

       SET NOCOUNT ON

       WHILE ( 1= 1)

         EXEC p2 4

       GO

 

Finally, leave that one running while you run this from a third connection:

       -- Batch #3

       USE deadlocktest

       SET NOCOUNT ON

       CREATE TABLE #t1 ( c2 int , c3 int )

       GO

       WHILE ( 1= 1) BEGIN

         INSERT INTO #t1 EXEC p1 4

         TRUNCATE TABLE #t1

       END

       GO

 

This will cause a deadlock; you should see one of the batches aborted by a 1205 error.  Now that we have a reproducible deadlock, I’ll follow the troubleshooting steps that I posted in Deadlock Troubleshooting, Part 1 .  

 

1. Turn on trace flag 1222 .  The setup script already turned this on for you as a global flag (the “-1” in the dbcc traceon command is critical). 
2. Get the -T1222 output .   Look at your errorlog now and you should see the trace flag 1222 output describing the deadlock.  
3. Decode the -T1222 output .  Read through Deadlock Troubleshooting, Part 1 again if you need more information about how to interpret -T1222 or -T1204 output.   Here’s what you should end up with after sifting through the -T1222 details and extracting the most important tidbits:
   
               Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
                                    SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
                    Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
                                    UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
                   
                    Spid X is waiting for a Shared KEY lock on index t1.cidx.  Spid Y holds a conflicting X lock.
                    Spid Y is waiting for an eXclusive KEY lock on index t1.idx1.  Spid X holds a conflicting S lock.
   
   
4. Run the queries through Database Tuning Advisor .   The -T1222 output tell us what inputbuffer we were running at the time of the deadlock (“ EXEC p1 4 ” and “ EXEC p2 4 ”).  Tune each of these queries in DTA using the steps I discussed in Part 1.   DTA will recommend a new index for Batch 3.  Create the index by selecting "Apply Recommendations" from the Action drop-down menu.

At this point, if you re-run Batch 2 and Batch 3, you’ll find that the deadlock has been solved.   You didn’t even have to use steps 5-8 or the list of other deadlock avoidance strategies that I listed in Part 1 of this series of posts.  

 

In a subsequent post I'll look at the details of the query plans involved in this particular deadlock to understand what caused the deadlock and why DTA's proposed index fixed it.