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.)
FrankG, you may have meant it tongue-in-cheek :), but yes, I think your best bet may be to contact MS support for assistance with your deadlock involving MS repl-created tables. The only option available to you without modifying system procs or system tables would be to force a different plan with a plan guide, and that approach to a solution may not "stick" across service packs or QFEs if the change updates the merge trigger or the MSMerge stored proc involved in the deadlock.
I have already described several deadlock scenarios that involve only one table in another post. This
This is a really great article.
I wish that I had access to something like this resource 5 years ago when I had to solve some spectacular deadlocking issues on a SQL Server 2000 app.
I especially like the index tuning advisor hint - that is sooooo true.
Can 2 processes acquire rowlock on same row?
I'm facing a deadlock where 2 processes have acquired row lock on same row and waiting for eachother. Here is the deadlock graph.
2009-07-28 14:13:29.50 spid18s ridlock fileid=1 pageid=10089 dbid=5 objectname=dcmdb.dcmdbuser.fs_payaccount id=lock1254ce80 mode=X associatedObjectId=72057595031977984
2009-07-28 14:13:29.50 spid18s owner-list
2009-07-28 14:13:29.50 spid18s owner id=process93af28 mode=X
2009-07-28 14:13:29.50 spid18s waiter-list
2009-07-28 14:13:29.50 spid18s waiter id=process93a988 mode=U requestType=wait
2009-07-28 14:13:29.50 spid18s ridlock fileid=1 pageid=10089 dbid=5 objectname=dcmdb.dcmdbuser.fs_payaccount id=lock1257ba00 mode=X associatedObjectId=72057595031977984
2009-07-28 14:13:29.50 spid18s owner id=process93a988 mode=X
2009-07-28 14:13:29.50 spid18s waiter id=process93af28 mode=U requestType=wait
As you can see above the associatedObjectId is same for both ridlocks.
When can this happen? From the logic of my program 2 threads will never update the same row in fs_payaccount table.
"associatedObjectId" isn't a row identifier; it's the heap or B-tree identifier (HoBT ID -- see http://msdn.microsoft.com/en-us/library/ms178104.aspx for documentation of the 1211 output fields that aren't doc'ed in this post). In other words, it just identifies the table or index. You can look up the object name associated with a HoBT ID using "SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = xxx", but it would just tell you that the row lock is on table "dcmdb.dcmdbuser.fs_payaccount", which you can already tell from the rest of the 1211 output.
In other words, there are two different rows (from the same table) involved in this deadlock. There's not enough information here to be sure, but my guess would be that one or both of the queries involved are scanning this table to locate the rows to update. This would acquire Update locks on all rows.
Thank you very much.Great article and it saved my day....
Great article! Fixed my deadlock problems by creating a new index.
The DTA tool does seem a little index happy though.
What is the downside of adding too many indexes?
I am new to this deadlock theory. Just gone through your article and it is explained very nice way.
Actually I am facing a deadlock problem while I transfer database from SQL Server 2005 Express Edition to SQL Server 2008 Express Edition with SP1 (both have different instances and I am transferring database from 2005 instance to 2008 instance).
Actually I have posted this problem on msdn site. I would appreciate if you can spare some minutes and take a look at the below given link. I have explained whole problem in that thread.
The most common downside of too many indexes is incrementally more expensive updates, inserts, and deletes. For most scenarios this cost won't be noticible, though. If an index is effective in eliminating scans and preventing a deadlock, I'd only pause before adding it in one of these two scenarios:
- the workload is OLTP-like, and the table has relatively high sustained modification rates (say, >50 transactions/sec that modify the table)
- the table is frequently reindexed, or large amounts of data are regularly inserted into the data (e.g. in a warehouse with a regular data feed from other systems), and the insert/reindex job already threatens to exceed a reasonable execution time.
Thanks for the good article.hope will see more article from you
You should shown great insight into Deadlocks...I have learned tremendously from your 3 articles...
Please keep it up!
Thank you so much for this article, great info and very (very!) helpful
This is a great blog... Thanks !!
As a beginner DBA, how can we conclude at the first instance that a deadlock has occured in the server ?
(When neither the trace flags are turned on nor the profiler is set..)
What are the symptoms of a deadlock..?
@Pastille, if you don't have either trace flag enabled and you're not running profiler, then the only symptom of most deadlocks is an error message returned to the application. (See the first paragraph of this blog post.)