Deadlock Troubleshooting, Part 2

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. 

 

(This post series is continued in Deadlock Troubleshooting, Part 3.)