Deadlock Troubleshooting, Part 3

Deadlock Troubleshooting, Part 3

Rate This
  • Comments 44

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 = c2-1 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.  

Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post
  • 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.
  • 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
  • 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.

  • 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

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

  • 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

  • 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

  • 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

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

  • 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

  • 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

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

  • 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

  • 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

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

Page 1 of 3 (44 items) 123