Deadlock Troubleshooting, Part 3

re: Deadlock Troubleshooting, Part 3

Martin Ritchie — Mon, 25 Sep 2006 22:30:08 GMT

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.

re: Deadlock Troubleshooting, Part 3

bartd — Mon, 02 Oct 2006 01:15:54 GMT

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

re: Deadlock Troubleshooting, Part 3

bartd — Thu, 19 Oct 2006 23:14:19 GMT

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.

re: Deadlock Troubleshooting, Part 3

rogerlin — Fri, 20 Oct 2006 18:06:46 GMT

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

re: Deadlock Troubleshooting, Part 3

rogerlin — Fri, 20 Oct 2006 19:03:53 GMT

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!

re: Deadlock Troubleshooting, Part 3

bartd — Fri, 20 Oct 2006 21:54:03 GMT

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

re: Deadlock Troubleshooting, Part 3

rogerlin — Sat, 21 Oct 2006 02:00:09 GMT

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

re: Deadlock Troubleshooting, Part 3

bartd — Sat, 21 Oct 2006 02:42:36 GMT

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

re: Deadlock Troubleshooting, Part 3

elad — Sun, 14 Jan 2007 20:56:08 GMT

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

re: Deadlock Troubleshooting, Part 3

Rogerlin — Tue, 27 Feb 2007 23:37:43 GMT

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

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

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

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

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 executionStack

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

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 executionStack

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

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

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

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

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

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 executionStack

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

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

re: Deadlock Troubleshooting, Part 3

bartd — Wed, 28 Feb 2007 15:51:10 GMT

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

re: Deadlock Troubleshooting, Part 3

bartd — Wed, 28 Feb 2007 15:57:44 GMT

> 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

re: Deadlock Troubleshooting, Part 3

Rogerlin — Mon, 23 Apr 2007 19:23:22 GMT

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