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.)
@free, if the app has changed, then the query or schema changes are probably responsible for your deadlock. If the app has not changed, then it is probably a query plan that changed in between SQL 2005 and SQL 2008 SP1. Either way, the deadlock troubleshooting process is the same as normal (I'd start with the steps given in this post.)
Could you please let me know what is "Exchange Event" in the deadlock graph?
@Baburaj, this is discussed in some detail @ blogs.msdn.com/.../today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx
awesome post!. Just a question:
we have two processes that lock in X mode a single resource.
process id=process4677708 taskpriority=0 logused=1316 waitresource=KEY: 11:72057594056802304 (6400212899bf) waittime=4335 ownerId=601589 transactionname=user_transaction lasttranstarted=2011-01-12T11:33:20.830 XDES=0x808a1970 lockMode=S schedulerid=5 kpid=3636 status=suspended spid=115 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2011-01-12T11:33:23.073 lastbatchcompleted=2011-01-12T11:33:22.707 clientapp=.Net SqlClient Data Provider hostname=005-DEV-WEBSERV hostpid=2952 loginname=techno isolationlevel=read committed (2) xactid=601589 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
frame procname=adhoc line=1 stmtstart=64 sqlhandle=0x0200000034a66538fa79a0cd92f63b160cc31348523074a2
SELECT * FROM Domains WHERE Name=@Name AND DateDeleted is null AND IDUser=@IDUser
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
(@Name nvarchar(11),@IDUser int)SELECT * FROM Domains WHERE Name=@Name AND DateDeleted is null AND IDUser=@IDUser
process id=process868bc8 taskpriority=0 logused=1316 waitresource=KEY: 11:72057594056802304 (6500cf872cad) waittime=4382 ownerId=601625 transactionname=user_transaction lasttranstarted=2011-01-12T11:33:20.993 XDES=0x86b7d3d0 lockMode=S schedulerid=2 kpid=2900 status=suspended spid=99 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2011-01-12T11:33:23.027 lastbatchcompleted=2011-01-12T11:33:22.707 clientapp=.Net SqlClient Data Provider hostname=005-DEV-WEBSERV hostpid=2952 loginname=techno isolationlevel=read committed (2) xactid=601625 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
keylock hobtid=72057594056802304 dbid=11 objectname=NicSystem.dbo.Domains indexname=_dta_index_Domains_11_1195151303__K18_K4_K1_2_3_5_6_7_8_9_10_11_12_13_14_15_16_17 id=lock918bd580 mode=X associatedObjectId=72057594056802304
owner id=process868bc8 mode=X
waiter id=process4677708 mode=S requestType=wait
keylock hobtid=72057594056802304 dbid=11 objectname=NicSystem.dbo.Domains indexname=_dta_index_Domains_11_1195151303__K18_K4_K1_2_3_5_6_7_8_9_10_11_12_13_14_15_16_17 id=lock997a9800 mode=X associatedObjectId=72057594056802304
owner id=process4677708 mode=X
waiter id=process868bc8 mode=S requestType=wait
I'm trying to get some help to understand wat's going on. Reading your post I've created the indexes suggested by the Database Tuning Advisor and now the old pagelock on table Domains is a keylock. How it's possible that a single resource is locked in X-mode from two different processes?
> we have two processes that lock in X mode a single resource
No you don't, but I understand why you could conclude that from this -T1222 output. It's impossible for different sessions to both hold an X lock on the same resource unless they are enlisted in the same transaction.
Your decoded -T1222 output should look something like this:
Spid 115 is running this query: SELECT * FROM Domains WHERE Name=@Name AND DateDeleted is null AND IDUser=@IDUser
Spid 99 is running this query (the same query as 115): SELECT * FROM Domains WHERE Name=@Name AND DateDeleted is null AND IDUser=@IDUser
Spid 115 is waiting for a Shared KEY lock on the index on table NicSystem.dbo.Domains named "_dta_index_Domains_11_1195151303__K18_K4_K1_2_3_5_6_7_8_9_10_11_12_13_14_15_16_17" . (Spid 99 holds a conflicting X lock.)
Spid 99 is waiting for a Shared lock on a DIFFERENT key lock on the same index. (Spid 115 holds a conflicting X lock.)
The two keylock resources happen to be in the same index, but you can tell that the specific keys are different because the resources have different "id" values. The lock resource id for a key lock is a hash of the index key values -- different id's mean different key values.
I don't know anything about the previous queries run by these spids or about the query plans being used, but the transaction name ("user_transaction") implies that both of these spids are in the middle of a multi-statement explicit transaction. In other words, the app ran a "BEGIN TRAN" and hasn't yet run a "COMMIT TRAN". My guess is that the sequence of events is something like this:
1. Both spids open a transaction, most likely via a simple BEGIN TRAN.
2. Both spids run some other INSERT/UPDATE/DELETE query that modifies data in table NicSystem.dbo.Domains. The data modification query must acquire an X lock on this index. (The text of that prior query is not available in the -T1222 output. If you can't determine what it is by examining the code, you'll need to capture a profiler trace that includes the queries that led up to the deadlock. One of the queries in between the BEGIN TRAN and the deadlock is modifying the Domains table.)
3. Both spids run the SELECT statement mentioned above. They both are still in middle of their multi-statement transactions, so both of the X key locks are still being held. Unfortunately, each spid's SELECT statement tries to read one of the key values that was deleted by other spid's INS/UPD/DEL query in step #2. Each spid ends up blocking the other.
You can use the steps in this blog post and in Part 3 of this blog post series to try to eliminate the deadlock. For example:
- I think the SNAPSHOT or READ COMMITTED SNAPSHOT transaction isolation levels would prevent this, if the semantics of one of those isolation level are OK for your application.
- You can examine the app code to see if you can reduce the duration of this transaction.
- You can look at the indexes that support the INSERT/UPDATE/DELETE and those that support the SELECT in order to ensure that both queries are locking the minimum number of resources.
- If the data modification query is an UPDATE, you may be able to prevent the problem by removing the updated columns from the index that the SELECT statement is using (or by using a hint to force a different index that doesn't include any of the updated columns).
- If all else fails, you can deliberately force blocking earlier in the transaction by moving a copy of the SELECT to a point in the transaction before the INSERT/UPDATE/DELETE and using the HOLDLOCK and UPDLOCK hints (see the example at the bottom of Part 3 in the series).
- ...? (not an exhaustive list...)
Thanks for the article.
Just one question. When you have to staments located in two different stored procedure that cuases a deadlock, will you be able to determine which SPs are involved from the T1222 data?
@Vannix78: Yes; check out the sample -T1222 output in this very post. There's a "procname" attribute that will provide the stored procedure name. And if there is a proc that calls another proc (not demonstrated in this example), you get the entire T-SQL stack.
Thanks a great deal for this post it has really helped me with my course work that seemed so hard..you are a life saver!!!!!
We are on SQL Server 2000. We would like to know is there a way of capturing what caused the deadlock victim error after the event? We have a SQL Job that runs on daily basis, with basic updating/deleting/inserting SQL scripts to and from tables. However just on month end this job fails with the error 'Transaction (Process ID 63) was deadlocked on lock communication buffer resources with another process and has been chosen as the deadlock victim...'
Is there a trace scheduler we can switch on/off at specific date and time just to record which processes are causing the issue on month end?
Take another look at the process described in this post. Trace flag 1222 isn't available on SQL 2000, but trace flag 1204 is available. Otherwise the process is exactly as described in this post. (And at the end of step #3 you'll find a sample -T1204 output annotated to help you understand the 1204 output format.)
Bart - great article, don't know how I missed it until now. I also appreciate that you still respond to comments after all these years.
For the sake of completness, I see one technique missing which I have found very useful as quick and dirty method to solve complicated deadlocks, using try/catch block and waiting and retrying if there is a deadlock.
DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
INSERT Authors VALUES
(@au_id, @au_lname, '', '', '', '', '',
WAITFOR DELAY '00:00:05'
SELECT * FROM authors WHERE au_lname LIKE 'Test%'
SELECT ERROR_NUMBER() AS ErrorNumber
SET @Tries = @Tries + 1
Thanks again for a great article which I am sure has helped a lot of folks.
Thanks Bob! Retry should definitely be mentioned. As you noted, you can even retry in T-SQL, as long as your transaction is wholly-contained within a single stored proc or batch. If the current transaction spans batches, the retry logic would need to be moved out to the client app code.
I'll see about updating the suggested solutions to add this.
Great Article!!! It helps me a lot..
Is there a way to find transaction isolation level for the SQL statements?
hi bartduncan, this deadlock often occurs in our system. these two processes hold x lock on different indexes and on different tables and still end up on a deadlock. can you help me identify as to why it happens.
thanks in advance for your help.
<process id="processbaf978" taskpriority="0" logused="4328" waitresource="KEY: 6:72057594516013056 (8a012d451225)" waittime="4296" ownerId="1406974893" transactionname="user_transaction" lasttranstarted="2012-07-26T16:15:52.387" XDES="0x1d222daa0" lockMode="S" schedulerid="1" kpid="12388" status="suspended" spid="199" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2012-07-26T16:17:49.917" lastbatchcompleted="2012-07-26T16:17:48.210" clientapp="JSQL" hostpid="4294505091" loginname="sa" isolationlevel="read committed (2)" xactid="1406974893" currentdb="6" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<frame procname="psi_db.dbo.Add_EmployeeDetails" line="126" stmtstart="9038" stmtend="10182" sqlhandle="0x03000600836cb0274be14001949c00000100000000000000">
if exists ( Select 1 from
( select Emp_id from master_Employee
where EmployeeNumber = @empno and
Employee_type = @emptype and
isnull(Expiration_date, getdate() - 1) >= getdate()) ME
Inner Join employeesubdetail ESD on
ME.Emp_id = ESD.Emp_id ) </frame>
<frame procname="adhoc" line="4" stmtstart="136" stmtend="814" sqlhandle="0x010006002c504416b09e575d040000000000000000000000">
exec Add_EmployeeDetails @P0 OUTPUT,1272737,2452,N'9468',N'<SV_ARRAY></SV_ARRAY>',0,N'',454,0,0,N'',N'',5061608,N'',N'REG02',4193196,N'<SV_ARRAY><ITEM VALUE1="9468" VALUE2="1"></ITEM></SV_ARRAY>',0,0,NULL,@P20 OUTPUT,N'<SV_ARRAY></SV_ARRAY>',N'07/26/2013',N'','20130725 16:00:52.105',N'<SV_ARRAY></SV_ARRAY>',@P26 OUTPUT,N'',0 </frame>
DECLARE @P0 INTEGER
DECLARE @P20 VARCHAR(8000)
DECLARE @P26 INTEGER
exec Add_EmployeeDetails @P0 OUTPUT,1272737,2452,N'9468',N'<SV_ARRAY></SV_ARRAY>',0,N'',454,0,0,N'',N'',5061608,N'',N'REG02',4193196,N'<SV_ARRAY><ITEM VALUE1="9468" VALUE2="1"></ITEM></SV_ARRAY>',0,0,NULL,@P20 OUTPUT,N'<SV_ARRAY></SV_ARRAY>',N'07/26/2013',N'','20130725 16:00:52.105',N'<SV_ARRAY></SV_ARRAY>',@P26 OUTPUT,N'',0
SELECT @P0 ,@P20 ,@P26 </inputbuf>
<process id="process50c0b08" taskpriority="0" logused="600" waitresource="KEY: 6:72057594508935168 (9a01a0a71080)" waittime="7921" ownerId="1406983583" transactionname="user_transaction" lasttranstarted="2012-07-26T16:16:08.337" XDES="0x2dfc19830" lockMode="S" schedulerid="4" kpid="8868" status="suspended" spid="178" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2012-07-26T16:16:08.687" lastbatchcompleted="2012-07-26T16:16:08.360" clientapp="JSQL" hostpid="4294505091" loginname="sa" isolationlevel="read committed (2)" xactid="1406983583" currentdb="6" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128024">
<frame procname="psi_db.dbo.Add_EmployeeToDep" line="66" stmtstart="3108" stmtend="3410" sqlhandle="0x030006008a00ec2a3ecf7801e59a00000100000000000000">
if not exists (Select EmployeeNumber
Where EmployeeNumber = @empno and
Employee_type = @emptype
<frame procname="adhoc" line="3" stmtstart="80" stmtend="2060" sqlhandle="0x010006002a379c0ea0554271040000000000000000000000">
exec Add_EmployeeToDep @P0 OUTPUT,N'Kelsey',N'New',N'544297757',N'SSN',N'<SV_ARRAY><ITEM VALUE1="37" VALUE2="97209"></ITEM><ITEM VALUE1="3" VALUE2="JANE"></ITEM></SV_ARRAY>',N'REG01',N'CDS01',6922,@P9 OUTPUT </frame>
DECLARE @P9 INTEGER
exec Add_EmployeeToDep @P0 OUTPUT,N'Kelsey',N'New',N'544297757',N'SSN',N'<SV_ARRAY><ITEM VALUE1="37" VALUE2="97209"></ITEM><ITEM VALUE1="3" VALUE2="JANE"></ITEM></SV_ARRAY>',N'REG01',N'CDS01',6922,@P9 O </inputbuf>
<keylock hobtid="72057594508935168" dbid="6" objectname="psi_db.dbo.Emp_Status" indexname="IX_Emp_Status1" id="lock52876b580" mode="X" associatedObjectId="72057594508935168">
<owner id="processbaf978" mode="X" />
<waiter id="process50c0b08" mode="S" requestType="wait" />
<keylock hobtid="72057594516013056" dbid="6" objectname="psi_db.dbo.master_Employee" indexname="IX_Master_Emp_ID" id="lock52ba26100" mode="X" associatedObjectId="72057594516013056">
<owner id="process50c0b08" mode="X" />
<waiter id="processbaf978" mode="S" requestType="wait" />