A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed. When the deadlock monitor thread in SQL Server detects a circular blocking chain, it selects one of the participants as a victim, cancels that spid’s current batch, and rolls backs his transaction in order to let the other spids continue with their work. The deadlock victim will get a 1205 error:
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
A deadlock is a special type of blocking scenario, but blocking and deadlocking are not the same thing. Sometimes we have people report that they are experiencing "deadlocking" when they are really only seeing blocking.
With very few exceptions, deadlocks are a natural side effect of blocking, not a SQL Server bug. The typical deadlock solution is either a stored proc/app code tweak, or a schema/indexing change.
Here’s how to troubleshoot deadlocks. These steps apply to most deadlocks, and they’ll allow you to resolve many of them without even having to dig into query plans or other nitty gritty details. What’s that? You like digging into query plans, and have nitty grits for breakfast every morning? OK then, we’ll look at a deadlock scenario from the inside out a bit later. But first, here are the basics:
process id=processdceda8 taskpriority=0 logused=0 waitresource=KEY: 2:72057594051493888 (0400a4427a09) waittime=5000 ownerId=24008914 transactionname=SELECT lasttranstarted=2006-09-08T15:54:22.327 XDES=0x8fd9a848 lockMode=S schedulerid=1 kpid=4404 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2006-09-08T15:54:22.293 lastbatchcompleted=2006-09-08T15:54:22.293 clientapp=OSQL-32 hostname=BARTD2 hostpid=3408 loginname=bartd isolationlevel=read committed (2) xactid=24008914 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
frame procname=tempdb.dbo.p1 line=2 stmtstart=60 sqlhandle=0x03000200268be70bd
SELECT c2, c3 FROM t1 WHERE c2 = @p1
frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000008a4df52d3
EXEC p1 3
EXEC p1 3
process id=process3c54c58 taskpriority=0 logused=16952 waitresource=KEY: 2:72057594051559424 (0900fefcd2fe) waittime=5000 ownerId=24008903 transactionname=UPDATE lasttranstarted=2006-09-08T15:54:22.327 XDES=0x802ecdd0 lockMode=X schedulerid=2 kpid=4420 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2006-09-08T15:54:22.327 lastbatchcompleted=2006-09-08T15:54:22.310 clientapp=OSQL-32 hostname=BARTD2 hostpid=2728 loginname=bartd isolationlevel=read committed (2) xactid=24008903 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
frame procname=tempdb.dbo.p2 line=2 stmtstart=58 sqlhandle=0x030002005fafdb0c
UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1
frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000006f878816
EXEC p2 3
EXEC p2 3
keylock hobtid=72057594051559424 dbid=2 objectname=tempdb.dbo.t1 indexname=idx1 id=lock83642a00 mode=S associatedObjectId=72057594051559424
owner id=processdceda8 mode=S
waiter id=process3c54c58 mode=X requestType=wait
keylock hobtid=72057594051493888 dbid=2 objectname=tempdb.dbo.t1 indexname=cidx id=lock83643780 mode=X associatedObjectId=72057594051493888
owner id=process3c54c58 mode=X
waiter id=processdceda8 mode=S requestType=wait
pagelock fileid=1 pageid=95516 dbid=9 objectname="" id=lock177a9e280 mode=IX associatedObjectId=72057596554838016
These are all general recommendations that you can apply to any deadlock without having to really roll up your sleeves and get dirty. If after doing all of this you haven’t resolved it, though, you’ll have to dive a bit deeper and tailor a solution to the specifics of the scenario. Here’s a menu of some common techniques that you can choose from when deciding how best to tackle a deadlock:
1. Begin Transaction
2. Update Part table
2. Update Supplier table
3. Update Supplier table
3. Update Part table
4. Commit Transaction
These two batches may deadlock frequently. If both are about to execute step 3, they may each end up blocked by the other because they both need access to a resource that the other connection locked in step 2.
In a follow-up post I’ll look at a fairly typical deadlock in detail. This will provide an example of what you'd have to do if the 8 high-level steps listed above fail you, forcing you to understand the scenario at a deeper level so that you can craft a custom solution.
(This post series is continued in Deadlock Troubleshooting, Part 2.)
In the decoding process, you idetify one as conflicting Update lock, another one you just call Update lock. What's the difference between a conflicting lock and a ordinary lock? Does the trace flag 1222 deadlock graph provide the detail so we would know which one is a conflicting lock and which is not? Does it makes a difference when a lock is conflicting or not?
Thanks a lot,
By "conflicting" I simply meant that that the existing lock was incompatible with the new lock request. "Conflicting lock" == blocking lock. That's just my word choice, not a technical term. If two lock requests are compatible (e.g. two shared locks), they will both be granted. If the two requests conflict, one will be blocked and the other granted.
The 1222 output does identify which lock requests have been granted (<owner-list>) and which are blocked (<waiter-list>).
Thanks so much for the info... It helped me tremendously especially when I was trying to repeat the problem. FYI: my problem was related with indexes. We have a legacy app with stored procedures updating the same row for different purposes. And one of the columns being updated has also a non-clustered index. I open 2 query analyser and put while 1=1 to run 2 SPs and then Boom, I get the error in either fex seconds or 20 seconds. the only time I don't get an error iswhenI remove all the indexes which is not an option. Btw, this table doesn't have a primary key... I know I know.... It wasn't me who created and that person is no longer with the company... :) But I have to fix it.
Check the Companion tool at www.sqlminds.com It will do for you all of the above steps and more. For example, if you have 3 or 4 SIDs deadlocking and each SIDs has multiple statements per DB transaction, the above approach will fail since it will report ONLY the statements, which deadlocked:
SID1 - begin tran update t1 ... where PK = 1
SID2 - begin tran update t1 ... where PK = 2
SID3 - begin tran update t1 ... where PK = 3
SID1 - select * from t1 where PK = 3
SID2 - select * from t1 where PK = 1
SID3 - select * from t1 where PK = 2
This is where the deadlock monitor (spid=4) will kick in and guess what, you'll be getting the last three statements in the output
SID3 - select * from t1 where PK = 2
I don't think you can figure out the deadlock given only these three statements. You can do some tedious digging into the outstanding locks and figure out the deadlock but this can be done with the assumption that you know intimately your statements (i.e. what if you are an ASP - App service provider...). Check out the tool I've mentioned; it will give you the blocking chain PLUS the timing. HTH
The tool you describe sounds pretty cool.
FWIW, step 8 in the instructions above mentions that a profiler trace may be necessary if one or more of the deadlock participants are involved in a multi-batch transaction.
is it possible to cause a deadlock by 2 "select" staments?
tnks a lot for any help !
Possibly. One such case would be if the SELECT statements used a hint to change the type of locks being acquired (e.g. UPDLOCK, XLOCK). You could also see this if the SELECT statements were part of a multi-statement transaction. For example, these two transactions could deadlock on the SELECT statements:
Connection 1: begin tran update t1 set ... where c1 = x select * from t1 where c1 = y
Connection 2: begin tran update t1 set ... where c1 = y select * from t1 where c1 = x
Troubleshoot these just as you would any other deadlock.
In the text above, on point #2 you have highlighted the TEXT with YELLOW back-ground, which is really cool.
Will this feature (text with Yellow back-ground) be available out-of-box, if it can be, then it will be really cool
No, the SQL errorlog is just a plain text file; the yellow text highlighting is my emphasis. I did it to call out some of the data points in the 1222 output that can be the most useful when trying to understand a deadlock. You'll have to locate these data points in your own -T1222 output yourself.
Hi Bart thanks for your post, very helpful. I have read through your Decoding_T1204_Output.htm and have a small question.
Spid 52 is running a DELETE statement on line 6 of the stored proc spClearItemStatus. He holds an X lock on the key resource KEY: 7:2121058592:2 (a70064fb1eac). This lock is blocking spid 52, who is waiting to acquire a U lock on the same key.
Q: Should it say: This lock is blocking spid 51, instead?
Spid 51 is running an UPDATE statement on line 47 of the stored proc spUpdateItemProp. He holds an X lock on key KEY: 7:1977058079:1 (02014f0bec4e). His X lock is blocking spid 51, who is waiting to acquire an X lock on the same key.
Q: Should it say: This lock is blocking spid 52, instead?
You're absolutely right -- those were errors. I've fixed them in HTM file attached to the post.
Which profiler event(s) should I capture? I use Deadlock Graph and Blocked Process Report, are there others?
If one or both spids is running a multi-statement transaction, you may need to capture a profiler trace that spans the deadlock in order to identify the full set of queries that were involved in the deadlock. Unfortunately, both -T1204 and -T1222 only print out the two queries that “closed the loop”, and it’s possible that one of the blocking locks was acquired by an earlier query run within the same transaction.
Which profiler events capture such scenario?