Deadlock Troubleshooting, Part 1


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:

 

  1. Turn on trace flag 1222 with “DBCC TRACEON (1222, -1)” or by adding “-T1222” as a SQL startup parameter.  This trace flag is a new trace flag in SQL 2005, a much improved version of the tried-and-true -T1204.  If you’re running SQL 2005, you should be using 1222 instead of 1204 unless you have deep-seated masochistic tendencies. Alternatives to 1222:
    • If you are using SQL 2000 or SQL 7.0, you’ll have no choice but to fall back on the older -T1204. 
    • There’s a “Deadlock graph” Profiler trace event that provides the same info as -T1222.  Feel free to use this instead of -T1222 if you’re on SQL 2005.  But don’t waste your time with the “Lock:Deadlock” and “Lock:Deadlock Chain” trace events that are in SQL 2000, as they provide an unacceptably incomplete picture of the deadlock. 
  2. Get the -T1222 output from the SQL errorlog after the deadlock has occurred.  You’ll see output that looks like this:

deadlock-list

 deadlock victim=processdceda8

  process-list

   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

    executionStack

     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    

    inputbuf

       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

    executionStack

     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    

    inputbuf

       EXEC p2 3

  resource-list

   keylock hobtid=72057594051559424 dbid=2 objectname=tempdb.dbo.t1 indexname=idx1 id=lock83642a00 mode=S associatedObjectId=72057594051559424

    owner-list

     owner id=processdceda8 mode=S

    waiter-list

     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-list

     owner id=process3c54c58 mode=X

    waiter-list

     waiter id=processdceda8 mode=S requestType=wait

 

  1. “Decode” the -T1222 output to better understand the deadlock scenario.  The deadlock is summarized by a “process-list” and a “resource-list”.  A “process” is a spid or worker thread that participates in the deadlock.  Each process is assigned an identifier, like “processdceda8”.  A resource is a resource that one of the participants owns (usually a lock) that the other participant is waiting on.  I like to use a format like the one below to summarize the deadlock.  You can skip this step if you want, but I never do; I find it really helps me understand the deadlock situation more clearly.  I’ve highlighted in yellow each of the data points within the 1222 output that you would need to reconstruct this summary on your own.

                Spid 54 is running this query (line 2 of proc [p1]):
                                    SELECT c2, c3 FROM t1 WHERE c2 = @p1
                    Spid 55 is running this query (line 2 of proc [p2]):
                                    UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1
                   
                    Spid 54 is waiting for a Shared KEY lock on index t1.cidx.  
                                    (Spid 55 holds a conflicting X lock.)
                    Spid 55 is waiting for an eXclusive KEY lock on index t1.idx1.  
                                    (
    Spid 54 holds a conflicting S lock.)

    For most lock types (including KEY locks, as shown in this example), SQL will directly identify the index by name in the output.  For some lock types, though, you’ll get an “associatedObjectId”, but no object name.  An example: 

          pagelock fileid=1 pageid=95516 dbid=9 objectname=”” id=lock177a9e280 mode=IX associatedObjectId=72057596554838016

    The attribute “associatedObjectId” isn’t the type of Object ID that you’re probably familiar with; it’s actually a partition ID.  You can determine the database name by running “SELECT DB_NAME(9)”, where the “9” in this example comes from the “dbid” attribute, highlighted in blue.  Then you can determine the index and table name by looking up the associatedObjectId/PartitionId in the indicated database: 

         SELECT OBJECT_NAME(i.object_id), i.name
         FROM sys.partitions AS p
         INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
         WHERE p.partition_id = 72057596554838016 

    For those of you on SQL 2005 who think that the -T1222 output is a bit overwhelming, you’re right.  But you may also want to count your blessings and be thankful that you don’t have to wade through -T1204 output, which is a lot more difficult to interpret than -T1222 and doesn’t provide nearly as much useful information about the deadlock.  Check out the file “Decoding_T1204_Output.htm” attached to this post for annotated -T1204 output.

  2. Run the queries involved in the deadlock through Database Tuning Advisor.  Plop the query in a Management Studio query window, change db context to the correct database, right-click the query text and select “Analyze Query in DTA”.  Don’t skip this step; more than half of the deadlock issues we see are resolved simply by adding an appropriate index so that one of the queries runs more quickly and with a smaller lock footprint.  If DTA recommends indexes (it’ll say “Estimated Improvement: <some non-zero>%”), create them and monitor to see if the deadlock persists.  You can select “Apply Recommendations” from the Action drop-down menu to create the index immediately, or save the CREATE INDEX commands as a script to create them during a maintenance window.  Be sure to tune each of the queries separately. 
  3. Make sure the query is using the minimum necessary transaction isolation level (-T1222 will tell you this – search the output for “isolationlevel”).  Queries run by transactional COM+ components will default to serializable, which is usually overkill.  This can be reduced by query hints (“…FROM tbl1 WITH (READCOMMITTED)…”), a SET TRANSACTION ISOLATION LEVEL command, or, in Windows 2003 and later, by configuring the object in the Component Services MMC plugin.
  4. Make sure that your transactions are as brief as they can be while still meeting the relevant business constraints.  Try not to use implicit transactions, as this model of transaction management encourages unnecessarily long transactions. 
  5. Look for other opportunities to improve the efficiency of the queries involved in the deadlock, either through query changes or through indexing improvements.  A query that locks the minimum number of resources will be much less likely to deadlock with another query.  Table scans, index scans, and large hashes or large sorts in the query plan may indicate opportunities for improvement.
  6. 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.

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:

 

  • Access objects in the same order.   Consider the following two batches:

1. Begin Transaction

1. Begin Transaction

2. Update Part table

2. Update Supplier table

3. Update Supplier table

3. Update Part table

4. Commit Transaction

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. 

  • If both deadlock participants are using the same index, consider adding an index that can provide an alternate access path to one of the spids.  For example, adding a covering nonclustered index for a SELECT involved in a deadlock may prevent the problem (assuming that none of the covering index keys are modified by the other deadlock participant).
  • On the other hand, if the spids are deadlocking because they took alternate paths (indexes) to a common required data row or page, consider whether one of the indexes can be removed or an index hint used to force both queries to share an access path.  Be cautious of potential performance hits as a result of this approach.
  • Deadlocks are a special type of blocking where two spids both end up blocking the other.  Sometimes the best way to prevent a deadlock is to force the blocking to occur at an earlier point in one of the two transactions.  For example, if you force spid A to be blocked by spid B at the very beginning of A’s transaction, it may not have a chance to acquire the lock resource that later ends up blocking spid B.  Doesn’t this means you are deliberately causing blocking?  Yes, but remember that you already have blocking or you wouldn’t be in a deadlock situation, and simple blocking is a big improvement over a deadlock.  As soon as B commits his transaction, A will be able to proceed.  HOLDLOCK and UPDLOCK hints can be useful for this.
  • If a high priority process is being selected as a victim in a deadlock with a lower priority process, the lower priority process could be modified to SET DEADLOCK_PRIORITY LOW.  Spids that set this will offer themselves up as the sacrificial lamb in any deadlock they encounter. 
  • Avoid placing clustered indexes on columns that are frequently updated. Updates to clustered index key columns will require locks on the clustered index (to move the row) and all nonclustered indexes (since the leaf level of NC indexes reference rows by clustered index key value). 
  • In some cases it may be appropriate to add a NOLOCK hint, assuming that one of the queries is a SELECT statement.  While this is a tempting path because it is a quick and easy solution for many deadlocks, approach it with caution as it carries with it all the usual caveats surrounding read uncommitted isolation level (a query could return a transactionally inconsistent view of the data).  If you are unfamiliar with the risks, read the “SET TRANSACTION ISOLATION LEVEL” topic in SQL Books Online. 
  • In SQL 2005 you could consider the new SNAPSHOT isolation level.  This will avoid most blocking while avoiding the risks of NOLOCK.  An even cooler new feature IMHO is the new READ COMMITTED SNAPSHOT database option (see ALTER DATABASE), which allows you to use a variant of snapshot isolation level without changing your app.  
  • If one or both locks involved in the deadlock are S/X TAB (table) locks, lock escalation may be involved.  You can reduce the likelihood of lock escalation by enabling trace flag 1224 (SQL 2005 and later) or 1211 (see KB 323630).  Note that this does not apply to “intent” TAB locks, which have a capital “I” prefix (e.g. IS / IX TAB locks).
  • If the deadlock is intermittent, sometimes the simplest solution is to add deadlock retry logic. The retry logic could be in T-SQL, as long as (a) you’re on SQL 2005 or later so that you can use BEGIN TRY, and (b) your transaction is wholly-contained within a single stored proc or batch. See this article for details. If the deadlock transaction spans multiple batches you can still add deadlock retry logic, but it would need to be moved out to the client app code. If you can only add deadlock retry logic to one of the participants in the deadlock, you can use SET DEADLOCK_PRIORITY LOW to ensure that the engine prefentially aborts the transaction of the guy that has the retry logic.

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.)

  

SQL2000_Deadlocks_T1204.htm

Comments (81)

  1. ice9 says:

    Great Article, Can you recommend me some tips to solve a deadlock problem?

    I have a Master Stored Procedure, which internally calls other procedures as needed. This master proc is invoked from a Web Application (ASPX). Sporadically I get error messages

    Error Detail: System.Data.SqlClient.SqlException: Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

      at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    1. How do detect where the problem is…means which section of the code is causing deadlock?

    2. How should I rectify them?

    ?Should I wrap the master proc with begin end tran..(since its a very high called proc I left it intially)

    ?Should I wrap my internal procs with tran blocks?

    TIA

  2. bartduncan says:
    1. How do detect where the problem is…means which section of the code is causing deadlock?

      This is discussed in steps 1-3 in the post.  To recap that info: If you’re on SQL 2005, turn on -T1222.  This will tell you the final 2 statements involved in the deadlock.  If you’re on SQL 2000, turn on -T1204 and -T3605 and capture a profiler trace that includes the SP:StmtStarting, Lock:Deadlock, and Exception events (at a minimum).  

      > 2. How should I rectify them?

      Once you identify the queries involved in the deadlock, follow steps 4-8 in the post.  

  3. rogerlin says:

    Hi Bart,

    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,

    Roger

  4. bartduncan says:

    Roger,

    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>).

    Bart

  5. Anonymous says:

    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.

  6. Anonymous says:

    Check the Companion tool at http://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

    then

    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

    SID1 – select * from t1 where PK = 3

    SID2 – select * from t1 where PK = 1

    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

  7. bartduncan says:

    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.  

  8. Anonymous says:

    is it possible to cause a deadlock by 2  "select" staments?

    tnks  a lot for any help !

  9. bartduncan says:

    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.

  10. Anonymous says:

    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

  11. bartduncan says:

    Prasanna,

    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.  

    Bart

  12. Anonymous says:

    Hi Bart thanks for your post, very helpful.  I have read through your Decoding_T1204_Output.htm and have a small question.  

    You:

    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?

    You:

    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?

    Regards,

    Dmitrey

  13. bartduncan says:

    Dmitrey,

    You’re absolutely right — those were errors.  I’ve fixed them in HTM file attached to the post.

    Thanks!

    Bart

  14. Anonymous says:

    Hi, Bart

    Which profiler event(s) should I capture? I use Deadlock Graph and Blocked Process Report, are there others?

    Thank you,

    Bill

    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.

  15. Anonymous says:

    Hi,

    Which profiler events capture such scenario?

    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.

    Bill

  16. bartduncan says:

    Bill,

    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.

    HTH,

    Bart

  17. Anonymous says:

    Hi, Bart

    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.

    Thank you,

    Bill

  18. bartduncan says:

    Bill,

    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.  

    HTH,

    Bart

  19. Anonymous says:

    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

    and

    ridlock fileid=1 pageid=2097 dbid=5 objectname=db.dbo.t2 id=lock39eaec0 mode=X associatedObjectId=72057594040811520

    Thanks,

    Anders

  20. bartduncan says:

    Anders:

    “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.  

  21. adam.bean says:

    Hello Bart,

    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.

    Thanks

  22. bartduncan says:

    Adam,

    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,

    Bart

  23. adam.bean says:

    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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf; -T1222

  24. bartduncan says:

    Try bypassing the config manager GUI and peeking in the registry:

       HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerParameters

    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.

    HTH,

    Bart

  25. adam.bean says:

    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.

  26. Anonymous says:

    Hi Bart,

    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    

    Thanks

    -Shagun

  27. Anonymous says:

    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

         SELECT * FROM TestRowID WITH (UPDLOCK)

         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

         OBJECT         2073058421               IX Lock   Granted

         PAGE             72057594038321152   IU Lock   Granted   1:154

         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….

  28. bartduncan says:

    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.  

  29. Anonymous says:

    Bart,

    Great post!  

    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.

    Thanks

    Mike

  30. bartduncan says:

    Mike,

    I don’t think CONTEXT_INFO will help you with your deadlocks.  In theory, sp_gettoken/sp_bindsession could prevent a deadlock, assuming that (a) it is appropriate for the two processes involved in the deadlock to share a single transaction, and (b) you are able to make some non-trivial changes to one of the apps to have it enlist in the other app’s transaction.  

    Unless your deadlock involves two connections that are doing different pieces of the same ETL transformation, I doubt (a) would apply to you.  

    I’m not clear on the details of your situation.  Are you trying to load a table that has no indexes while other processes concurrently access the table?  If so, this is a recipe for blocking.  Any read or modification to a table without indexes will require a table scan, which of course means locking every page or row in the table.  That all but guarantees that any query that runs at the same time as your ETL data load will be involved in some sort of blocking.  It also means that you are probably getting terrible query plans.

    If that isn’t your situation, please share some specifics.  

    Bart

  31. Anonymous says:

    You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  32. Anonymous says:

    Thanks Bart.

  33. adam.bean says:

    Bart,

    Do you have some time you’d be able to look over some deadlocks we’ve been having? I’ve been using your page as a bible and I’m still coming up a bit short … If you are able to give me a few minutes, I’d really appreciate it. My email is in my bio.

    Thanks!

  34. Anonymous says:

    Bart,

    The bindsession (context_info) method worked like a charm.  

    I had also tried disabling page locks, suspecting lock escalation, which did reduce the deadlocks, but not eliminate completely.  The processing time increased enormously.  Yuk.

    Is there any chance we could get you to visit and present at the PASS-MN (Minneapolis) group in the future?

    Thanks!

  35. Anonymous says:

    Firstly I must say a big thank you to Microsoft for the new deadlock trace flag 1222. Compared to the…

  36. Anonymous says:

    Firstly I must say a big thank you to Microsoft for the new deadlock trace flag 1222. Compared to the

  37. Anonymous says:

    Hi bart,

    i’ve been experiencing alot of deadlock with type ‘pagelock’ such this :

    update KMS_SERV_MAST_REC set ACTION_CODE= @P0 , ACTV_GRP_ID= @P1 , ACTV_ID= @P2 , STAFF_ID= @P3 , ICD_CODE= @P4 , ICD_CODE_2= @P5 , ICD_CODE_3= @P6 , ICD_CODE_4= @P7 , ICD_CODE_5= @P8 , CREATED_BY= @P9 , CREATED_DATE= @P10 , LAST_UPD_BY= @P11 , LAST_UPD_DATE= @P12 , actual_date= @P13 , dischrg_home_addr= @P14 , dischrg_summary_notes= @P15 , dischrg_summary_given= @P16 , dismissal_code= @P17 , amendment_type= @P18 , amendment_note= @P19  where MR_NO= @P20  and MR_DATE= @P21  and MR_TYPE= @P22  and MR_SEQ= @P23    

            inputbuf

        (@P0 nvarchar(4000),@P1 bigint,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 datetime,@P11 nvarchar(4000),@P12 datetime,@P13 datetime,@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 nvarchar(4000),@P21 datetime,@P22 nvarchar(4000),@P23 int)update KMS_SERV_MAST_REC set ACTION_CODE= @P0 , ACTV_GRP_ID= @P1 , ACTV_ID= @P2 , STAFF_ID= @P3 , ICD_CODE= @P4 , ICD_CODE_2= @P5 , ICD_CODE_3= @P6 , ICD_CODE_4= @P7 , ICD_CODE_5= @P8 , CREATED_BY= @P9 , CREATED_DATE= @P10 , LAST_UPD_BY= @P11 , LAST_UPD_DATE= @P12 , actual_date= @P13 , dischrg_home_addr= @P14 , dischrg_summary_notes= @P15 , dischrg_summary_given= @P16 , dismissal_code= @P17 , amendment_type= @P18 , amendment_note= @P19  where MR_NO= @P20  and MR_DATE= @P21  and MR_TYPE= @P22  and MR_SEQ= @P23    

           process id=processbc44d8 taskpriority=0 logused=0 waitresource=PAGE: 5:1:26310 waittime=687 ownerId=53109491 transactionname=implicit_transaction lasttranstarted=2008-07-03T11:07:22.770 XDES=0x3d7ebe8 lockMode=U schedulerid=4 kpid=4964 status=suspended spid=59 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-07-03T11:07:22.787 lastbatchcompleted=2008-07-03T11:07:22.787 clientapp=jTDS hostname=GRSMED1 hostpid=123 loginname=kmsappl isolationlevel=read committed (2) xactid=53109491 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058

            executionStack

             frame procname=adhoc line=1 stmtstart=834 sqlhandle=0x02000000531ade3a7674bac5a6157116168d0d92c2150219

        update KMS_SERV_MAST_REC set ACTION_CODE= @P0 , ACTV_GRP_ID= @P1 , ACTV_ID= @P2 , STAFF_ID= @P3 , ICD_CODE= @P4 , ICD_CODE_2= @P5 , ICD_CODE_3= @P6 , ICD_CODE_4= @P7 , ICD_CODE_5= @P8 , CREATED_BY= @P9 , CREATED_DATE= @P10 , LAST_UPD_BY= @P11 , LAST_UPD_DATE= @P12 , actual_date= @P13 , dischrg_home_addr= @P14 , dischrg_summary_notes= @P15 , dischrg_summary_given= @P16 , dismissal_code= @P17 , amendment_type= @P18 , amendment_note= @P19  where MR_NO= @P20  and MR_DATE= @P21  and MR_TYPE= @P22  and MR_SEQ= @P23    

            inputbuf

        (@P0 nvarchar(4000),@P1 bigint,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 datetime,@P11 nvarchar(4000),@P12 datetime,@P13 datetime,@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 nvarchar(4000),@P21 datetime,@P22 nvarchar(4000),@P23 int)update KMS_SERV_MAST_REC set ACTION_CODE= @P0 , ACTV_GRP_ID= @P1 , ACTV_ID= @P2 , STAFF_ID= @P3 , ICD_CODE= @P4 , ICD_CODE_2= @P5 , ICD_CODE_3= @P6 , ICD_CODE_4= @P7 , ICD_CODE_5= @P8 , CREATED_BY= @P9 , CREATED_DATE= @P10 , LAST_UPD_BY= @P11 , LAST_UPD_DATE= @P12 , actual_date= @P13 , dischrg_home_addr= @P14 , dischrg_summary_notes= @P15 , dischrg_summary_given= @P16 , dismissal_code= @P17 , amendment_type= @P18 , amendment_note= @P19  where MR_NO= @P20  and MR_DATE= @P21  and MR_TYPE= @P22  and MR_SEQ= @P23    

          resource-list

           pagelock fileid=1 pageid=26310 dbid=5 objectname=kms30prod002.kms.KMS_SERV_MAST_REC id=lock2be86740 mode=IX associatedObjectId=72057594063421440

            owner-list

             owner id=process88b1f8 mode=IX

            waiter-list

             waiter id=processbc44d8 mode=U requestType=wait

           pagelock fileid=1 pageid=17123 dbid=5 objectname=kms30prod002.kms.KMS_SERV_MAST_REC id=lock30e08b40 mode=U associatedObjectId=72057594063421440

            owner-list

             owner id=processbc44d8 mode=U

            waiter-list

             waiter id=process88b1f8 mode=U requestType=wait

    Do you have any idea whats going on ?

    I wonder if i used ROWLOCK hint on my update statement will resolved this deadlock ?

    Thanks in advance

  38. bartduncan says:

    Rony –

    Page locks are usually an indication of a table or index scan.  The best solution is probably to tune your UPDATE statement so that there is a more efficient access path to the qualifying rows.  Review the steps in this blog post; did you run the query through Database Engine Tuning Advisor?  It might have recommended a better index.  If DTA didn’t make any recommendations, consider an index on MR_NO, MR_DATE, MR_TYPE, and MR_SEQ.  

    HTH,

    Bart

  39. Anonymous says:

    Hi Bart,

    Thanks for your answer, i already run the query through database engine tuning advisor and yes DTA didnt make any recommendations, i forgot to tell you that MR_NO, MR_DATE, MR_TYPE and MR_SEQ are primary key, so all of these column is already indexed i think.

    Any other suggestions bart ?

    I’ll try to see whether i can change the UPDATE statement and see if there is a more efficient acces path to the qualifying rows

    Rony

  40. Anonymous says:

    I’ve received a couple of questions in email and in comments about deadlocks involving mysterious-sounding

  41. Anonymous says:

    Hi, I could no able trace dead lock information Profiler, even though there was dead lock issue happend from Application level. I turn on the trace flags 1222, 1204 using DBCC in SQL Query Analyser.

    Please suggest how to trace dead lock information?

  42. bartduncan says:

    Satish –

    1. What is the exact text of the error message you received in the application?
    2. What is the exact text of the DBCC command you ran to turn on the trace flags?

    3. Were you expecting to find the trace flag output in Profiler?  If so, re-read this article.  

    4. Were the trace flags turned on at the time of the deadlock error?  They must be on before the deadlock occurs.

    5. Did you restart the SQL Server service?  A trace flag enabled via DBCC TRACEON will be turned off when you restart the service.  

    6. Try running "DBCC TRACESTATUS (-1)" to see what trace flags are currently enabled.

    HTH –

    Bart

  43. Anonymous says:

    Although the best known deadlock scenario involves two connections modifying two tables in different

  44. Anonymous says:

    Very interestings posts (the three ones).

    In my case I have deadlocks occuring during the syncrhonisation of pull subscribers of our merge replication (sql 2005).

    The trace is showing us system stored procedures of the merge replication involved in the deadlock. What are we supposed to do in that case ? To send these post to MS ? 🙂

    Here is the trace. What would be the actions to do ?

    2009-01-14 15:47:19.89 spid16s     deadlock-list

    2009-01-14 15:47:19.89 spid16s      deadlock victim=process928e38

    2009-01-14 15:47:19.89 spid16s       process-list

    2009-01-14 15:47:19.89 spid16s        process id=process928e38 taskpriority=5 logused=57720 waitresource=KEY: 7:72057595096006656 (de008218ea30) waittime=3343 ownerId=172382185 transactionname=user_transaction lasttranstarted=2009-01-14T15:47:16.470 XDES=0xdae3258 lockMode=U schedulerid=2 kpid=3184 status=suspended spid=166 sbid=0 ecid=0 priority=-5 transcount=2 lastbatchstarted=2009-01-14T15:47:16.410 lastbatchcompleted=2009-01-14T15:47:16.393 clientapp=900SE66SQLPROD2008-saretec-PubSesame-901RA01SQLEXPRESS-27 hostname=667 hostpid=5932 loginname=sa isolationlevel=read committed (2) xactid=172382185 currentdb=7 lockTimeout=4294967295 clientoption1=673384544 clientoption2=128024

    2009-01-14 15:47:19.89 spid16s         executionStack

    2009-01-14 15:47:19.89 spid16s          frame procname=mssqlsystemresource.sys.sp_MSmakegeneration line=489 stmtstart=44260 stmtend=44598 sqlhandle=0x0300ff7f587a2f069f52ee00bb9900000100000000000000

    2009-01-14 15:47:19.89 spid16s     update dbo.MSmerge_genhistory with (rowlock)

    2009-01-14 15:47:19.89 spid16s             set genstatus = 1,

    2009-01-14 15:47:19.89 spid16s                 guidsrc = newid(),

    2009-01-14 15:47:19.89 spid16s                 coldate = getdate()

    2009-01-14 15:47:19.89 spid16s             where genstatus = 3    

    2009-01-14 15:47:19.89 spid16s         inputbuf

    2009-01-14 15:47:19.89 spid16s     Proc [Database Id = 32767 Object Id = 103774808]    

    2009-01-14 15:47:19.89 spid16s        process id=process929d38 taskpriority=0 logused=2668 waitresource=KEY: 7:72057595095810048 (c4026ce1c0d6) waittime=3390 ownerId=172382289 transactionname=UPDATE lasttranstarted=2009-01-14T15:47:16.487 XDES=0xb97ef40 lockMode=X schedulerid=2 kpid=2292 status=suspended spid=108 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-01-14T15:47:16.487 lastbatchcompleted=2009-01-14T15:47:16.487 clientapp=.Net SqlClient Data Provider hostname=018PA01 hostpid=3420 loginname=sa isolationlevel=read committed (2) xactid=172382289 currentdb=7 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056

    2009-01-14 15:47:19.89 spid16s         executionStack

    2009-01-14 15:47:19.89 spid16s          frame procname=saretec.dbo.MSmerge_upd_1F5733FC149A4CBEA3E79A56B5BDD635 line=98 stmtstart=8862 stmtend=10288 sqlhandle=0x030007007da2e90011d07801719b00000000000000000000

    2009-01-14 15:47:19.89 spid16s     update dbo.MSmerge_ctsv_1F5733FC149A4CBEA3E79A56B5BDD635 with (rowlock)

    2009-01-14 15:47:19.89 spid16s             set lineage = { fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) },

    2009-01-14 15:47:19.89 spid16s                 generation = @newgen,

    2009-01-14 15:47:19.89 spid16s                 partchangegen = case when (@partchange = 1 or @joinchange = 1) then @newgen else partchangegen end,

    2009-01-14 15:47:19.89 spid16s                     colv1 = { fn UPDATECOLVBM(colv1, @replnick, @bm, @missingbm, { fn GETMAXVERSION({ fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) }) }) }  

    2009-01-14 15:47:19.89 spid16s             FROM inserted as I JOIN dbo.MSmerge_ctsv_1F5733FC149A4CBEA3E79A56B5BDD635 as V with (rowlock)

    2009-01-14 15:47:19.89 spid16s             ON (I.rowguidcol=V.rowguid)

    2009-01-14 15:47:19.89 spid16s             and V.tablenick = @tablenick

    2009-01-14 15:47:19.89 spid16s             option (force order, loop join)    

    2009-01-14 15:47:19.89 spid16s          frame procname=saretec.dbo.SES_MissionEtat_Maj line=17 stmtstart=814 stmtend=1114 sqlhandle=0x030007002574f346db9f7901719b00000100000000000000

    2009-01-14 15:47:19.89 spid16s     UPDATE T_MISSION

    2009-01-14 15:47:19.89 spid16s     SET INT_ETAT_PRIMAIRE = @INT_ETAT_PRIMAIRE,

    2009-01-14 15:47:19.89 spid16s     SCD_ETATS_SECONDAIRES = @SCD_ETATS_SECONDAIRES

    2009-01-14 15:47:19.89 spid16s     WHERE NUMIDT_MIS = @ID_MISSION    

    2009-01-14 15:47:19.89 spid16s         inputbuf

    2009-01-14 15:47:19.89 spid16s     Proc [Database Id = 7 Object Id = 1190360101]    

    2009-01-14 15:47:19.89 spid16s       resource-list

    2009-01-14 15:47:19.89 spid16s        keylock hobtid=72057595095810048 dbid=7 objectname=saretec.dbo.MSmerge_contents indexname=nc2MSmerge_contents id=lock17ac4800 mode=S associatedObjectId=72057595095810048

    2009-01-14 15:47:19.89 spid16s         owner-list

    2009-01-14 15:47:19.89 spid16s          owner id=process928e38 mode=S

    2009-01-14 15:47:19.89 spid16s         waiter-list

    2009-01-14 15:47:19.89 spid16s          waiter id=process929d38 mode=X requestType=wait

    2009-01-14 15:47:19.89 spid16s        keylock hobtid=72057595096006656 dbid=7 objectname=saretec.dbo.MSmerge_genhistory indexname=c1MSmerge_genhistory id=lock18ea6980 mode=X associatedObjectId=72057595096006656

    2009-01-14 15:47:19.89 spid16s         owner-list

    2009-01-14 15:47:19.89 spid16s          owner id=process929d38 mode=X

    2009-01-14 15:47:19.89 spid16s         waiter-list

    2009-01-14 15:47:19.89 spid16s          waiter id=process928e38 mode=U requestType=wait

  45. bartduncan says:

    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.  

  46. Anonymous says:

    I have already described several deadlock scenarios that involve only one table in another post. This

  47. Anonymous says:

    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.

  48. Anonymous says:

    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-list

    2009-07-28 14:13:29.50 spid18s          owner id=process93a988 mode=X

    2009-07-28 14:13:29.50 spid18s         waiter-list

    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.

  49. bartduncan says:

    Rashmi,

    “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. 

    Bart

  50. Anonymous says:

    Thank you very much.Great article and it saved my day….

  51. Anonymous says:

    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?

    Thanks!

  52. Anonymous says:

    Hello Bart,

    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.

    Regards,

    Jigs

  53. bartduncan says:

    @Chad –

    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.

  54. Anonymous says:

    Thanks for the good article.hope will see more article from you

  55. Anonymous says:

    Bart,

    You should shown great insight into Deadlocks…I have learned tremendously from your 3 articles…

    Please keep it up!

    Thanks!

  56. Anonymous says:

    Thank you so much for this article, great info and very (very!) helpful

  57. Anonymous says:

    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).

  58. Anonymous says:

    Hi,

    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..?

  59. bartduncan says:

    @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.)

  60. bartduncan says:

    @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.)

  61. Anonymous says:

    Hi Brat,

    Could you please let me know what is "Exchange Event" in the deadlock graph?

    Thanks,

    Baburaj

  62. Anonymous says:

    HI,

    awesome post!. Just a question:

    we have two processes that lock in  X  mode a single resource.

    deadlock-list

    deadlock victim=process4677708

     process-list

      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

       executionStack

        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

    unknown    

       inputbuf

    (@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

       executionStack

        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

    unknown    

       inputbuf

    (@Name nvarchar(11),@IDUser int)SELECT * FROM Domains WHERE Name=@Name AND DateDeleted is null AND IDUser=@IDUser    

     resource-list

      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-list

        owner id=process868bc8 mode=X

       waiter-list

        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-list

        owner id=process4677708 mode=X

       waiter-list

        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?

  63. bartduncan says:

    @Alessio –

    > 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…)

     

    HTH,
    Bart

  64. Vannix78 says:

    Hi Bart

    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?

    Tx

  65. bartduncan says:

    @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.

  66. Anonymous says:

    Hi bart,

    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!!!!!

  67. Anonymous says:

    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?

  68. bartduncan says:

    @kat,

    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.)

    HTH,

    Bart

  69. Anonymous says:

    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.

    From msdn.microsoft.com/…/aa175791(v=sql.80).aspx

    DECLARE @Tries tinyint

    SET @Tries = 1

    WHILE @Tries <= 3

    BEGIN

     BEGIN TRANSACTION

     BEGIN TRY

       INSERT Authors VALUES

         (@au_id, @au_lname, '', '', '', '', '',

    '11111', 0)

       WAITFOR DELAY '00:00:05'

       SELECT * FROM authors WHERE au_lname LIKE 'Test%'

       COMMIT

       BREAK

     END TRY

     BEGIN CATCH

       SELECT ERROR_NUMBER() AS ErrorNumber

       ROLLBACK

       SET @Tries = @Tries + 1

       CONTINUE

     END CATCH;

    END

    Thanks again for a great article which I am sure has helped a lot of folks.

  70. bartduncan says:

    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.

  71. Anonymous says:

    Great Article!!! It helps me a lot..

  72. Naomi Nosonovsky says:

    Is there a way to find transaction isolation level for the SQL statements?

  73. 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.

    <TextData><deadlock-list>

    <deadlock victim="process50c0b08">

     <process-list>

      <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">

       <executionStack>

        <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>

       </executionStack>

       <inputbuf>

    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>

      <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">

       <executionStack>

        <frame procname="psi_db.dbo.Add_EmployeeToDep" line="66" stmtstart="3108" stmtend="3410" sqlhandle="0x030006008a00ec2a3ecf7801e59a00000100000000000000">

    if not exists (Select EmployeeNumber

        From Emp_Status
    
        Where EmployeeNumber = @empno and
    
            Employee_type = @emptype
    
        ) &nbsp; &nbsp; &lt;/frame&gt;
    

        <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>

       </executionStack>

       <inputbuf>

    DECLARE @P0 INTEGER

    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>

      </process>

     </process-list>

     <resource-list>

      <keylock hobtid="72057594508935168" dbid="6" objectname="psi_db.dbo.Emp_Status" indexname="IX_Emp_Status1" id="lock52876b580" mode="X" associatedObjectId="72057594508935168">

       <owner-list>

        <owner id="processbaf978" mode="X" />

       </owner-list>

       <waiter-list>

        <waiter id="process50c0b08" mode="S" requestType="wait" />

       </waiter-list>

      </keylock>

      <keylock hobtid="72057594516013056" dbid="6" objectname="psi_db.dbo.master_Employee" indexname="IX_Master_Emp_ID" id="lock52ba26100" mode="X" associatedObjectId="72057594516013056">

       <owner-list>

        <owner id="process50c0b08" mode="X" />

       </owner-list>

       <waiter-list>

        <waiter id="processbaf978" mode="S" requestType="wait" />

       </waiter-list>

      </keylock>

     </resource-list>

    </deadlock>

    </deadlock-list>

    </TextData>

  74. bartduncan says:

    Suba, all deadlocks involve at least two different lock resources, and those resources are often on different indexes. Each of your two sessions holds one of the locks, and is waiting to acquire the lock held by the other session. Take some time to go through the specific steps in this blog post to decode the 1222 output, and hopefully it will give you a clearer understanding of the deadlock scenario.

    Bart

  75. sambeet says:

    excellent article and is still useful after all these years.

  76. Anonymous says:

    Thank you for your wonderful article, I used it to diagnose a deadlock.

    I have a select statement inside a stored procedure that occasionally causes a deadlock with another stored procedure that is running an update.  It appears to be a conflict over an index as you describe in your article.

    Unfortunately, for various reasons I cannot alter the indexes on the tables involved in the deadlock.

    If I use "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" inside the stored procedure that does the read, do I need to call BEGIN TRANSACTION as well?  Currently I do not, as the stored procedure does not change any data.

    Thanks again for your excellent article.

  77. Anonymous says:

    Awesome article….thanks a ton to author, Bart Duncan…

  78. Anonymous says:

    Loved this article, it made my life easier! Good stuff!