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.)
Capture RPC:Starting/Completed, SQL:BatchStarting/Completed, and SQLTransaction at minimum. If possible without introducing undue load on the server, add SQL:StmtStarting, RPC:StmtStarting, and a pre-execution showplan event like Showplan All/Showplan XML. -T1222 will tell you what resources (including table names) were involved in the deadlock. Those profiler events will tell you what tables were modified within the participating spids' transactions. Taken together, trace flag 1222 and the profiler trace should allow you to figure out which queries acquired the locks that were involved in the deadlock.
Can you give me an example of more about the statement below and why Deadlock Graph\-T1222 can not accomplish? I guess i stll dont understand..
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.
Consider the simple deadlock scenario described at the beginning of http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx. In a case like this, only query #3 would be shown in the -T1222 (or -T1204) output. You would need a profiler trace if you wanted to see query #2 for each transaction, which played an important role in the deadlock.
Is there a reason why adding the "Lock:Deadlock graph" event class will not generate the event when the Lock:Deadlock event is recorded?
In addition, the -T1222 output is indicating two different resources for a RID lock (names have been changed to protect the innocent :O). Why would it deadlock on statements in two different tables?
ridlock fileid=1 pageid=1711 dbid=5 objectname=db.dbo.t1 id=lock39ebac0 mode=X associatedObjectId=72057594039959552
ridlock fileid=1 pageid=2097 dbid=5 objectname=db.dbo.t2 id=lock39eaec0 mode=X associatedObjectId=72057594040811520
> “Is there a reason why adding the "Lock:Deadlock graph" event class will not generate the event when the Lock:Deadlock event is recorded?”
Sorry, could you clarify this question? Capturing the "Lock:Deadlock Graph” event in a profiler trace will provide the same information that you can get in the errorlog via -T1222. To see the raw XML instead of the graphical view of the deadlock that the profiler GUI shows, right-click on the event and select “Extract event data”. (The graphical view is pretty and easier to understand, but it is also only provides a subset of the information that is available in the raw XML.)
> “In addition, the -T1222 output is indicating two different resources for a RID lock (names have been changed to protect the innocent :O). Why would it deadlock on statements in two different tables?”
Every deadlock must involve a minimum of two *different* lock resources. Each resource is owned by one of the deadlock participants. Each deadlock participant is blocked, waiting to get access to the resource currently locked by the other participant. That’s the way deadlocks work; if only one lock resource was involved, it could only be a simple blocking incident, not a deadlock.
PingBack from http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
Great article, best I've read on 2005 deadlocking thus far. I've always used the -T1204 and -T3605 in 2000. I was getting ready to add these to our new 2005 servers when I thought I should look for an update ... low and behold there is.
I've added the -T1222 via config manager, restarted, and created a deadlock (http://wardyit.com/blog/blog/archive/2005/12/12/65.aspx) yet I see no messages in my eventviewer nor displayed on screen ... just the deadlock error itself.
Did you enable the trace flag via DBCC TRACEON, or as a server startup parameter? If you enabled it as a server startup param, did you bounce the SQL service? If you enabled it via DBCC TRACEON, be sure that you set it as a global trace flag (note the "-1" in "DBCC TRACEON (1222, -1)"). The trace flag infrastructure is implemented completely differently in SQL 2005, so there are certain trace flags that now must be enabled globally when a simple "DBCC TRACEON (flag)" would suffice in SQL 2000. That might be worth a blog post someday...
If you're pretty sure that this is what you did, run "DBCC TRACESTATUS (-1)". If the trace flag is set correctly, you'll see 1222 in the list of enabled trace flags, and the "Global" column will be set to 1. If 1222 shows up in the list but the Global column is set to 0, you set the trace flag at the session level (meaning only for your spid -- it won't apply to the deadlock monitor thread, which is the only spid that can spew -T1222 to the errorlog).
If the trace flag is definitely set globally, recreate your deadlock, wait for it to be detected and resolved, then run "sp_readerrorlog". You should see the -T1222 output near the end of the log (search for the text "deadlock").
Hope this helps,
Thanks for the fast reply Bart.
Yeah I added it via the gui (Config Manager) -T1222; at the end of the startup parameters. Restarted SQL afterwards and still not seeing any information in the error log.
Running DBCC TRACESTATUS (-1) returns nothing but completed successfully.
I don't like the new config manager ... what here is wrong?
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1222
Try bypassing the config manager GUI and peeking in the registry:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters
You should have a "SQLArg3" value there with the data "-T1222". If it seems to be in order, is there any chance that you copied and pasted the text "-T1222" when you added the param originally? Word and some other editors have an annoying habit of replacing a normal ASCII dash "-" character with a long dash. Visually, the long dash is very difficult to differentiate from the ASCII dash, but it is sufficient to make SQL unable to understand the command line param.
Just a guess... If this is a possibile explanation, edit the value in regedit, re-type the string, then bounce SQL so it will re-read that reg key. In the short-term you can enable the trace flag temporarily by running "DBCC TRACEON (1222, -1)", just don't forget the -1. But it should work to add the trace flag as a SqlArg -- SQL wouldn't be able to start at all if it couldn't read the params listed in that reg key, as it is the only way the server can find out where the master database lives.
Just wanted to follow up and say I got it working ... I had an extra space before the -T1222 flag!
Anyways, thanks for the help and this useful information.
I'll really appreciate if you can interpret following deadlock graph on SQL Server 2000
2007-06-04 16:28:32.21 spid4 Node:1
2007-06-04 16:28:32.21 spid4 KEY: 8:1358627883:2 (9e045f9bca23) CleanCnt:2 Mode: Range-S-S Flags: 0x0
2007-06-04 16:28:32.21 spid4 Grant List 0::
2007-06-04 16:28:32.22 spid4 Owner:0x42cd4160 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:139 ECID:0
2007-06-04 16:28:32.24 spid4 SPID: 139 ECID: 0 Statement Type: INSERT Line #: 1
2007-06-04 16:28:32.24 spid4 Input Buf: RPC Event: sp_execute;1
2007-06-04 16:28:32.24 spid4 Grant List 1::
2007-06-04 16:28:32.24 spid4 Requested By:
2007-06-04 16:28:32.26 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:138 ECID:0 Ec:(0x44843508) Value:0x42cdce40 Cost:(0/24D0)
2007-06-04 16:28:32.29 spid4
2007-06-04 16:28:32.35 spid4 Node:2
2007-06-04 16:28:32.35 spid4 KEY: 8:1358627883:2 (9e045f9bca23) CleanCnt:2 Mode: Range-S-S Flags: 0x0
2007-06-04 16:28:32.37 spid4 Grant List 0::
2007-06-04 16:28:32.37 spid4 Grant List 1::
2007-06-04 16:28:32.37 spid4 Owner:0x42cddc60 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:138 ECID:0
2007-06-04 16:28:32.38 spid4 SPID: 138 ECID: 0 Statement Type: INSERT Line #: 1
2007-06-04 16:28:32.43 spid4 Input Buf: RPC Event: sp_execute;1
2007-06-04 16:28:32.43 spid4 Requested By:
2007-06-04 16:28:32.57 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:139 ECID:0 Ec:(0x4469F508) Value:0x42cd41e0 Cost:(0/166C)
2007-06-04 16:28:32.68 spid4 Victim Resource Owner:
2007-06-04 16:28:32.68 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:139 ECID:0 Ec:(0x4469F508) Value:0x42cd41e0 Cost:(0/166C)
2007-06-04 16:28:42.80 spid4
Wondering if someone can explain the following:
Create a table with no indexes, an Identity column (int), two varchar(50) columns and one smallint column.
The last three columns all nullable.
Enter two rows with values. Now in two Query windows execute the following:
1) BEGIN TRAN
SELECT * FROM TestRowID WITH (UPDLOCK)
WHERE ID = 1
2) BEGIN TRAN
WHERE ID = 2
The statement in the second is blocked by the first statement....
If we look at the activity monitor in SQL 2005 we for :
1) DATABASE 0 S Lock Granted
OBJECT 2073058421 IX Lock Granted
PAGE 72057594038321152 IU Lock Granted 1:154
RID 72057594038321152 U Lock Granted 1:154:0
2) DATABASE 0 S Lock Granted
RID 72057594038321152 U Lock Wait 1:154:0
Object 2073058421 is the table in question.
Now the question is, why is the second window trying to take out an U lock on RID 0?
It should lock another row, right?
Even when we introduce an non-clustered index on the ID Column, the same issue still occurs.
The database is SQL Server 2005 SP 2, but the same is observed on SQL 2000. However, introducing an
index on SQL 2000 does remove the blocking behaviour....
Bas, because there is no index on the table, SQL must visit and evaluate every row to see if it needs to be updated. Because the row might be updated, SQL must acquire an Update lock on each row as part of this evaluation (to prevent a common type of deadlock).
Your first transaction acquires and holds a lock on a row that is incompatible with the Update lock that the second transaction will acquire.
Creating an index might avoid the blocking if SQL chooses to use it. However, SQL may choose to use a table scan if (as in your example) the number of rows in the table is very small (or the percentage of the table that the QO estimates it will need to be update is large). If SQL chooses to scan the table despite the presence of an index, you will still see the same locking behavior. The fundamental rules of governing this decision haven't changed in SQL 2005, but there are subtle changes in the costing of possible plans in different versions of SQL that could cause a scan to be costed as slightly cheaper on one version and a seek to be costed as a bit cheaper on a different version.
What are your thoughts on using SET CONTEXT_INFO (or sp_bindsession) ? I have an ETL process that has frequent deadlocks, even though the data is strictly partitioned - doesn't overlap. Don't want index maint on loading/transforming, and when disabled page locks, the process slowed down tremendously.