Deadlock Troubleshooting, Part 3


Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock in a database:

 

 

Process A

Process B

 

 

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

 

 

If Process A and Process B each reached step #3 in their respective transactions at approximately the same time, it’s easy to see how they could end up blocking each other.  The most obvious solution to this deadlock is to change the order of the UPDATE statements in one of the transactions, so that lock resources are acquired in a consistent order. 

 

Instead of this overly simplistic deadlock, let’s take a closer look at the deadlock scenario demonstrated in Deadlock Troubleshooting, Part 2.  In that case, these two stored procedures ended up deadlocked:

 

       CREATE PROC p1 @p1 int AS

       SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

       GO

 

       CREATE PROC p2 @p1 int AS

       UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

       UPDATE t1 SET c2 = c21 WHERE c1 = @p1

       GO

 

There’s no UPDATE, DELETE, or INSERT in the first proc; it consists of a single SELECT.  And even the two UPDATE statements in the second proc aren’t wrapped in an outer BEGIN TRAN/COMMIT TRAN.  Both UPDATEs ran within their own autocommit transaction, which means that only one of them could have been involved in the deadlock.  Clearly this doesn’t fit the stereotypical “modify A then modify B / modify B then modify A” deadlock model described above.  This isn’t an edge case, by the way. We actually see this type of deadlock – where one or both of the participants are in the middle a single-query, autocommit transaction – more often than easy-to-understand deadlock scenarios involving two multi-statement transactions that just modify two tables in a different order. 

 

So, what would you do if DTA hadn’t automagically recommended a new index that prevented this deadlock?  To craft your own solution by hand, you need a deeper understanding of the deadlock than we have at the moment. 

 

What caused this deadlock?

We’ll need to refer back to the deadlock summary that was distilled from the -T1222 output (see Deadlock Troubleshooting, Part 1 for a refresher on decoding -T1222):

 

            Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
                                SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
                Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
                                UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
               
                The SELECT is waiting for a Shared KEY lock on index t1.cidx.  The UPDATE holds a conflicting X lock.
                The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1.  The SELECT holds a conflicting S lock.

 

First, let’s look at the query plan for the SELECT query.  To view the plan, execute “SET STATISTICS PROFILE ON”, then run “EXEC p1 4”. 

 

   SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

     |–Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))

          |–Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)

          |–Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

 

A Nested Loop join executes its first child operator once, and executes the second child operator for each row returned by the first child (see this post for details).  In this case, the first child is a nonclustered Index Seek to find the rows “WHERE c2 BETWEEN @p1 AND @p1+1”.  For each qualifying row in the nonclustered index, a second seek is done on the clustered index to look up the whole data row.  This clustered index seek is necessary because the nonclustered index does not cover the query.  If you’re running SQL 2000, you’ll see a different-looking plan:

 

   SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

     |–Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([t1]))

          |–Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+1) ORDERED FORWARD)

 

For practical purposes, these two plans are identical.  The purpose of the Bookmark Lookup operator in the SQL 2000 plan is to visit the clustered index to retrieve the full set of columns for a row identified by a nonclustered index.  In SQL 2005 this same operation is expressed as a loop join between the nonclustered index and the clustered index.  For this deadlock, it’s simply important to note that both plans calls for a seek from the nonclustered index, then a seek from the clustered index.

 

Now let’s look at the UPDATE:

 

   UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

     |–Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))

          |–Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))

               |–Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN …

                    |–Top(ROWCOUNT est 0)

                         |–Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)

 

The UPDATE has a fairly simple query plan.  The two most significant operators are the first and the last one.  The Clustered Index Seek locates the rows that quality for the “WHERE c1 = @p1” predicate.  Once a qualifying row has been found, the Clustered Index Update operator acquires an eXclusive key lock on the clustered index and modifies the row. 

 

We now have a full understanding of how the UPDATE blocks the SELECT: the UPDATE acquires an X lock on a clustered index key, and that lock blocks the SELECT’s bookmark lookup on the clustered index.  But the other half of the deadlock – the reason that the SELECT blocks the UPDATE – isn’t quite so obvious.  The -T1222 told us “The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1.  The SELECT holds a conflicting S lock.  It’s not very apparent from the plan, but the UPDATE needs an X lock on the nonclustered index [idx1] because the column it is updating ([c2]) is one of the non-clustered index’s key columns.  Any change to an index key column means that a row in the index must be relocated, and that relocation requires an X lock. 

 

This is a key point to remember when trying to understand many deadlocks: the access path to find the qualifying rows is important, but index updates implied by the columns being modified can be just as important.  To make things more confusing, sometimes you’ll see explicit “Index Update” or “Index Delete” operators in the plan for each nonclustered index that needs to be updated, while other times these don’t show up in the plan.  (For more info on this check out Wide vs. Narrow Plans.) 

 

To summarize: the SELECT used the nonclustered index to find a qualifying row.  While holding a Shared lock on the nonclustered index, it needs to jump over to the clustered index and retrieve some columns that aren’t part of the nonclustered index.  While it’s doing this, the UPDATE is busy doing a seek on the clustered index.  It finds a row, locks it and modifies it.  But because one of the columns being modified is a key column in the nonclustered index, it then has to move to the nonclustered index and update that index, too.  This requires a second X key lock on the nonclustered index.  So, the SELECT ends up blocked waiting for the UPDATE to release his X lock on the clustered index, while the UPDATE winds up blocked and waiting for the SELECT to release his S lock on the nonclustered index. 

 

Hopefully it’s clear that even though each participant in this deadlock is just a single query, this is still a problem caused by out-of-order resource access patterns.  The SELECT statement locks a key in the nonclustered index, then locks a key in the clustered index.  The problem is that the UPDATE needs to lock the same two resources, but because of its query plan, it tries to lock them in the opposite order.  In a sense, it’s really the same problem as the simple deadlock scenario described at the beginning of this post. 

 

The locks acquired by a query aren’t acquired all at once.  A query plan is like a little program.  It wouldn’t be terribly inaccurate, for example, to think of a nested loop join as a FOR loop.  Each iteration of the loop acquires a key lock on the outer table, then holds that lock while looking up (and locking) matching rows in the inner table.  Deadlocks like this one are a little harder to figure out because the order of resource access within a single query depends on the query plan, and can’t be determined just by looking at the T-SQL. 

 

How did DTA’s new index avoid the deadlock? 

Here’s an index that will prevent this deadlock:

            CREATE INDEX idx2 ON t1 (c2, c3)

 

This index “covers” the query “SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1”, which is just another way of saying that the index includes all of the columns referenced by the query.  SQL will use this index instead of the [idx1] index because the plan based on the covering index is cheaper.  The fact that the index covers the query means that the bookmark lookup against the clustered index is no longer necessary.  And since the SELECT no longer needs to access the clustered index, it won’t get blocked by the UPDATE’s lock on the clustered index.  

 

What other solutions are available?

All deadlocks boil down to out-of-order resource access patterns.  In the simple deadlock scenario described at the beginning of this post, the solution is obvious: just reverse the two UPDATE statements in one of the transactions, and you won’t end up deadlocked.  But in the more complex scenario that we just explored, it’s not so clear how to change the order in which locks are acquired.  Each deadlock participant is running a single-query, autocommit transaction, so you can’t just swap the order of two queries to acquire resources in a different order.  SQL is a language designed to express high-level set operations; the specifics of how the database should go about retrieving and updating the specified set of data is generally left up to the SQL engine, with good reason.  However, you do have some options for either influencing which lock resources a query needs, or modifying the order in which it acquires the locks.  Below are six different possible solutions to this deadlock.  Some of these are not ideal for this particular deadlock, but they are still worth exploring since the approach to deadlock avoidance that they illustrate may be the best possible solution for some other deadlock you encounter.

 

  • The new index is arguably the simplest and most elegant solution.  This deadlock occurs because two queries take different paths to the same resource.  The new index avoids the deadlock by eliminating any need for the SELECT to access the row in the clustered index.  As a happy side effect, it also speeds up the SELECT query.  
           CREATE INDEX idx2 ON t1 (c2, c3)
  • If you’re running SQL 2005, you could use the new SNAPSHOT or READ_COMMITTED_SNAPSHOT isolation levels.  
           ALTER DATABASE deadlocktest SET READ_COMMITTED_SNAPSHOT ON
  • Adding a NOLOCK hint to the SELECT will avoid the deadlock, but be cautious of this solution — dirty reads can cause runtime errors and will expose you to uncommitted data.
           ALTER PROC p1 @p1 int AS
           SELECT c2, c3 FROM t1 WITH (NOLOCK) WHERE c2 BETWEEN @p1 AND @p1+1
  • As mentioned above, this deadlock occurs because two queries take different paths to the same resource.  By forcing one of the queries to use the same index as the other query, you can prevent the deadlock.  However, SQL chose query plans that used two different indexes because those were the most efficient plans available for the two queries.  By forcing a different index path, you are actually slowing down one of the queries.  This may be OK since it does avoid the deadlock, but you should test to make sure the cost is acceptable.  
           ALTER PROC p1 @p1 int AS
           SELECT c2, c3 FROM t1 WITH (INDEX=cidx) WHERE c2 BETWEEN @p1 AND @p1+1
    If this query was coming from an application as an ad hoc query (not part of a stored proc), you could either modify the app to specify the index hint or use a plan guide with OPTION (USE PLAN…) if modifying the app wasn’t possible.  Plan guides are available in SQL 2005 and later.
  • One way to look at this deadlock is as a problem that arises because there’s an index on a frequently-updated column.  Dropping the nonclustered index [idx1] will avoid the deadlock by (a) depriving the SELECT of its alternate access path to the row, and (b) preventing the UPDATE from having to update the nonclustered index row when it updates the [c2] column.  Like the prior solution, however, this will slow down the SELECT and any other queries that use this index.  
           DROP INDEX t1.idx1
  • You could force one of the transactions to block at an earlier point, before it has had an opportunity to acquire the lock that ends up blocking the other transaction.  In the example below, the SELECT proc has been modified to run a new query that acquires and holds a lock on the clustered index before it accesses the nonclustered index.  In effect, this changes the order of resource access from (nonclustered, clustered) to (clustered, nonclustered).  Since that’s the same order that the UPDATE uses, the deadlock is no longer an issue.  
           ALTER PROC p1 @p1 int AS
           BEGIN TRAN
             DECLARE @x int
             SELECT @x = COUNT(*) FROM t1 WITH (HOLDLOCK, UPDLOCK) WHERE c1 = @p1
             SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
           COMMIT TRAN

 

If you can think of any other solutions, please share them in a comment.  

Comments (45)

  1. Anonymous says:

    I think one of the major causes of deadlocks in this case is that an X lock has been created.  The second stored proc should create and Update lock not an eXclusive lock.  The reason it has created an exclusive lock is that the same row is updated twice in one statement.  By modifying a stored-proc to only update a row once, often exclusive locks can be avoided and thus deadlocks are reduced.

    The above example is sort of trivial but in many cases a temporary variable could be used to store the value instead of updating the record.  Only in the end have the record update execute just once.  This would resolve the above deadlock I believe.

  2. bartduncan says:

    You could still hit this deadlock even if the second proc only contained one UPDATE statement.  There is no way to modify a row without acquiring an X lock.  An Update lock just indicates an intent to modify a row later.  U locks are acquired while locating a qualifying row, and the U lock must be converted to an X lock when the qualifying row is located.  The purpose of U locks is to prevent deadlocks between two UPDATE statements that target the same row.  

    Try this – I haven’t verified, but it should still deadlock with the SELECT:

          ALTER PROC p2 @p1 int AS

          UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

          GO

          — Batch #2

          USE deadlocktest

          SET NOCOUNT ON

          WHILE (1=1) BEGIN

            BEGIN TRAN

            EXEC p2 4

            ROLLBACK

          END

          GO

  3. bartduncan says:

    Roger:

    As I mentioned in these posts, almost all deadlocks are the result of blocking (typically compounded by poorly-tuned queries), not SQL Server bugs.

    Did you look at http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx for instructions about how to decode the 1222 output?  If you did that, you would end up with a digested version that looks something like this:

       Spid 135 is running this query:
          UPDATE tbph WITH(ROWLOCK) set ClientAccepted =  @p1
          from TaxBankProductHistory tbph inner join
              TaxBankProduct tbp with(nolock) on tbp.OID = tbph.BankProductOID inner join
              TaxPayer tp with(nolock) on tp.SSN = tbp.SSN
          where tbph.ClientAccepted =  @p2
              and tp.OfficeOID =  @p3  OPTION (MAXDOP 1)
       Spid 153 is running the same query.

       Spid 135 is waiting for an Update key lock on index TaxBankProductHistory.IX__BankProductHistory__BankProductOID_OID_CreateDate_ClientAccepted.
           (Spid 153 holds a conflicting Update lock.)
       Spid 153 is waiting for an Update lock on a different key in the same index. 
           (Spid 135 holds a conflicting Update lock.)

    Once you’ve identified the queries involved, the next recommended step is to crank the queries through DTA to see if a more efficient query plan would reduce the lock footprint enough to avoid the deadlock.  Have you tried this?  If not, I would recommend doing so before wasting time on manual tuning efforts.

  4. rogerlin says:

    Hi Bart,

    Thank you for your advise on this one. I have put this query in the query window:

    DECLARE @p1 TINYINT, @p2 TINYINT, @p3 INT

    SELECT @p1 = 1, @p2 = 0, @p3 = 330

    UPDATE trh WITH(ROWLOCK)

      set ClientAccepted =  @p1

     from TaxReturnHistory trh

     join TaxPayer tp with(nolock) on tp.SSN = trh.SSN

    where trh.ClientAccepted = @p2 and tp.OfficeOID = @p3

    OPTION (MAXDOP 1)

    Run the DTA, it came up with no recommandations. The tables all have covered indexes. This deadlock is hard to repro, we run our stress test over night which may encounter one. I can see how two process running a different statement can get deadlock. As these processes running the same statement and deadlock on the same table, same index, how does that happen? Here we only need to update one column with value either 0 or 1, it’s on a covered index, why more than one lock is required to get this done? Could you please explain what was going on here and provide us an example how we can prepro this type of deadlock so we may understand it better?

    Thanks a lot,

    Roger

  5. rogerlin says:

    When two processes running the same update statement with a different key, should they use the same path to access their own set of rows? Since they use the same path with a different search key value, they should not request the same lock resource, should they? If that is the case, should we only expect to see blocking instead of deadlock? Just hope you will cover this type of deadlocks in your articles. Thanks!

  6. bartduncan says:

    Roger:

    Your comment about the two UPDATEs needing to lock the same row implies that a query should never have to lock any rows except those that it is ultimately going to act on.  That is not correct, unfortunately.  As you said, every query has an "access path" that needs to be followed to locate the rows that the query will retrieve or modify.  On its way to locate the qualifying rows, a query may touch and lock many other rows that will not ultimately be retrieved.  This fact is at the core of the general recommendation to make sure that there are no better indexes for the query; a more selective index seek means fewer rows touched, which means less chance of getting blocked by or blocking another query.

    My advice is to get the query plans and evaluate which of the deadlock avoidance techniques listed on http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx might apply to your situation.  There’s also a chance that an XLOCK hint might prevent your deadlock scenario, but that’s just speculation; there’s really not enough data here for me to make a more concrete recommendation.  

    Bart

  7. rogerlin says:

    Hi Bart,

    For sure you are the expert on deadlock! I thought I was getting the deadlock on the covered index, it turned out I was wrong. Look into the query plan one of the table was having a index scan for a merge join. I add the needed key to the index, wow, the plan changed completely and only touched the rows it qualified to be update. I am sure there is no more chance for another deadlock since we don’t need to touch anything out side of the data set for the search key. That seems a very simple thing for the DTA to pick it up, it didn’t. Also do we have the object id in the deadlock graph? I searched the table id, it wasn’t there, only the stored proc id was there as the object id. We have the key information there, it’s dbid, page, then the key or/and rowid? Would be possible you can show me how to get the table id out of the key?

    Thank you a lot, simply looking at the plan it straighted out this one. You are the best.

    Roger

  8. bartduncan says:

    Roger,

    The Deadlock Avoidance, Part 1 post (http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx) shows where the object (and index) name for each lock resource is located in the -T1222 output (the "objectname" attribute of each item in the <resource-list> element).  

    HTH,

    Bart

  9. Anonymous says:

    wouldn’t a simple holdlok hint on the select query solve the problem?

  10. Anonymous says:

    Hi Bart,

    I got this very unusual deadlock, looks like was from a paralelle plan for a single row update between two update for a different id. I don’t believe it would deadlock if it was a serial plan. I haven’t had chance to search in vkb yet but feel this one is more fishing like a bug, please let me know if I am wrong again.

    Here is the deadlock graph from the errorlog, hope will fit in this text box:

    2007-02-26 11:37:09.93 spid19s     deadlock-list

    2007-02-26 11:37:09.93 spid19s      deadlock victim=process8ccf28

    2007-02-26 11:37:09.93 spid19s       process-list

    2007-02-26 11:37:09.93 spid19s        process id=process6d9b58 taskpriority=0 logused=20004 waittime=4421 schedulerid=1 kpid=9812 status=suspended spid=709 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=process8ccf28 taskpriority=0 logused=0 waitresource=KEY: 7:72057594069254144 (68005a494ccf) waittime=4218 ownerId=3431587031 transactionname=UPDATE lasttranstarted=2007-02-26T11:37:03.620 XDES=0x7446e940 lockMode=U schedulerid=2 kpid=8608 status=suspended spid=708 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.587 lastbatchcompleted=2007-02-26T11:37:03.570 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587031 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000039ef242194b27bba37bd021a11bbe604fa6bf6c7

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180935 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=process8cd2e8 taskpriority=0 logused=20004 waittime=4031 schedulerid=2 kpid=9948 status=suspended spid=710 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processae1798 taskpriority=0 logused=0 waitresource=KEY: 7:72057594069254144 (470105845e1e) waittime=4390 ownerId=3431586932 transactionname=UPDATE lasttranstarted=2007-02-26T11:37:03.337 XDES=0x2a9efe10 lockMode=U schedulerid=3 kpid=8332 status=suspended spid=709 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processb75108 taskpriority=0 logused=0 waitresource=KEY: 7:72057594069254144 (68005a494ccf) waittime=3921 ownerId=3431587049 transactionname=UPDATE lasttranstarted=2007-02-26T11:37:03.633 XDES=0x36b0d6d0 lockMode=U schedulerid=4 kpid=7632 status=suspended spid=710 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processc093d8 taskpriority=0 logused=20004 waittime=3781 schedulerid=5 kpid=9220 status=suspended spid=710 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processfd56a8 taskpriority=0 logused=20004 waittime=4718 schedulerid=8 kpid=7528 status=suspended spid=709 sbid=0 ecid=5 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processfd5f18 taskpriority=0 logused=20004 waittime=3937 schedulerid=8 kpid=9452 status=suspended spid=710 sbid=0 ecid=5 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s       resource-list

    2007-02-26 11:37:09.93 spid19s        keylock hobtid=72057594069254144 dbid=7 objectname=fc_tax_content06.dbo.TaxPayerBilling indexname=IX__TaxPayerBilling__FederalOID_ReturnOID id=lock29b9e0c0 mode=U associatedObjectId=72057594069254144

    2007-02-26 11:37:09.93 spid19s         owner-list

    2007-02-26 11:37:09.93 spid19s          owner id=processfd5f18 mode=U

    2007-02-26 11:37:09.93 spid19s         waiter-list

    2007-02-26 11:37:09.93 spid19s          waiter id=processae1798 mode=U requestType=wait

    2007-02-26 11:37:09.93 spid19s        exchangeEvent id=portaa6cae8 nodeId=4

    2007-02-26 11:37:09.93 spid19s         owner-list

    2007-02-26 11:37:09.93 spid19s          owner event=e_waitNone type=producer id=processae1798

    2007-02-26 11:37:09.93 spid19s         waiter-list

    2007-02-26 11:37:09.93 spid19s          waiter event=e_waitPortOpen type=producer id=processfd56a8

    2007-02-26 11:37:09.93 spid19s          waiter event=e_waitPortOpen type=producer id=process6d9b58

    2007-02-26 11:37:09.93 spid19s        keylock hobtid=72057594069254144 dbid=7 objectname=fc_tax_content06.dbo.TaxPayerBilling indexname=IX__TaxPayerBilling__FederalOID_ReturnOID id=lock2dd4a8c0 mode=U associatedObjectId=72057594069254144

    2007-02-26 11:37:09.93 spid19s         owner-list

    2007-02-26 11:37:09.93 spid19s          owner id=processfd56a8 mode=U

    2007-02-26 11:37:09.93 spid19s         waiter-list

    2007-02-26 11:37:09.93 spid19s          waiter id=process8ccf28 mode=U requestType=wait

    2007-02-26 11:37:09.93 spid19s          waiter id=processb75108 mode=U requestType=wait

    2007-02-26 11:37:09.93 spid19s        exchangeEvent id=portaa6c3e0 nodeId=4

    2007-02-26 11:37:09.93 spid19s         owner-list

    2007-02-26 11:37:09.93 spid19s          owner event=e_waitNone type=producer id=processb75108

    2007-02-26 11:37:09.93 spid19s         waiter-list

    2007-02-26 11:37:09.93 spid19s          waiter event=e_waitPortOpen type=producer id=processfd5f18

    2007-02-26 11:37:09.93 spid19s          waiter event=e_waitPortOpen type=producer id=process8cd2e8

    2007-02-26 11:37:09.93 spid19s          waiter event=e_waitPortOpen type=producer id=processc093d8

    2007-02-26 11:37:09.93 spid19s     deadlock-list

    2007-02-26 11:37:09.93 spid19s      deadlock victim=processb75108

    2007-02-26 11:37:09.93 spid19s       process-list

    2007-02-26 11:37:09.93 spid19s        process id=process6d9b58 taskpriority=0 logused=20004 waittime=4421 schedulerid=1 kpid=9812 status=suspended spid=709 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=process8cd1f8 taskpriority=0 logused=20004 waittime=4968 schedulerid=2 kpid=1772 status=suspended spid=709 sbid=0 ecid=3 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=process8cd2e8 taskpriority=0 logused=20004 waittime=4031 schedulerid=2 kpid=9948 status=suspended spid=710 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processae1798 taskpriority=0 logused=0 waitresource=KEY: 7:72057594069254144 (470105845e1e) waittime=4390 ownerId=3431586932 transactionname=UPDATE lasttranstarted=2007-02-26T11:37:03.337 XDES=0x2a9efe10 lockMode=U schedulerid=3 kpid=8332 status=suspended spid=709 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processb75108 taskpriority=0 logused=0 waitresource=KEY: 7:72057594069254144 (68005a494ccf) waittime=3921 ownerId=3431587049 transactionname=UPDATE lasttranstarted=2007-02-26T11:37:03.633 XDES=0x36b0d6d0 lockMode=U schedulerid=4 kpid=7632 status=suspended spid=710 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processb75c48 taskpriority=0 logused=20004 waittime=4359 schedulerid=4 kpid=3552 status=suspended spid=709 sbid=0 ecid=2 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processc093d8 taskpriority=0 logused=20004 waittime=3781 schedulerid=5 kpid=9220 status=suspended spid=710 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processd1d5b8 taskpriority=0 logused=20004 waittime=3968 schedulerid=6 kpid=7216 status=suspended spid=709 sbid=0 ecid=4 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processf40e38 taskpriority=0 logused=20004 waittime=4265 schedulerid=7 kpid=7428 status=suspended spid=709 sbid=0 ecid=1 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processfd56a8 taskpriority=0 logused=20004 waittime=4718 schedulerid=8 kpid=7528 status=suspended spid=709 sbid=0 ecid=5 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.307 lastbatchcompleted=2007-02-26T11:37:03.307 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431586932 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x0200000000dad81d584fea62bf8da39731a2cb796af12a93

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180934 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s        process id=processfd5f18 taskpriority=0 logused=20004 waittime=3937 schedulerid=8 kpid=9452 status=suspended spid=710 sbid=0 ecid=5 priority=0 transcount=0 lastbatchstarted=2007-02-26T11:37:03.603 lastbatchcompleted=2007-02-26T11:37:03.603 clientapp=Electronic Filing Center hostname=NFCAS11 hostpid=1756 isolationlevel=read committed (2) xactid=3431587049 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056

    2007-02-26 11:37:09.93 spid19s         executionStack

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c69401

    2007-02-26 11:37:09.93 spid19s     (@1 numeric(3,3),@2 numeric(4,3),@3 int,@4 tinyint)UPDATE [TaxPayerBilling] WITH(rowlock)  set [ApplicationSVBFee] = @1,[ApplicationOrrtaxFee] = @2  WHERE [ReturnOID]=@3 AND [Active]=@4    

    2007-02-26 11:37:09.93 spid19s          frame procname=adhoc line=1 sqlhandle=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe462

    2007-02-26 11:37:09.93 spid19s     UPDATE TaxPayerBilling WITH (ROWLOCK) SET ApplicationSVBFee =     0.000 , ApplicationOrrtaxFee =     3.000 WHERE ReturnOID = 2180936 AND Active = 1    

    2007-02-26 11:37:09.93 spid19s         inputbuf

    2007-02-26 11:37:09.93 spid19s       resource-list

    2007-02-26 11:37:09.93 spid19s        keylock hobtid=72057594069254144 dbid=7 objectname=fc_tax_content06.dbo.TaxPayerBilling indexname=IX__TaxPayerBilling__FederalOID_ReturnOID id=lock29b9e0c0 mode=U associatedObjectId=72057594069254144

    2007-02-26 11:37:09.93 spid19s         owner-list

    2007-02-26 11:37:09.93 spid19s          owner id=processfd5f18 mode=U

    2007-02-26 11:37:09.94 spid19s         waiter-list

    2007-02-26 11:37:09.94 spid19s          waiter id=processae1798 mode=U requestType=wait

    2007-02-26 11:37:09.94 spid19s        exchangeEvent id=portaa6cae8 nodeId=4

    2007-02-26 11:37:09.94 spid19s         owner-list

    2007-02-26 11:37:09.94 spid19s          owner event=e_waitNone type=producer id=processae1798

    2007-02-26 11:37:09.94 spid19s         waiter-list

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=processf40e38

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=processb75c48

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=process8cd1f8

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=processd1d5b8

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=processfd56a8

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=process6d9b58

    2007-02-26 11:37:09.94 spid19s        keylock hobtid=72057594069254144 dbid=7 objectname=fc_tax_content06.dbo.TaxPayerBilling indexname=IX__TaxPayerBilling__FederalOID_ReturnOID id=lock2dd4a8c0 mode=U associatedObjectId=72057594069254144

    2007-02-26 11:37:09.94 spid19s         owner-list

    2007-02-26 11:37:09.94 spid19s          owner id=processfd56a8 mode=U

    2007-02-26 11:37:09.94 spid19s         waiter-list

    2007-02-26 11:37:09.94 spid19s          waiter id=processb75108 mode=U requestType=wait

    2007-02-26 11:37:09.94 spid19s        exchangeEvent id=portaa6c3e0 nodeId=4

    2007-02-26 11:37:09.94 spid19s         owner-list

    2007-02-26 11:37:09.94 spid19s          owner event=e_waitNone type=producer id=processb75108

    2007-02-26 11:37:09.94 spid19s         waiter-list

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=processfd5f18

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=process8cd2e8

    2007-02-26 11:37:09.94 spid19s          waiter event=e_waitPortOpen type=producer id=processc093d8

    Looks like all are in.

    Thanks,

    Roger

  11. bartduncan says:

    Roger,

    If you take a moment to look at the query plan for the affected queries, I think you’ll find that your deadlock actually does involve parallel query plans (though the parallelism is not the cause of the deadlock).  I suspect that if you had an appropriate index so that SQL doesn’t have to touch so many rows in order to find the one that qualifies for your WHERE clause, you would be able to prevent this deadlock.  Seriously, try the steps laid out in http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx :).  They won’t solve every deadlock, but they will help most (including the two that you have posted here).  

    HTH,

    Bart

  12. bartduncan says:

    elad said:

    wouldn’t a simple holdlok hint on the select query solve the problem?

    elad,

    No.  In fact, the deadlock here occurs precisely because both queries hold their lock on one index while trying to acquire a lock on a different index.  Feel free to try it out, thoughl http://blogs.msdn.com/bartd/archive/2006/09/13/Deadlock-Troubleshooting_2C00_-Part-2.aspx includes scripts that you can run to reproduce the deadlock discussed in these three posts.  

    HOLDLOCK can be effective way to prevent deadlocks in some other cases (most commonly on a SELECT in combination with an UPDLOCK hint to force serialization at an earlier point in a multi-statement transaction).  

    Thanks,

    Bart

  13. Anonymous says:

    Hi Bart,

    Does this show the parallel threads deadlocked amount themselves?

    2007-04-21 09:54:02.410 spid5s       deadlock-list

    2007-04-21 09:54:02.410 spid5s        deadlock victim=process8cd018

    2007-04-21 09:54:02.410 spid5s         process-list

    2007-04-21 09:54:02.410 spid5s          process id=process6d86b8 taskpriority=0 logused=20018 waittime=31 schedulerid=1 kpid=8404 status=suspended spid=648 sbid=0 ecid=5 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.410 spid5s           executionStack

    2007-04-21 09:54:02.410 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.410 spid5s       unknown    

    2007-04-21 09:54:02.410 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.410 spid5s       unknown    

    2007-04-21 09:54:02.410 spid5s           inputbuf

    2007-04-21 09:54:02.410 spid5s          process id=process6d8c58 taskpriority=0 logused=20022 waittime=31 schedulerid=1 kpid=9776 status=suspended spid=648 sbid=0 ecid=14 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.410 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=process6d9108 taskpriority=0 logused=20020 waittime=31 schedulerid=1 kpid=10036 status=suspended spid=648 sbid=0 ecid=10 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=process8cc7a8 taskpriority=0 logused=20022 waittime=31 schedulerid=2 kpid=8768 status=suspended spid=648 sbid=0 ecid=16 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=process8cd018 taskpriority=0 logused=20014 waittime=109 schedulerid=2 kpid=1100 status=suspended spid=648 sbid=0 ecid=1 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=process8cda68 taskpriority=0 logused=20020 waittime=31 schedulerid=2 kpid=8796 status=suspended spid=648 sbid=0 ecid=12 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=process8cdc48 waittime=31 schedulerid=2 kpid=0

    2007-04-21 09:54:02.430 spid5s          process id=process8cde28 taskpriority=0 logused=20018 waittime=31 schedulerid=2 kpid=7648 status=suspended spid=648 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=processb744d8 taskpriority=0 logused=20020 waittime=31 schedulerid=4 kpid=6672 status=suspended spid=648 sbid=0 ecid=11 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=processb74a78 taskpriority=0 logused=20018 waittime=31 schedulerid=4 kpid=4420 status=suspended spid=648 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=processb74d48 taskpriority=0 logused=20022 waittime=15 schedulerid=4 kpid=3668 status=suspended spid=648 sbid=0 ecid=15 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=processf406b8 taskpriority=0 logused=20020 waittime=31 schedulerid=7 kpid=9464 status=suspended spid=648 sbid=0 ecid=9 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s          process id=processf41978 taskpriority=0 logused=20022 waittime=31 schedulerid=7 kpid=5020 status=suspended spid=648 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2007-04-21T09:54:00.993 lastbatchcompleted=2007-04-21T09:54:00.993 clientapp=SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1) hostname=NFCDS01 hostpid=1808 isolationlevel=read committed (2) xactid=5170183470 currentdb=12 lockTimeout=4294967295 clientoption1=673187936 clientoption2=128056

    2007-04-21 09:54:02.430 spid5s           executionStack

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de500759800000100000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s            frame procname=unknown line=1 sqlhandle=0x01000c00d4094b3820944741000000000000000000000000

    2007-04-21 09:54:02.430 spid5s       unknown    

    2007-04-21 09:54:02.430 spid5s           inputbuf

    2007-04-21 09:54:02.430 spid5s         resource-list

    2007-04-21 09:54:02.430 spid5s          threadpool id=scheduler9d0040

    2007-04-21 09:54:02.430 spid5s           owner-list

    2007-04-21 09:54:02.430 spid5s            owner id=process8cde28

    2007-04-21 09:54:02.430 spid5s            owner id=process8cda68

    2007-04-21 09:54:02.430 spid5s            owner id=process8cd018

    2007-04-21 09:54:02.430 spid5s            owner id=process8cc7a8

    2007-04-21 09:54:02.430 spid5s           waiter-list

    2007-04-21 09:54:02.430 spid5s            waiter id=process8cdc48

    2007-04-21 09:54:02.430 spid5s          exchangeEvent id=portaa6c7a0 nodeId=22

    2007-04-21 09:54:02.430 spid5s           owner-list

    2007-04-21 09:54:02.430 spid5s            owner event=pending id=process8cdc48

    2007-04-21 09:54:02.430 spid5s           waiter-list

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=processf41978

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process6d8c58

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=processb74d48

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process8cc7a8

    2007-04-21 09:54:02.430 spid5s          exchangeEvent id=portaa6d358 nodeId=20

    2007-04-21 09:54:02.430 spid5s           owner-list

    2007-04-21 09:54:02.430 spid5s            owner event=e_waitNone type=producer id=processf41978

    2007-04-21 09:54:02.430 spid5s            owner event=pending id=process8cdc48

    2007-04-21 09:54:02.430 spid5s           waiter-list

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=processf406b8

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process6d9108

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=processb744d8

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process8cda68

    2007-04-21 09:54:02.430 spid5s          exchangeEvent id=portaa6c728 nodeId=18

    2007-04-21 09:54:02.430 spid5s           owner-list

    2007-04-21 09:54:02.430 spid5s            owner event=e_waitNone type=producer id=processf406b8

    2007-04-21 09:54:02.430 spid5s            owner event=pending id=process8cdc48

    2007-04-21 09:54:02.430 spid5s           waiter-list

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process6d86b8

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=processb74a78

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process8cde28

    2007-04-21 09:54:02.430 spid5s          exchangeEvent id=portaa6cf98 nodeId=14

    2007-04-21 09:54:02.430 spid5s           owner-list

    2007-04-21 09:54:02.430 spid5s            owner event=e_waitNone type=producer id=process6d86b8

    2007-04-21 09:54:02.430 spid5s            owner event=pending id=process8cdc48

    2007-04-21 09:54:02.430 spid5s           waiter-list

    2007-04-21 09:54:02.430 spid5s            waiter event=e_waitPortOpen type=consumer id=process8cd018

    Notice they are all from the same job step with the same statement start and end position.

    Thanks,

    Roger

  14. bartduncan says:

    Roger,

    You’re right — this is a parallel thread deadlock.  The key indicator of this is the fact that the resources involved in the deadlock (see the "resource-list" section) are not lock resources; they are "exchangeEvent" resources, instead.  

    Unfortunately, because all of the deadlock participants were child threads, in this case the -T1222 output doesn’t provide the inputbuffer.  You could either use the fact that this was "SQLAgent – TSQL JobStep (Job 0x2B2BB728F365D042A64133DB2E890283 : Step 1)" to track down what query deadlocked, or capture a profiler trace with SQL:BatchStarting/RPC:Starting plus the Deadlock XML Graph event to identify the query.  Once you have identified the query that is deadlocking with itself, a MAXDOP 1 hint will work around the problem.  Alternatively, you could probably eliminate the parallelism and make the query much faster as a happy side effect by tuning the query with good supporting indexes.  

    HTH,

    Bart

  15. Anonymous says:

    I am trying to troubleshoot a deadlock but i cannot determine what is causing it because in the deadlock information that is being captured i cannot see any queries or other information. I have both 1222 and 1204 enabled.

    Any ideas?

    deadlock-list

    deadlock victim=process38316d8

     process-list

      process id=process3808478 taskpriority=0 logused=10009 waittime=593 schedulerid=1 kpid=216 status=suspended spid=51 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process3809ac8 taskpriority=0 logused=20009 waittime=593 schedulerid=1 kpid=5672 status=suspended spid=51 sbid=0 ecid=17 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process38136d8 taskpriority=0 logused=20009 waittime=593 schedulerid=2 kpid=5644 status=suspended spid=51 sbid=0 ecid=16 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process3813828 taskpriority=0 logused=10009 waittime=593 schedulerid=2 kpid=6064 status=suspended spid=51 sbid=0 ecid=9 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process381c478 taskpriority=0 logused=10009 waittime=593 schedulerid=3 kpid=5292 status=suspended spid=51 sbid=0 ecid=10 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process381d2e8 taskpriority=0 logused=20009 waittime=593 schedulerid=3 kpid=4372 status=suspended spid=51 sbid=0 ecid=19 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process38265c8 taskpriority=0 logused=10009 waittime=593 schedulerid=4 kpid=5552 status=suspended spid=51 sbid=0 ecid=11 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process3827ac8 taskpriority=0 logused=20009 waittime=593 schedulerid=4 kpid=5716 status=suspended spid=51 sbid=0 ecid=18 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process38309b8 waittime=609 schedulerid=5 kpid=0

      process id=process38312e8 taskpriority=0 logused=20012 waittime=625 schedulerid=5 kpid=3204 status=suspended spid=51 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process38316d8 taskpriority=0 logused=10009 waittime=593 schedulerid=5 kpid=5108 status=suspended spid=51 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process383a718 taskpriority=0 logused=20012 waittime=625 schedulerid=6 kpid=5216 status=suspended spid=51 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process383ada8 waittime=609 schedulerid=6 kpid=0

      process id=process383beb8 taskpriority=0 logused=10009 waittime=593 schedulerid=6 kpid=5852 status=suspended spid=51 sbid=0 ecid=14 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process3845588 taskpriority=0 logused=20009 waittime=609 schedulerid=7 kpid=6096 status=suspended spid=51 sbid=0 ecid=15 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

      process id=process38456d8 taskpriority=0 logused=20005 waittime=625 schedulerid=7 kpid=760 status=suspended spid=51 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 loginname=generic isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

    Proc [Database Id = 5 Object Id = 1557580587]    

      process id=process3845c18 taskpriority=0 logused=10009 waittime=593 schedulerid=7 kpid=5992 status=suspended spid=51 sbid=0 ecid=12 priority=0 transcount=0 lastbatchstarted=2008-04-04T22:01:45.127 lastbatchcompleted=2008-04-04T22:01:45.127 clientapp=.Net SqlClient Data Provider hostname=ACN357NICVPS01 hostpid=920 isolationlevel=read committed (2) xactid=6236579 currentdb=5 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

       executionStack

        frame procname=unknown line=1 sqlhandle=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62

    unknown    

        frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000

    unknown    

        frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000

    unknown    

       inputbuf

     resource-list

      threadpool id=scheduleree6080

       owner-list

        owner id=process38316d8

        owner id=process38312e8

       waiter-list

        waiter id=process38309b8

      exchangeEvent id=port80140950 nodeId=9

       owner-list

        owner event=pending id=process383ada8

        owner event=pending id=process38309b8

       waiter-list

        waiter event=e_waitPortOpen type=consumer id=process3813828

        waiter event=e_waitPortOpen type=consumer id=process3808478

        waiter event=e_waitPortOpen type=consumer id=process381c478

        waiter event=e_waitPortOpen type=consumer id=process38265c8

        waiter event=e_waitPortOpen type=consumer id=process3845c18

        waiter event=e_waitPortOpen type=consumer id=process38316d8

        waiter event=e_waitPortOpen type=consumer id=process383beb8

        waiter event=e_waitPortOpen type=producer id=process3845588

        waiter event=e_waitPortOpen type=producer id=process38136d8

        waiter event=e_waitPortOpen type=producer id=process3809ac8

        waiter event=e_waitPortOpen type=producer id=process3827ac8

        waiter event=e_waitPortOpen type=producer id=process381d2e8

      exchangeEvent id=port80140690 nodeId=5

       owner-list

        owner event=pending id=process383ada8

        owner event=pending id=process38309b8

       waiter-list

        waiter event=e_waitPortOpen type=consumer id=process38456d8

      exchangeEvent id=port80140c10 nodeId=12

       owner-list

        owner event=pending id=process383ada8

        owner event=pending id=process38309b8

       waiter-list

        waiter event=e_waitPortOpen type=producer id=process38312e8

        waiter event=e_waitPortOpen type=producer id=process383a718

      threadpool id=scheduleref6080

       owner-list

        owner id=process383beb8

        owner id=process383a718

       waiter-list

        waiter id=process383ada8

    NULL

    Victim Resource Owner:

    ResType:ExchangeId Stype:’AND’ SPID:51 BatchID:0 ECID:13 TaskProxy:(0x00000000809BF8F0) Value:0x2a7c0460 Cost:(0/10009)

  16. bartduncan says:

    Your deadlock is not a "normal" lock deadlock; it’s a parallel thread deadlock.  Note that the resources are "exchangeEvent" resources, and all participants in the deadlock are threads from the same spid (51).  This is a query that is deadlocking with itself.  

    It’s a query in a longish proc (line number 546), but to get the specific proc name and stmt text you’ll need to capture a profiler trace.  Once you’ve identified the query, the easiest workaround is to prevent parallel execution either by tuning the query (parallelism like this generally suggests a large table scan, hash, or sort) or by adding an "OPTION (MAXDOP 1)" query hint to the query.  

  17. Anonymous says:

    Hi Bart,

    Sorry, I didn’t have time to read all the comments(maybe this situation is mentioned in them) but one of the most "baffling" deadlocks is when you have a select being the cause of the deadlock: you have an update and a select on different rows in the table but the select generates a table scan . After aquring several shared locks SQL Server 2000 decides that an table lock escalation  is necessary and voila, you have the deadlock. I wonder if this happens in 2005 also.

  18. bartduncan says:

    That sort of deadlock can definitely happen in SQL 2005 (or 2008).  It’s common for people to assume that a query won’t touch any rows except those that it will end up modifying or selecting, but it is often the case that the indexes on the table don’t allow for such an efficient query plan.  Index tuning may be necessary to prevent scans or limit the number of locks that a query needs to acquire in some other way.  

    (As an aside, lock escalation never blocks; if SQL can’t escalate to a table lock to because someone else holds an incompatible lock, it will continue acquiring locks at the row or page level.  But you can still run into a SELECT vs. UPDATE deadlock.  In fact, that’s the scenario in the deadlock dissected in this post.)

  19. Anonymous says:

    The T1204 message was pretty specific, something like escalating to table lock(sorry, my memory doesn’t help me when it comes to generated messages:-). The update was doing the rest by holding a lock. Indexes didn’t help because the table scan was generated by something like str1 = UPPER(str2), so the optimizer wouldn’t use any indexes. So what do you mean by lock escalation never blocks? Please elaborate.

  20. bartduncan says:

    I mean that a lock escalation attempt will never block.  As SQL scans a table, it has thresholds that, when crossed, trigger an attempt to escalate.  Escalation means acquiring a TABle lock, then releasing all PAGe/ROW/KEY locks.  If someone holds a lock on the table that would block the TAB lock, the escalation attempt is aborted, and the scan continues to acquire locks at the original granularity (row, key, or page).  Periodically (every N locks), it will retry the escalation, but the subsequent attempts will also not block.  

    This is the way the lock manager has handled escalation since SQL 7.  

    A SELECT can deadlock with an UPDATE without requiring a TAB lock (see the scenario in this blog post, for example).  Or the engine can decide at the beginning of the query that a TAB lock would be the best locking strategy, in which case a table lock would be involved even though no escalation occurred (lock escalation is, by definition, a decision to promote a existing set of KEY/ROW/PAG locks to a TAB lock).  But you should never see a a blocking incident where a lock escalation attempt is stuck waiting for someone else to release an incompatible TAB lock.  If you ever see someone blocked waiting on a TAB lock, it is always because that lock type was decided on before the query began executing.

  21. Anonymous says:

    Hi Bart,

    Now that I got your attention(sorry for the delay between posts): the version based isolation level should get rid of the deadlocks mentioned above. WHat would be the dangers of completely switching to this isolation level?

  22. bartduncan says:

    Yes, snapshot isolation/read versioning is one of the alternate solutions identified above.  The main danger is that it allows you to read a stale image of data that is currently being modified by someone else.  See the discussion in the Definition section of http://en.wikipedia.org/wiki/Snapshot_isolation for some more info.  Whether this risk matters is dependent on the application.  

  23. Anonymous says:

    I read somewhere that the snapshot  will put pressure on tempdb inducing performance problems. Personally I doubt that, but did you guys have more data about the impact on performance?

  24. bartduncan says:

    Yes, that can happen.  If used on databases with high transaction rates, snapshot isolation can also increase the size of tempdb (to store the pre-modification row versions).  In many cases, neither of these will be an issue, but both problems can and do happen.  

    I don’t think it’s possible to quantify the impact in a simplistic way (e.g. read committed snapshot isolation will slow down your transactions by X% and will cause tempdb to grow by Y%).  The affect, if any, depends on far too many variables: current size and % free space in tempdb, speed of the disks that tempdb is on, whether the disks servicing tempdb also have to deal with log writes or other I/O-intensive operations, the user database’s transaction rate, amount of log records generated for a typical write transaction, length of time that transactions stay open, proportion of read-only vs. write transactions, likelihood that the rows being modified will need to be read concurrently by someone else, etc etc…  

    The only way to find out for sure that I know of is to test.  Or, if you don’t have a realistic QA environment, flip the switch during a non-peak period and say a prayer.  In most environments it will work without any fuss.  In some you may have to make some config changes (for example, increase tempdb size, or move tempdb to a dedicated disk).  In some more extreme cases you may find that you can’t safely use snapshot isolation levels, and will need to look at one other other possible deadlock solutions.

  25. Anonymous says:

    I have the following deadlock which was caused through the same spid:

    2008-06-18 11:31:35.260 spid16s      deadlock-list

    2008-06-18 11:31:35.260 spid16s       deadlock victim=process3831c18

    2008-06-18 11:31:35.260 spid16s        process-list

    2008-06-18 11:31:35.260 spid16s         process id=process3826c58 taskpriority=0 logused=4244 waitresource=PAGE: 7:1:825 waittime=2359 ownerId=2757801102 transactionname=implicit_transaction lasttranstarted=2008-06-18T11:31:30.933 XDES=0x4394e0370 lockMode=U schedulerid=4 kpid=2576 status=suspended spid=86 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2008-06-18T11:31:32.887 lastbatchcompleted=2008-06-18T11:31:32.887 clientapp=SOCRATE CONTABILITATE hostname=TS1 hostpid=9472 loginname=BotaN isolationlevel=read committed (2) xactid=2757801102 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128058

    2008-06-18 11:31:35.260 spid16s          executionStack

    2008-06-18 11:31:35.260 spid16s           frame procname=rcs.dbo.CNT18_RulajeDocCont line=399 stmtstart=28380 stmtend=29620 sqlhandle=0x030007002a4c5604732423010b9800000100000000000000

    2008-06-18 11:31:35.260 spid16s      UPDATE rulajevaluta

    2008-06-18 11:31:35.260 spid16s       SET rulajdebit = COALESCE (r.rulajdebit, 0) + COALESCE (i.rd, 0),

    2008-06-18 11:31:35.260 spid16s       rulajcredit = COALESCE (r.rulajcredit, 0) + COALESCE (i.rc, 0)

    2008-06-18 11:31:35.260 spid16s       FROM rulajevaluta r

    2008-06-18 11:31:35.260 spid16s       JOIN @rulajevaluta i ON i.cont = r.cont

    2008-06-18 11:31:35.260 spid16s       AND i.conta = r.conta

    2008-06-18 11:31:35.260 spid16s       AND i.conts = r.conts

    2008-06-18 11:31:35.260 spid16s       AND i.contb = r.contb

    2008-06-18 11:31:35.260 spid16s       AND i.contu = r.contu

    2008-06-18 11:31:35.260 spid16s       AND i.contv = r.contv

    2008-06-18 11:31:35.260 spid16s       AND r.codsoc = i.codsoc

    2008-06-18 11:31:35.260 spid16s       AND r.luna =  i.luna

    2008-06-18 11:31:35.260 spid16s       AND r.an = i.an

    2008-06-18 11:31:35.260 spid16s       AND r.valuta = i.valuta

    2008-06-18 11:31:35.260 spid16s       AND ISNULL(r.iddimensiune1,0) = ISNULL(i.iddimensiune1,0)

    2008-06-18 11:31:35.260 spid16s       AND ISNULL(r.iddimensiune2,0) = ISNULL(i.iddimensiune2,0)    

    2008-06-18 11:31:35.260 spid16s           frame procname=rcs.dbo.CNT_ArtContIntroducere line=122 stmtstart=5060 stmtend=5172 sqlhandle=0x03000700f2c0fe04d8855c01829600000100000000000000

    2008-06-18 11:31:35.260 spid16s      exec @lret = CNT18_RulajeDocCont  @nrcontare,@id, 0    

    2008-06-18 11:31:35.260 spid16s          inputbuf

    2008-06-18 11:31:35.260 spid16s      Proc [Database Id = 7 Object Id = 83804402]    

    2008-06-18 11:31:35.260 spid16s         process id=process3831c18 taskpriority=0 logused=2228 waitresource=PAGE: 7:1:1317504 waittime=2890 ownerId=2757801521 transactionname=user_transaction lasttranstarted=2008-06-18T11:31:31.480 XDES=0x420160370 lockMode=U schedulerid=5 kpid=4736 status=suspended spid=238 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-06-18T11:31:31.387 lastbatchcompleted=2008-06-18T11:31:31.387 clientapp=SOCRATE TREZORERIE hostname=TS3 hostpid=20144 loginname=BalteanuI isolationlevel=read committed (2) xactid=2757801521 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056

    2008-06-18 11:31:35.260 spid16s          executionStack

    2008-06-18 11:31:35.260 spid16s           frame procname=rcs.dbo.CNT18_RulajeDocCont line=399 stmtstart=28380 stmtend=29620 sqlhandle=0x030007002a4c5604732423010b9800000100000000000000

    2008-06-18 11:31:35.260 spid16s      UPDATE rulajevaluta

    2008-06-18 11:31:35.260 spid16s       SET rulajdebit = COALESCE (r.rulajdebit, 0) + COALESCE (i.rd, 0),

    2008-06-18 11:31:35.260 spid16s       rulajcredit = COALESCE (r.rulajcredit, 0) + COALESCE (i.rc, 0)

    2008-06-18 11:31:35.260 spid16s       FROM rulajevaluta r

    2008-06-18 11:31:35.260 spid16s       JOIN @rulajevaluta i ON i.cont = r.cont

    2008-06-18 11:31:35.260 spid16s       AND i.conta = r.conta

    2008-06-18 11:31:35.260 spid16s       AND i.conts = r.conts

    2008-06-18 11:31:35.260 spid16s       AND i.contb = r.contb

    2008-06-18 11:31:35.260 spid16s       AND i.contu = r.contu

    2008-06-18 11:31:35.260 spid16s       AND i.contv = r.contv

    2008-06-18 11:31:35.260 spid16s       AND r.codsoc = i.codsoc

    2008-06-18 11:31:35.260 spid16s       AND r.luna =  i.luna

    2008-06-18 11:31:35.260 spid16s       AND r.an = i.an

    2008-06-18 11:31:35.260 spid16s       AND r.valuta = i.valuta

    2008-06-18 11:31:35.260 spid16s       AND ISNULL(r.iddimensiune1,0) = ISNULL(i.iddimensiune1,0)

    2008-06-18 11:31:35.260 spid16s       AND ISNULL(r.iddimensiune2,0) = ISNULL(i.iddimensiune2,0)    

    2008-06-18 11:31:35.260 spid16s           frame procname=rcs.dbo.TRS_Transfer_Plati line=453 stmtstart=24698 stmtend=24808 sqlhandle=0x03000700cb55092a74943901c29900000100000000000000

    2008-06-18 11:31:35.260 spid16s      exec @lret = CNT18_RulajeDocCont @nrcontare,NULL,0,1    

    2008-06-18 11:31:35.260 spid16s          inputbuf

    2008-06-18 11:31:35.260 spid16s      Proc [Database Id = 7 Object Id = 705254859]    

    2008-06-18 11:31:35.260 spid16s        resource-list

    2008-06-18 11:31:35.260 spid16s         pagelock fileid=1 pageid=1317504 dbid=7 objectname=rcs.dbo.rulajevaluta id=lock42c25bd00 mode=UIX associatedObjectId=58630224609280

    2008-06-18 11:31:35.260 spid16s          owner-list

    2008-06-18 11:31:35.260 spid16s           owner id=process3826c58 mode=UIX

    2008-06-18 11:31:35.260 spid16s          waiter-list

    2008-06-18 11:31:35.260 spid16s           waiter id=process3831c18 mode=U requestType=wait

    2008-06-18 11:31:35.260 spid16s         pagelock fileid=1 pageid=825 dbid=7 objectname=rcs.dbo.rulajevaluta id=lock42c349900 mode=U associatedObjectId=58630224609280

    2008-06-18 11:31:35.260 spid16s          owner-list

    2008-06-18 11:31:35.260 spid16s           owner id=process3831c18 mode=U

    2008-06-18 11:31:35.260 spid16s          waiter-list

    2008-06-18 11:31:35.260 spid16s           waiter id=process3826c58 mode=U requestType=wait

    What’s about it? How to solve it?

    thanks in advance,

    Sorin

  26. bartduncan says:

    It’s actually not the same spid.  One of the process elements is "spid=86", while the other is "spid=238".  The "spid16s" on the left of each line is just the identifier of the background system thread that handled the deadlock; you can ignore it.  

    I recommend that you go through the steps listed in http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx.  Break down the -T1222 output so that you have a better understanding of what caused the deadlock, then follow the remaining steps in that post to see if there is a simplistic solution.  For example, see whether the UPDATE is doing an unnecessary table scan that could be prevented with a better index.  Check the BEGIN/COMMIT TRANs in stored procs CNT_ArtContIntroducere, TRS_Transfer_Plati, and CNT18_RulajeDocCont to make sure that you aren’t holding a transaction open any longer than is absolutely necessary.  

  27. Anonymous says:

    It seems that the same procedure was called from within several stored procedures. It’s about CNT18_RulajeDocCont . Should I set a transaction isolation level inside this procedure? In order to be called in a serialization fashion?

    Have a nice day,

    Sorin

  28. Anonymous says:

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

  29. Steve.Ash says:

    Bart-

    I hate doing this, but I think this is a deadlock trace that hasn’t been on here before (its not intra-query parallelism!).  I have two simple update queries that are contending on the same index but on different keys,   which doesn’t sound exotic, but I cannot think of a scenario that would result in this deadlock graph:

        deadlock-list

         deadlock victim=process3a12c58

          process-list

           process id=process3a12c58 waitresource=KEY: 8:72057594690666496 (0100dc8c8473) waittime=2953 lockMode=X kpid=4072 isolationlevel=read uncommitted (1)

            executionStack

             frame procname=adhoc line=1 stmtstart=144

        UPDATE CONTROL_LEVEL  SET control_amt = @P0, credit_amt = @P1, credit_cnt = @P2, debit_amt =@P3, debit_cnt=@P4, difference_amt=@P5 WHERE (ID = @P6)    

            inputbuf

        (@P0 bigint,@P1 bigint,@P2 int,@P3 bigint,@P4 int,@P5 bigint,@P6 bigint)UPDATE CONTROL_LEVEL  SET control_amt = @P0, credit_amt = @P1, credit_cnt = @P2, debit_amt =@P3, debit_cnt=@P4, difference_amt=@P5 WHERE (ID = @P6)                                                            

           process id=process42b2ef8 waitresource=KEY: 8:72057594690666496 (0100a18ba131) waittime=2968 lockMode=X kpid=2796 isolationlevel=read uncommitted (1)

            executionStack

             frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x02000000a00dbb2c60ffa236c31f45683a32d26f213ff8ba

        UPDATE CONTROL_LEVEL SET IQA_PENDING_CNT = IQA_PENDING_CNT – 12 WHERE ID = @P0    

             frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

        unknown    

            inputbuf

        (@P0 bigint)UPDATE CONTROL_LEVEL SET IQA_PENDING_CNT = IQA_PENDING_CNT – 12 WHERE ID = @P0            

          resource-list

           keylock hobtid=72057594690666496 dbid=8 objectname=TW.dbo.CONTROL_LEVEL indexname=PK__CONTROL_LEVEL__6A9FD071 id=lockeebe0200 mode=X associatedObjectId=72057594690666496

            owner-list

             owner id=process3a12c58 mode=X

            waiter-list

             waiter id=process42b2ef8 mode=X requestType=wait

           keylock hobtid=72057594690666496 dbid=8 objectname=TW.dbo.CONTROL_LEVEL indexname=PK__CONTROL_LEVEL__6A9FD071 id=lock3151ee400 mode=X associatedObjectId=72057594690666496

            owner-list

             owner id=process42b2ef8 mode=X

            waiter-list

             waiter id=process3a12c58 mode=X requestType=wait

    So as you see, the only own each other, which is a key X lock on a key in the PK clustered index.  Why this is wierd to me is that both statements update a single record in the table, using the primary key value as the index– so they shouldn’t be trying to lock each other.

    UNLESS… they are traversing the index "in the opposite order" (or via hash join) and lock each other, but I would’ve though that would result in an IX during traversal until the actual key was found, then an X.  In which case, they shouldn’t be trying to get X locks on each other.  The other thing I was thinking about was what happens in the event of a page split (or does that not affect locking?), but these are all numeric values that don’t expand…

    Bart- regardless if you are able to take time to reply I am really thankful for your insightful and informative blog posts!

    Steve Ash

    steve.ash@metavante.com

  30. bartduncan says:

    Steve, any chance that these processes are part of a multi-statement transaction that does more than one modification to the CONTROL_LEVEL table within the same transaction?

  31. Anonymous says:

    Bart-

    Yes this is probably the case, but these are different processes–  Wouldnt that imply different connections/spids and therefore different transactions?  And if they were in the same transaction why would they block–the transaction would already hold the lock, right?  

    I took some of the attributes out of the deadlock trace for space– I will look at the transaction id on the processes.

    Thanks so much for the feedback!  Any other thoughts?

    Steve

  32. Steve.Ash says:

    Bart-

    Yes this is probably the case, but these are different processes–  Wouldnt that imply different connections/spids and therefore different transactions?  And if they were in the same transaction why would they block–the transaction would already hold the lock, right?  

    I took some of the attributes out of the deadlock trace for space– I will look at the transaction id on the processes.

    Thanks so much for the feedback!  Any other thoughts?

    Steve

    (I wasnt logged in before, so sorry if I am double posting this response)

  33. bartduncan says:

    > Wouldnt that imply different connections/spids and therefore different transactions? 
    > And if they were in the same transaction why would they block–the transaction would already hold the lock, right?

    Yes, the different spids would have different transactions.  That’s exactly why they would block each other.  Your scenario is probably something like:

    1. spid A and spid B both start (separate) transactions
    2. spid A modifies row #1, acquiring and holding an X lock on this row
    3. spid B modifies row #2, acquiring and holding an X lock on this row
    4. spid A tries to modify row #2, but gets blocked because spid B still holds an X lock on this row
    5. spid B tries to modify row #1, but gets blocked because spid A still holds an X lock on this row

    In the deadlock graph, you see the two spids immediately following step #5.

  34. Steve.Ash says:

    Right- I am completely stupid. I was thinking that these two were updating the exact same row (from the application standpoint), but yea- these are probably updating different rows from previous statements in transactions (hits head for overlooking the obvious).  This is the easiest of all deadlock scenarios!  I am really sorry for wasting your time!

    I guess natural follow ups would be:

    1) Does the execution stack always only show the last statement or is it supposed to show all of the statements in the transaction?

    2) Is there any way to find out what key value (i.e. the actual PK value from the ID field) that these locks were guarding?  I.e. from the hash in the waitresource or from the "id=lockeebe0200" in the owner list?  Is there anything that can be inferred from the lockeebe0200 or it that just an identifier for the logical lock object in memory?

    Ahh- this makes me so mad that I posted such a stupid question!

    Thanks again,

    Steve

  35. bartduncan says:

    No worries :).  

    1) It only shows the last statements, the ones that closed the loop.  Right now, the server does not keep a record of which statement acquired which lock, so the info that would allow going "back in time" to see past statements that also acquired some of the locks isn’t available in memory.  Often, you can infer the statement by the lock types, especially if you have some knowledge of the app or can crack open the stored procs that were running (the most common begin trans/commit trans are within a single stored procedure).  If this fails, you have to fall back on a profiler trace, which requires waiting for a reoccurrence of the problem.

    2) Unfortunately not.  This is a (one-way) hash of the key value.  If you needed to see the statement parameter values, you’d have to use profiler here, too.  Luckily, it is rarely necessary to know the param values or specific rows in order to understand the deadlock.  

  36. Anonymous says:

    Hey Bart,

    I understand the scenarios in Part #1, 2 and 3 but I am failing to understand why the following scenario causes a deadlock.

    Suppose we have the following table:

    CREATE TABLE Widgets (

      WidgetID       int,

      WidgetNumber   int,

      WidgetValue    int,

      constraint pk_widgets primary key nonclustered ( widgetid )

    )

    CREATE UNIQUE INDEX IX_Widgets ON Widgets ( WidgetNumber )

    INSERT INTO

    Widgets ( WidgetID, WidgetNumber, WidgetValue )

    VALUES ( 1, 10, 100 )

    1. Process A and B start a transaction
    2. Process A updates the row in the Widgets table

    3. Process B attempts to update the same row and has to wait for Process A to finish

    4. Process A attempts to update the same row and causes a deadlock

    SQL server terminates Process B!

    Process B should continue to wait until the transaction is complete!

  37. Anonymous says:

    You mention the option of forcing one of the transactions to block early on the process, by doing something like:

    SELECT @x = COUNT(*) FROM t1 WITH (HOLDLOCK, UPDLOCK) WHERE c1 = @p1

    Would this in general:

    a) lock a single set of rows (i.e. the ones where C1=@p1) in the index cidx?

    b) effectively place a index wide lock?

    If it is a), then the issue I have is that the two processes are independent, (different users logged into different clients!) so the runtime values of @p1 for the two procedures are more than likely to be completely different – in fact the values apply to different columns in the table, so there is no relationship between the parameters for the procedures at all.

    In addition, the count(*) is likely to be expensive in some cases – what if there are millions of rows?.  If the locking is an index wide lock, would this lock still be held, even if the value for @p1 was chosen such that there we no rows at all where c1=@p1?

    These may be silly questions !

    This is such black art stuff.

  38. cyboc says:

    Bart, thank you for this excellent primer.

    I am repeatedly getting a deadlock that I'm not sure how to solve, even after reading your tips. I think I understand what is causing the deadlock but I'm just clueless as to the best way to solve it. Let me explain:

    I have a table that has a TEXT column as well as a couple of CHAR columns. The "text in row" table option is currently off, which means, if I understand correctly, that the TEXT column's data will be stored in a separate page than the rest of the data for a given row. I realize that the TEXT datatype is supposed to be phased out in favor of VARCHAR(MAX) but unfortunately, I cannot change the schema for the table in question because it is not under our control (it was created by an application we purchased).

    Looking at the deadlock graph, we have one process that does a SELECT on that table, selecting all columns, including the TEXT column. The other process in the graph does an INSERT on that table, inserting a single row, including the TEXT column.

    The SELECT process owns a page lock (mode S) on one page and is requesting a page lock (mode S) on a second page. On the other hand, the INSERT process owns a page lock (mode IX) on the second page and is requesting a page lock (mode IX) on the first page. Clearly, this is a deadlock.

    I presume that one of the pages holds the data for the TEXT column and the other page holds the data for the other columns. Is this a correct assumption?

    The SELECT process seems to always be the loser because it is the least expensive (i.e. it has Log Used: 0). Our current "fix" is to detect the deadlock in the SELECT process and try it again when it occurs. Unfortunately, as we add more users to the system, the deadlocks seem to be happening more frequently and the system also seems to be less responsive.

    To improve this situation, I'm considering the following options:

    1) Rewrite the SELECT process so it does NOT select the TEXT column. This is for sort of a "ticker tape" use-case and the users might be able to live without seeing the data in the TEXT column. If they want to see it for a particular row, perhaps they can "drill down". But I don't think our UI developer likes this option.

    2) Use the "WITH (NOLOCK)" option in the SELECT process. However, what happens when you set this option and the INSERT process has only written one page and not the other when the SELECT goes to read the same record? Will the SELECT only see part of the record (e.g. either the TEXT column or the non-TEXT columns but not both)??? This worries me…

    3) Set the isolation level of the SELECT process to read uncommitted. But I have the same worry as in option 2.

    4) Set the "text in row" table option to ON, so that all of a row's data is (hopefully) stored on the same page. However, if the vendor of the application ever updates the schema for that table, I guess I'll have to remember to reapply that table option?

    Any recommendations or wisdom would be appreciated.

  39. bartduncan says:

    @cyboc –  

    > I presume that one of the pages holds the data for the TEXT column and the other page holds the data for the other columns. Is this a correct assumption?

    Maybe.  Send me the -T1222 output (you can use the blog contact form, or I'll send you an offline msg w/my email address) and there might be some clues to confirm this.  Do the pagelock resources in the -T1222 output identify an index ID for each page?  Text/image data is always on index ID 255.  Alternatively, there should be an associatedObjectId attribute.  This is a partition ID, and you can figure out which index it belongs to by querying sys.partitions as described in blogs.msdn.com/…/deadlock-troubleshooting_2c00_-part-1.aspx.  

    The fact that the SELECT is acquiring non-intent Shared page locks indicates that it is probably doing a full table or index scan.  That usually points to a lack of indexes on the table that would support a more efficient plan. (Either that, or the SELECT returns a large % of the data in the table.)  My recommendation would generally be to eliminate all unnecessary table/index scans before doing anything else, but it sounds like you may be reluctant to add any indexes because you don't "own" the schema…  

    > 1) Rewrite the SELECT process so it does NOT select the TEXT column.

    That might work, assuming that the text data is typically small enough to fit on page, and assuming that the two pages involved in the deadlock are a text page and a non-text clustered index page.  

    > 2) Use the "WITH (NOLOCK)" option in the SELECT process. …

    > what happens when you set this option and the INSERT process has only written

    > one page and not the other when the SELECT goes to read the same record?

    > Will the SELECT only see part of the record (e.g. either the TEXT column or the

    > non-TEXT columns but not both)??? This worries me…

    I'm not sure if the scenario you describe is possible, but NOLOCK / dirty reads can definitely cause odd behavior.  (I have never seen a case where an inconsistent version of a single row was returned for a table that used text/image… the typical symptom is the query simply failing with a strange error.)  

    > 4) Set the "text in row" table option to ON

    That might work, again assuming that the text data is typically small enough to fit on page, and assuming that the two pages involved in the deadlock are a text page and a non-text clustered index page.  

    Have you considered enabling the read committed snapshot database option?  That would probably address this along with most other blocking problems where one of the participants is only doing reads.  

  40. I posted a comment earlier, but am not sure it got through as I was not logged in.  In a nutshell, we use cursors, the "Fetch" cursor is being marked as the deadlock victim when we are updating the same table, the same result set, sometimes. Currently, removing the cursors would be an unfeasible solution.  The SQL uses a distinct index and selects all columns for the given table.  Is there any good way to find the reason for the deadlock in this kind of scenario?  Using the snapshot feature seems kind of dangerous in our application, and the indexes seem OK.  None are clustered, by the way.  Don't know if that matters or not.  Any ideas?  If you need more information, and did not get my other comment post, then tell me.  Was doing it from memory, so I might have forgotten some details.  Thanks.

  41. bartduncan says:

    tbaldarelli – A dynamic cursor essentially runs a slightly modified very of your query anew for each fetch. Other cursors run the query once, pausing the scan while the code processes the row in between each fetch call.  In either case, it's mostly the same as other deadlocks. The data collection process is the same, and the same deadlock avoidance techniques generally apply. I would capture the -T1211 output and decode it as discussed in Part 1 (blogs.msdn.com/…/747119.aspx) to better understand why the two spids are deadlocked. Then try index tuning, which is always worth a shot even if the existing indexes seem reasonable. If all else fails, you'll need to apply one of the deadlock avoidance techniques mentioned above. If you post the -T1211 output I can try to help you with the first step.

  42. Anonymous says:

    Thanks a lot for the clear and concise explanation….. Most sites explain the classic scenario involving out of order updates on different tables thru different update statements… What you have explained here is a more practical and subtle variation of the same scenario…

  43. Anonymous says:

    Here is one for you. What are these statements deadlocking. They are run in sequence in the same stored procedure.

    <EVENT_INSTANCE><EventType>DEADLOCK_GRAPH</EventType><PostTime>2012-05-15T13:55:39.547</PostTime><SPID>18</SPID><TextData><deadlock-list><deadlock victim="process1d8d8b708"><process-list><process id="process1d8d8b708" taskpriority="0" logused="352" waitresource="KEY: 7:72057594173259776 (5481b0210af3)" waittime="1871" ownerId="193061341" transactionname="user_transaction" lasttranstarted="2012-05-15T13:55:33.533" XDES="0x26cabac60" lockMode="U" schedulerid="2" kpid="900" status="suspended" spid="134" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-05-15T13:55:36.533" lastbatchcompleted="2012-05-15T13:55:33.533" hostpid="1828" loginname="KCUSUSTCAP200" isolationlevel="read committed (2)" xactid="193061341" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame procname="promax_development.dbo.sp_GetForecastDaemonJobs" line="890" stmtstart="90500" stmtend="90996" sqlhandle="0x030007003fb9e045c76a8a0024a000000100000000000000">

    DELETE forecastdaemonjobs

     FROM forecastdaemonjobs

        INNER JOIN (

           SELECT  fdj_rowid AS fdjrowid

           FROM    #forecastdaemonjobs

           ) #deljob

           ON fdj_rowid = fdjrowid

     — delete any account sku duplicates     </frame></executionStack><inputbuf>

    Proc [Database Id = 7 Object Id = 1172355391]    </inputbuf></process><process id="process5e13b88" taskpriority="0" logused="728" waitresource="KEY: 7:72057594040090624 (29476ae70d8c)" waittime="1872" ownerId="193061344" transactionname="user_transaction" lasttranstarted="2012-05-15T13:55:33.533" XDES="0x3f5b483b0" lockMode="U" schedulerid="2" kpid="1280" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-05-15T13:55:36.537" lastbatchcompleted="2012-05-15T13:55:33.537" hostpid="14472" loginname="KCUSUSTCAP200" isolationlevel="read committed (2)" xactid="193061344" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame procname="promax_development.dbo.sp_GetForecastDaemonJobs" line="900" stmtstart="90998" stmtend="92752" sqlhandle="0x030007003fb9e045c76a8a0024a000000100000000000000">

    DELETE FROM forecastdaemonjobslist

       FROM forecastdaemonjobslist INNER JOIN (

           SELECT  fdjl_skurowid AS fdjlskurowid, fdjl_skupubid AS tmpskupubid,

             fdjl_acrowid AS fdjlacrowid, fdjl_acpubid AS fdjlacpubid, min(fdjl_rowid) as rowid

           FROM    forecastdaemonjobslist

           GROUP BY  fdjl_skurowid, fdjl_skupubid, fdjl_acrowid, fdjl_acpubid

           ) #deljob

           ON fdjl_skurowid = fdjlskurowid AND

              fdjl_skupubid = tmpskupubid AND

             fdjl_acrowid = fdjlacrowid AND

             fdjl_acpubid = fdjlacpubid AND

             fdjl_rowid <> rowid

     — optionally delete all account/sku combinations that are not present in accountskudetails

     — unless they are on a promotion.  Terms are not being considered by design since if there

     — is no promotion for the account/sku then calculating the forecast is not required.     </frame></executionStack><inputbuf>

    Proc [Database Id = 7 Object Id = 1172355391]    </inputbuf></process></process-list><resource-list><keylock hobtid="72057594173259776" dbid="7" objectname="promax_development.dbo.forecastdaemonjobs" indexname="PK__forecast__E6B183EA7B5B524B" id="lock3452c0e00" mode="X" associatedObjectId="72057594173259776"><owner-list><owner id="process5e13b88" mode="X"/></owner-list><waiter-list><waiter id="process1d8d8b708" mode="U" requestType="wait"/></waiter-list></keylock><keylock hobtid="72057594040090624" dbid="7" objectname="promax_development.dbo.forecastdaemonjobslist" indexname="PK__forecast__0FE0553B02084FDA" id="lock3db06ed80" mode="X" associatedObjectId="72057594040090624"><owner-list><owner id="process1d8d8b708" mode="X"/></owner-list><waiter-list><waiter id="process5e13b88" mode="U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list></TextData><TransactionID/><LoginName>sa</LoginName><StartTime>2012-05-15T13:55:39.547</StartTime><ServerName>USTWAS80</ServerName><LoginSid>AQ==</LoginSid><EventSequence>3658230</EventSequence><IsSystem>1</IsSystem><SessionLoginName/></EVENT_INSTANCE>

  44. Anonymous says:

    Hello Bart,

    Thank you for your post.

    I have few questions for you and would like your to comment on my thoughts.

    1. do you see situations where replacing cl. index on non-cl. index reduces chances of deadlock.
    2. please comment begin tran / comit effect on probability of deadlock.

    3. in most recent case when delete deadlocked with select, I replaced cl. index for non.cl

    and added 1 more non-cl index to cover select stm.

    Now delete has lock on table object (not sure if it's table or page lock) and index seek

    on non-cl. index; select uses index seek.

    when before it was:

    delete: cl. index seek,cl.index delete

    select: non-cl. index seek, cl. index scan.

    I understand that during delete, all indexes are going to be excl. locked.

    But I feel that new situation has less chances for interferience.

    Actual deadlock could not be re-produced or tested.

    Please comment.

    Regarding #1:

    I had deadlock situation with update vs. another update which I could reproduce. After cl. index replacement for nonclust. version

    , deadlock could not be reproduced.

    I could add 1 more index to change index scan to index seek which could reduce footprint and improve

    efficency. After numerious tests, this index addition seemed irrelevant to deadlock situation.

    Regarding #2.

    I review coding where proc 1 starts begin tran block, calls proc 2, then proc 2 calls proc 3 and

    that's where deadlock happens. In most situations i can re-produce deadlock when begin tran

    is not envolved.

    I usually can reproduce deadlocks. But even after I fix indexes for these statements

    , I still see deadlocks even though more rearly.

    Which makes me to conclude next:

    Deadlocks coming from begin tran logic may have 2 reasons for it:

    1. poor index structure for deadlocked statements.

    2. additional locks caused by begin tran logic.

  45. Anonymous says:

    This is another way to avoid the deadlock:

    CREATE PROC p1 @p1 int AS

      declare @c1 int;

      SELECT @c1 = c1 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

      SELECT c2, c3 FROM t1 WHERE c1 = @c1

    GO

    I was surprised that this one worked too (running the reproduction scripts in step 2). Why? Does SQL Server scopes the shared locks on the subqueries?

    CREATE PROC p1 @p1 int AS

      select c2, c3 from t1 where c1 in (select c1 from t1 where c2 BETWEEN @p1 AND @p1+1)

    GO