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.
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).
Get the -T1222 output. Look at your errorlog now and you should see the trace flag 1222 output describing the deadlock.
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 = @p1Spid 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.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.)
Comments
- Anonymous
May 01, 2007
PingBack from http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx - Anonymous
March 05, 2009
Bart Sir, You are great, and I also worked for Microsoft in old willows building 10525.This was the great help for every Sql lover guys.Thanks and Keep Posted!! - Anonymous
June 02, 2009
(Sorry if I seem to have posted this twice - forgot to log in the first time!)This is a really useful post. One day the documentation that ships with the SQL server product will be equally clear...You mention the option of forcing one of the transactions to block early on the process, by doing something like:SELECT @x = COUNT() FROM t1 WITH (HOLDLOCK, UPDLOCK) WHERE c1 = @p1Would this in general:a) lock a single set of rows (i.e. the ones where C1=@p1) in the index cidx?b) effectively place a index wide lock?If it is a), then the issue I have is that the two processes are independent, (different users logged into different clients!) so the runtime values of @p1 for the two procedures are more than likely to be completely different - in fact the values apply to different columns in the table, so there is no relationship between the parameters for the procedures at all.In addition, the count() is likely to be expensive in some cases - what if there are millions of rows?. If the locking is an index wide lock, would this lock still be held, even if the value for @p1 was chosen such that there we no rows at all where c1=@p1?These may be silly questions !This is such black art stuff. - Anonymous
December 03, 2009
@stephen_or_doug,First, apologies for the delayed response. If there is an index on column [c1], then SQL will use a very selective index seek and only the rows where [c1]=@p1. But if there is no index that has [c1] as the leading column, SQL will need to scan the table or an index, and it will lock (and hold the lock on) every row that it scans. USE tempdb;GODROP TABLE t1;GOCREATE TABLE t1 (c1 int PRIMARY KEY, c2 int);INSERT INTO t1 SELECT [object_id], [object_id] FROM master.sys.objects;GOBEGIN TRAN;SELECT OBJECT_ID ('t1')SELECT COUNT() FROM t1 WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE c2 = 5;EXEC sp_lock;ROLLBACK;GOBEGIN TRAN;SELECT COUNT() FROM t1 WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE c1 = 5;EXEC sp_lock;ROLLBACK;GO - Anonymous
March 04, 2014
Thanks, man! This is a great testing tool to test your deadlock detection setup. Thanks for sharing.