Deadlock Troubleshooting, Part 3

Artykuł
09/25/2006

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

CREATE PROC p2 @p1 int AS

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

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

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

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

What caused this deadlock?

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

Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.

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

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

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

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

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

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

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

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

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

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

Now let’s look at the UPDATE:

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

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

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

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

|--Top(ROWCOUNT est 0)

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

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

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

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

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

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

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

How did DTA’s new index avoid the deadlock?

Here’s an index that will prevent this deadlock:

CREATE INDEX idx2 ON t1 (c2, c3)

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

What other solutions are available?

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

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

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

Comments

Anonymous
September 25, 2006
The comment has been removed
Anonymous
October 01, 2006
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
Anonymous
October 19, 2006
The comment has been removed
Anonymous
October 20, 2006
The comment has been removed
Anonymous
October 20, 2006
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!
Anonymous
October 20, 2006
The comment has been removed
Anonymous
October 20, 2006
The comment has been removed
Anonymous
October 20, 2006
The comment has been removed
Anonymous
January 14, 2007
wouldn't a simple holdlok hint on the select query solve the problem?
Anonymous
February 27, 2007
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-list2007-02-26 11:37:09.93 spid19s deadlock victim=process8ccf282007-02-26 11:37:09.93 spid19s process-list2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000000dad81d584fea62bf8da39731a2cb796af12a932007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000039ef242194b27bba37bd021a11bbe604fa6bf6c72007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe4622007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000000dad81d584fea62bf8da39731a2cb796af12a932007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe4622007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe4622007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000000dad81d584fea62bf8da39731a2cb796af12a932007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe4622007-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 inputbuf2007-02-26 11:37:09.93 spid19s resource-list2007-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=720575940692541442007-02-26 11:37:09.93 spid19s owner-list2007-02-26 11:37:09.93 spid19s owner id=processfd5f18 mode=U2007-02-26 11:37:09.93 spid19s waiter-list2007-02-26 11:37:09.93 spid19s waiter id=processae1798 mode=U requestType=wait2007-02-26 11:37:09.93 spid19s exchangeEvent id=portaa6cae8 nodeId=42007-02-26 11:37:09.93 spid19s owner-list2007-02-26 11:37:09.93 spid19s owner event=e_waitNone type=producer id=processae17982007-02-26 11:37:09.93 spid19s waiter-list2007-02-26 11:37:09.93 spid19s waiter event=e_waitPortOpen type=producer id=processfd56a82007-02-26 11:37:09.93 spid19s waiter event=e_waitPortOpen type=producer id=process6d9b582007-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=720575940692541442007-02-26 11:37:09.93 spid19s owner-list2007-02-26 11:37:09.93 spid19s owner id=processfd56a8 mode=U2007-02-26 11:37:09.93 spid19s waiter-list2007-02-26 11:37:09.93 spid19s waiter id=process8ccf28 mode=U requestType=wait2007-02-26 11:37:09.93 spid19s waiter id=processb75108 mode=U requestType=wait2007-02-26 11:37:09.93 spid19s exchangeEvent id=portaa6c3e0 nodeId=42007-02-26 11:37:09.93 spid19s owner-list2007-02-26 11:37:09.93 spid19s owner event=e_waitNone type=producer id=processb751082007-02-26 11:37:09.93 spid19s waiter-list2007-02-26 11:37:09.93 spid19s waiter event=e_waitPortOpen type=producer id=processfd5f182007-02-26 11:37:09.93 spid19s waiter event=e_waitPortOpen type=producer id=process8cd2e82007-02-26 11:37:09.93 spid19s waiter event=e_waitPortOpen type=producer id=processc093d82007-02-26 11:37:09.93 spid19s deadlock-list2007-02-26 11:37:09.93 spid19s deadlock victim=processb751082007-02-26 11:37:09.93 spid19s process-list2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000000dad81d584fea62bf8da39731a2cb796af12a932007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000000dad81d584fea62bf8da39731a2cb796af12a932007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x020000005942e21eaa29e18196f1e7e814b098ad12ebe4622007-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 inputbuf2007-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=1280562007-02-26 11:37:09.93 spid19s executionStack2007-02-26 11:37:09.93 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000b8bf3b07f75955d960ed6d3b1bd837c840c694012007-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=0x0200000000dad81d584fea62bf8da39731a2cb796af12a932007-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 inputbuf2007-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 lastbatchcomple
Anonymous
February 27, 2007
The comment has been removed
Anonymous
February 27, 2007
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
Anonymous
April 23, 2007
Hi Bart,Does this show the parallel threads deadlocked amount themselves?2007-04-21 09:54:02.410 spid5s deadlock-list2007-04-21 09:54:02.410 spid5s deadlock victim=process8cd0182007-04-21 09:54:02.410 spid5s process-list2007-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=1280562007-04-21 09:54:02.410 spid5s executionStack2007-04-21 09:54:02.410 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.410 spid5s unknown 2007-04-21 09:54:02.410 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.410 spid5s unknown 2007-04-21 09:54:02.410 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.410 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-04-21 09:54:02.430 spid5s process id=process8cdc48 waittime=31 schedulerid=2 kpid=02007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-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=1280562007-04-21 09:54:02.430 spid5s executionStack2007-04-21 09:54:02.430 spid5s frame procname=unknown line=8 stmtstart=208 stmtend=1746 sqlhandle=0x03000c00f721824bd27de5007598000001000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s frame procname=unknown line=1 sqlhandle=0x01000c00d4094b38209447410000000000000000000000002007-04-21 09:54:02.430 spid5s unknown 2007-04-21 09:54:02.430 spid5s inputbuf2007-04-21 09:54:02.430 spid5s resource-list2007-04-21 09:54:02.430 spid5s threadpool id=scheduler9d00402007-04-21 09:54:02.430 spid5s owner-list2007-04-21 09:54:02.430 spid5s owner id=process8cde282007-04-21 09:54:02.430 spid5s owner id=process8cda682007-04-21 09:54:02.430 spid5s owner id=process8cd0182007-04-21 09:54:02.430 spid5s owner id=process8cc7a82007-04-21 09:54:02.430 spid5s waiter-list2007-04-21 09:54:02.430 spid5s waiter id=process8cdc482007-04-21 09:54:02.430 spid5s exchangeEvent id=portaa6c7a0 nodeId=222007-04-21 09:54:02.430 spid5s owner-list2007-04-21 09:54:02.430 spid5s owner event=pending id=process8cdc482007-04-21 09:54:02.430 spid5s waiter-list2007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=processf419782007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process6d8c582007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=processb74d482007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process8cc7a82007-04-21 09:54:02.430 spid5s exchangeEvent id=portaa6d358 nodeId=202007-04-21 09:54:02.430 spid5s owner-list2007-04-21 09:54:02.430 spid5s owner event=e_waitNone type=producer id=processf419782007-04-21 09:54:02.430 spid5s owner event=pending id=process8cdc482007-04-21 09:54:02.430 spid5s waiter-list2007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=processf406b82007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process6d91082007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=processb744d82007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process8cda682007-04-21 09:54:02.430 spid5s exchangeEvent id=portaa6c728 nodeId=182007-04-21 09:54:02.430 spid5s owner-list2007-04-21 09:54:02.430 spid5s owner event=e_waitNone type=producer id=processf406b82007-04-21 09:54:02.430 spid5s owner event=pending id=process8cdc482007-04-21 09:54:02.430 spid5s waiter-list2007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process6d86b82007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=processb74a782007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process8cde282007-04-21 09:54:02.430 spid5s exchangeEvent id=portaa6cf98 nodeId=142007-04-21 09:54:02.430 spid5s owner-list2007-04-21 09:54:02.430 spid5s owner event=e_waitNone type=producer id=process6d86b82007-04-21 09:54:02.430 spid5s owner event=pending id=process8cdc482007-04-21 09:54:02.430 spid5s waiter-list2007-04-21 09:54:02.430 spid5s waiter event=e_waitPortOpen type=consumer id=process8cd018Notice they are all from the same job step with the same statement start and end position.Thanks,Roger
Anonymous
April 23, 2007
The comment has been removed
Anonymous
April 06, 2008
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-listdeadlock 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown inputbufProc [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=0x020000007ec1c918fd8bbdbf7075d4aa60c9b18a55310c62unknown frame procname=unknown line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000unknown frame procname=unknown line=546 stmtstart=51170 stmtend=51244 sqlhandle=0x030005002bcbd65cd75a2001739a00000100000000000000unknown 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=process383ada8NULLVictim Resource Owner:ResType:ExchangeId Stype:'AND' SPID:51 BatchID:0 ECID:13 TaskProxy:(0x00000000809BF8F0) Value:0x2a7c0460 Cost:(0/10009)
Anonymous
April 07, 2008
The comment has been removed
Anonymous
May 22, 2008
Hi Bart,Sorry, I didn't have time to read all the comments(maybe this situation is mentioned in them) but one of the most "baffling" deadlocks is when you have a select being the cause of the deadlock: you have an update and a select on different rows in the table but the select generates a table scan . After aquring several shared locks SQL Server 2000 decides that an table lock escalation is necessary and voila, you have the deadlock. I wonder if this happens in 2005 also.
Anonymous
May 22, 2008
That sort of deadlock can definitely happen in SQL 2005 (or 2008). It's common for people to assume that a query won't touch any rows except those that it will end up modifying or selecting, but it is often the case that the indexes on the table don't allow for such an efficient query plan. Index tuning may be necessary to prevent scans or limit the number of locks that a query needs to acquire in some other way. (As an aside, lock escalation never blocks; if SQL can't escalate to a table lock to because someone else holds an incompatible lock, it will continue acquiring locks at the row or page level. But you can still run into a SELECT vs. UPDATE deadlock. In fact, that's the scenario in the deadlock dissected in this post.)
Anonymous
May 22, 2008
The T1204 message was pretty specific, something like escalating to table lock(sorry, my memory doesn't help me when it comes to generated messages:-). The update was doing the rest by holding a lock. Indexes didn't help because the table scan was generated by something like str1 = UPPER(str2), so the optimizer wouldn't use any indexes. So what do you mean by lock escalation never blocks? Please elaborate.
Anonymous
May 22, 2008
The comment has been removed
Anonymous
May 28, 2008
Hi Bart,Now that I got your attention(sorry for the delay between posts): the version based isolation level should get rid of the deadlocks mentioned above. WHat would be the dangers of completely switching to this isolation level?
Anonymous
May 28, 2008
Yes, snapshot isolation/read versioning is one of the alternate solutions identified above. The main danger is that it allows you to read a stale image of data that is currently being modified by someone else. See the discussion in the Definition section of http://en.wikipedia.org/wiki/Snapshot_isolation for some more info. Whether this risk matters is dependent on the application.
Anonymous
May 28, 2008
I read somewhere that the snapshot will put pressure on tempdb inducing performance problems. Personally I doubt that, but did you guys have more data about the impact on performance?
Anonymous
May 28, 2008
The comment has been removed
Anonymous
June 18, 2008
I have the following deadlock which was caused through the same spid:2008-06-18 11:31:35.260 spid16s deadlock-list2008-06-18 11:31:35.260 spid16s deadlock victim=process3831c182008-06-18 11:31:35.260 spid16s process-list2008-06-18 11:31:35.260 spid16s process id=process3826c58 taskpriority=0 logused=4244 waitresource=PAGE: 7:1:825 waittime=2359 ownerId=2757801102 transactionname=implicit_transaction lasttranstarted=2008-06-18T11:31:30.933 XDES=0x4394e0370 lockMode=U schedulerid=4 kpid=2576 status=suspended spid=86 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2008-06-18T11:31:32.887 lastbatchcompleted=2008-06-18T11:31:32.887 clientapp=SOCRATE CONTABILITATE hostname=TS1 hostpid=9472 loginname=BotaN isolationlevel=read committed (2) xactid=2757801102 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=1280582008-06-18 11:31:35.260 spid16s executionStack2008-06-18 11:31:35.260 spid16s frame procname=rcs.dbo.CNT18_RulajeDocCont line=399 stmtstart=28380 stmtend=29620 sqlhandle=0x030007002a4c5604732423010b98000001000000000000002008-06-18 11:31:35.260 spid16s UPDATE rulajevaluta2008-06-18 11:31:35.260 spid16s SET rulajdebit = COALESCE (r.rulajdebit, 0) + COALESCE (i.rd, 0),2008-06-18 11:31:35.260 spid16s rulajcredit = COALESCE (r.rulajcredit, 0) + COALESCE (i.rc, 0)2008-06-18 11:31:35.260 spid16s FROM rulajevaluta r2008-06-18 11:31:35.260 spid16s JOIN @rulajevaluta i ON i.cont = r.cont2008-06-18 11:31:35.260 spid16s AND i.conta = r.conta2008-06-18 11:31:35.260 spid16s AND i.conts = r.conts2008-06-18 11:31:35.260 spid16s AND i.contb = r.contb2008-06-18 11:31:35.260 spid16s AND i.contu = r.contu2008-06-18 11:31:35.260 spid16s AND i.contv = r.contv2008-06-18 11:31:35.260 spid16s AND r.codsoc = i.codsoc2008-06-18 11:31:35.260 spid16s AND r.luna = i.luna2008-06-18 11:31:35.260 spid16s AND r.an = i.an2008-06-18 11:31:35.260 spid16s AND r.valuta = i.valuta2008-06-18 11:31:35.260 spid16s AND ISNULL(r.iddimensiune1,0) = ISNULL(i.iddimensiune1,0)2008-06-18 11:31:35.260 spid16s AND ISNULL(r.iddimensiune2,0) = ISNULL(i.iddimensiune2,0) 2008-06-18 11:31:35.260 spid16s frame procname=rcs.dbo.CNT_ArtContIntroducere line=122 stmtstart=5060 stmtend=5172 sqlhandle=0x03000700f2c0fe04d8855c018296000001000000000000002008-06-18 11:31:35.260 spid16s exec @lret = CNT18_RulajeDocCont @nrcontare,@id, 0 2008-06-18 11:31:35.260 spid16s inputbuf2008-06-18 11:31:35.260 spid16s Proc [Database Id = 7 Object Id = 83804402] 2008-06-18 11:31:35.260 spid16s process id=process3831c18 taskpriority=0 logused=2228 waitresource=PAGE: 7:1:1317504 waittime=2890 ownerId=2757801521 transactionname=user_transaction lasttranstarted=2008-06-18T11:31:31.480 XDES=0x420160370 lockMode=U schedulerid=5 kpid=4736 status=suspended spid=238 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-06-18T11:31:31.387 lastbatchcompleted=2008-06-18T11:31:31.387 clientapp=SOCRATE TREZORERIE hostname=TS3 hostpid=20144 loginname=BalteanuI isolationlevel=read committed (2) xactid=2757801521 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=1280562008-06-18 11:31:35.260 spid16s executionStack2008-06-18 11:31:35.260 spid16s frame procname=rcs.dbo.CNT18_RulajeDocCont line=399 stmtstart=28380 stmtend=29620 sqlhandle=0x030007002a4c5604732423010b98000001000000000000002008-06-18 11:31:35.260 spid16s UPDATE rulajevaluta2008-06-18 11:31:35.260 spid16s SET rulajdebit = COALESCE (r.rulajdebit, 0) + COALESCE (i.rd, 0),2008-06-18 11:31:35.260 spid16s rulajcredit = COALESCE (r.rulajcredit, 0) + COALESCE (i.rc, 0)2008-06-18 11:31:35.260 spid16s FROM rulajevaluta r2008-06-18 11:31:35.260 spid16s JOIN @rulajevaluta i ON i.cont = r.cont2008-06-18 11:31:35.260 spid16s AND i.conta = r.conta2008-06-18 11:31:35.260 spid16s AND i.conts = r.conts2008-06-18 11:31:35.260 spid16s AND i.contb = r.contb2008-06-18 11:31:35.260 spid16s AND i.contu = r.contu2008-06-18 11:31:35.260 spid16s AND i.contv = r.contv2008-06-18 11:31:35.260 spid16s AND r.codsoc = i.codsoc2008-06-18 11:31:35.260 spid16s AND r.luna = i.luna2008-06-18 11:31:35.260 spid16s AND r.an = i.an2008-06-18 11:31:35.260 spid16s AND r.valuta = i.valuta2008-06-18 11:31:35.260 spid16s AND ISNULL(r.iddimensiune1,0) = ISNULL(i.iddimensiune1,0)2008-06-18 11:31:35.260 spid16s AND ISNULL(r.iddimensiune2,0) = ISNULL(i.iddimensiune2,0) 2008-06-18 11:31:35.260 spid16s frame procname=rcs.dbo.TRS_Transfer_Plati line=453 stmtstart=24698 stmtend=24808 sqlhandle=0x03000700cb55092a74943901c299000001000000000000002008-06-18 11:31:35.260 spid16s exec @lret = CNT18_RulajeDocCont @nrcontare,NULL,0,1 2008-06-18 11:31:35.260 spid16s inputbuf2008-06-18 11:31:35.260 spid16s Proc [Database Id = 7 Object Id = 705254859] 2008-06-18 11:31:35.260 spid16s resource-list2008-06-18 11:31:35.260 spid16s pagelock fileid=1 pageid=1317504 dbid=7 objectname=rcs.dbo.rulajevaluta id=lock42c25bd00 mode=UIX associatedObjectId=586302246092802008-06-18 11:31:35.260 spid16s owner-list2008-06-18 11:31:35.260 spid16s owner id=process3826c58 mode=UIX2008-06-18 11:31:35.260 spid16s waiter-list2008-06-18 11:31:35.260 spid16s waiter id=process3831c18 mode=U requestType=wait2008-06-18 11:31:35.260 spid16s pagelock fileid=1 pageid=825 dbid=7 objectname=rcs.dbo.rulajevaluta id=lock42c349900 mode=U associatedObjectId=586302246092802008-06-18 11:31:35.260 spid16s owner-list2008-06-18 11:31:35.260 spid16s owner id=process3831c18 mode=U2008-06-18 11:31:35.260 spid16s waiter-list2008-06-18 11:31:35.260 spid16s waiter id=process3826c58 mode=U requestType=waitWhat's about it? How to solve it?thanks in advance,Sorin
Anonymous
June 18, 2008
The comment has been removed
Anonymous
June 18, 2008
It seems that the same procedure was called from within several stored procedures. It's about CNT18_RulajeDocCont . Should I set a transaction isolation level inside this procedure? In order to be called in a serialization fashion?Have a nice day,Sorin
Anonymous
September 24, 2008
I've received a couple of questions in email and in comments about deadlocks involving mysterious-sounding
Anonymous
November 06, 2008
The comment has been removed
Anonymous
November 06, 2008
Steve, any chance that these processes are part of a multi-statement transaction that does more than one modification to the CONTROL_LEVEL table within the same transaction?
Anonymous
November 06, 2008
Bart- Yes this is probably the case, but these are different processes-- Wouldnt that imply different connections/spids and therefore different transactions? And if they were in the same transaction why would they block--the transaction would already hold the lock, right? I took some of the attributes out of the deadlock trace for space-- I will look at the transaction id on the processes. Thanks so much for the feedback! Any other thoughts? Steve
Anonymous
November 06, 2008
Bart-Yes this is probably the case, but these are different processes-- Wouldnt that imply different connections/spids and therefore different transactions? And if they were in the same transaction why would they block--the transaction would already hold the lock, right? I took some of the attributes out of the deadlock trace for space-- I will look at the transaction id on the processes.Thanks so much for the feedback! Any other thoughts?Steve(I wasnt logged in before, so sorry if I am double posting this response)
Anonymous
November 07, 2008
> Wouldnt that imply different connections/spids and therefore different transactions?
> And if they were in the same transaction why would they block--the transaction would already hold the lock, right? Yes, the different spids would have different transactions. That's exactly why they would block each other. Your scenario is probably something like:

spid A and spid B both start (separate) transactions
2. spid A modifies row #1, acquiring and holding an X lock on this row
3. spid B modifies row #2, acquiring and holding an X lock on this row
4. spid A tries to modify row #2, but gets blocked because spid B still holds an X lock on this row
5. spid B tries to modify row #1, but gets blocked because spid A still holds an X lock on this row In the deadlock graph, you see the two spids immediately following step #5.

Anonymous
November 07, 2008
The comment has been removed
Anonymous
November 07, 2008
The comment has been removed
Anonymous
May 03, 2009
Hey Bart,I understand the scenarios in Part #1, 2 and 3 but I am failing to understand why the following scenario causes a deadlock.Suppose we have the following table:CREATE TABLE Widgets ( WidgetID int, WidgetNumber int, WidgetValue int, constraint pk_widgets primary key nonclustered ( widgetid ))CREATE UNIQUE INDEX IX_Widgets ON Widgets ( WidgetNumber )INSERT INTOWidgets ( WidgetID, WidgetNumber, WidgetValue )VALUES ( 1, 10, 100 )Process A and B start a transactionProcess A updates the row in the Widgets table Process B attempts to update the same row and has to wait for Process A to finish Process A attempts to update the same row and causes a deadlock SQL server terminates Process B!Process B should continue to wait until the transaction is complete!
Anonymous
June 02, 2009
You mention the option of forcing one of the transactions to block early on the process, by doing something like:SELECT @x = COUNT() FROM t1 WITH (HOLDLOCK, UPDLOCK) WHERE c1 = @p1Would this in general:a) lock a single set of rows (i.e. the ones where C1=@p1) in the index cidx?b) effectively place a index wide lock?If it is a), then the issue I have is that the two processes are independent, (different users logged into different clients!) so the runtime values of @p1 for the two procedures are more than likely to be completely different - in fact the values apply to different columns in the table, so there is no relationship between the parameters for the procedures at all.In addition, the count() is likely to be expensive in some cases - what if there are millions of rows?. If the locking is an index wide lock, would this lock still be held, even if the value for @p1 was chosen such that there we no rows at all where c1=@p1?These may be silly questions !This is such black art stuff.
Anonymous
August 17, 2010
Bart, thank you for this excellent primer.I am repeatedly getting a deadlock that I'm not sure how to solve, even after reading your tips. I think I understand what is causing the deadlock but I'm just clueless as to the best way to solve it. Let me explain:I have a table that has a TEXT column as well as a couple of CHAR columns. The "text in row" table option is currently off, which means, if I understand correctly, that the TEXT column's data will be stored in a separate page than the rest of the data for a given row. I realize that the TEXT datatype is supposed to be phased out in favor of VARCHAR(MAX) but unfortunately, I cannot change the schema for the table in question because it is not under our control (it was created by an application we purchased).Looking at the deadlock graph, we have one process that does a SELECT on that table, selecting all columns, including the TEXT column. The other process in the graph does an INSERT on that table, inserting a single row, including the TEXT column.The SELECT process owns a page lock (mode S) on one page and is requesting a page lock (mode S) on a second page. On the other hand, the INSERT process owns a page lock (mode IX) on the second page and is requesting a page lock (mode IX) on the first page. Clearly, this is a deadlock.I presume that one of the pages holds the data for the TEXT column and the other page holds the data for the other columns. Is this a correct assumption?The SELECT process seems to always be the loser because it is the least expensive (i.e. it has Log Used: 0). Our current "fix" is to detect the deadlock in the SELECT process and try it again when it occurs. Unfortunately, as we add more users to the system, the deadlocks seem to be happening more frequently and the system also seems to be less responsive.To improve this situation, I'm considering the following options:1) Rewrite the SELECT process so it does NOT select the TEXT column. This is for sort of a "ticker tape" use-case and the users might be able to live without seeing the data in the TEXT column. If they want to see it for a particular row, perhaps they can "drill down". But I don't think our UI developer likes this option.2) Use the "WITH (NOLOCK)" option in the SELECT process. However, what happens when you set this option and the INSERT process has only written one page and not the other when the SELECT goes to read the same record? Will the SELECT only see part of the record (e.g. either the TEXT column or the non-TEXT columns but not both)??? This worries me...3) Set the isolation level of the SELECT process to read uncommitted. But I have the same worry as in option 2.4) Set the "text in row" table option to ON, so that all of a row's data is (hopefully) stored on the same page. However, if the vendor of the application ever updates the schema for that table, I guess I'll have to remember to reapply that table option?Any recommendations or wisdom would be appreciated.
Anonymous
August 17, 2010
@cyboc - > I presume that one of the pages holds the data for the TEXT column and the other page holds the data for the other columns. Is this a correct assumption?Maybe. Send me the -T1222 output (you can use the blog contact form, or I'll send you an offline msg w/my email address) and there might be some clues to confirm this. Do the pagelock resources in the -T1222 output identify an index ID for each page? Text/image data is always on index ID 255. Alternatively, there should be an associatedObjectId attribute. This is a partition ID, and you can figure out which index it belongs to by querying sys.partitions as described in blogs.msdn.com/.../deadlock-troubleshooting_2c00_-part-1.aspx. The fact that the SELECT is acquiring non-intent Shared page locks indicates that it is probably doing a full table or index scan. That usually points to a lack of indexes on the table that would support a more efficient plan. (Either that, or the SELECT returns a large % of the data in the table.) My recommendation would generally be to eliminate all unnecessary table/index scans before doing anything else, but it sounds like you may be reluctant to add any indexes because you don't "own" the schema... > 1) Rewrite the SELECT process so it does NOT select the TEXT column.That might work, assuming that the text data is typically small enough to fit on page, and assuming that the two pages involved in the deadlock are a text page and a non-text clustered index page. > 2) Use the "WITH (NOLOCK)" option in the SELECT process. ... > what happens when you set this option and the INSERT process has only written > one page and not the other when the SELECT goes to read the same record? > Will the SELECT only see part of the record (e.g. either the TEXT column or the > non-TEXT columns but not both)??? This worries me...I'm not sure if the scenario you describe is possible, but NOLOCK / dirty reads can definitely cause odd behavior. (I have never seen a case where an inconsistent version of a single row was returned for a table that used text/image... the typical symptom is the query simply failing with a strange error.) > 4) Set the "text in row" table option to ONThat might work, again assuming that the text data is typically small enough to fit on page, and assuming that the two pages involved in the deadlock are a text page and a non-text clustered index page. Have you considered enabling the read committed snapshot database option? That would probably address this along with most other blocking problems where one of the participants is only doing reads.
Anonymous
June 27, 2011
I posted a comment earlier, but am not sure it got through as I was not logged in. In a nutshell, we use cursors, the "Fetch" cursor is being marked as the deadlock victim when we are updating the same table, the same result set, sometimes. Currently, removing the cursors would be an unfeasible solution. The SQL uses a distinct index and selects all columns for the given table. Is there any good way to find the reason for the deadlock in this kind of scenario? Using the snapshot feature seems kind of dangerous in our application, and the indexes seem OK. None are clustered, by the way. Don't know if that matters or not. Any ideas? If you need more information, and did not get my other comment post, then tell me. Was doing it from memory, so I might have forgotten some details. Thanks.
Anonymous
June 28, 2011
tbaldarelli - A dynamic cursor essentially runs a slightly modified very of your query anew for each fetch. Other cursors run the query once, pausing the scan while the code processes the row in between each fetch call. In either case, it's mostly the same as other deadlocks. The data collection process is the same, and the same deadlock avoidance techniques generally apply. I would capture the -T1211 output and decode it as discussed in Part 1 (blogs.msdn.com/.../747119.aspx) to better understand why the two spids are deadlocked. Then try index tuning, which is always worth a shot even if the existing indexes seem reasonable. If all else fails, you'll need to apply one of the deadlock avoidance techniques mentioned above. If you post the -T1211 output I can try to help you with the first step.
Anonymous
February 14, 2012
Thanks a lot for the clear and concise explanation..... Most sites explain the classic scenario involving out of order updates on different tables thru different update statements... What you have explained here is a more practical and subtle variation of the same scenario...
Anonymous
May 15, 2012
The comment has been removed
Anonymous
August 15, 2012
Hello Bart,Thank you for your post.I have few questions for you and would like your to comment on my thoughts.do you see situations where replacing cl. index on non-cl. index reduces chances of deadlock.please comment begin tran / comit effect on probability of deadlock. in most recent case when delete deadlocked with select, I replaced cl. index for non.cl and added 1 more non-cl index to cover select stm.Now delete has lock on table object (not sure if it's table or page lock) and index seekon non-cl. index; select uses index seek.when before it was:delete: cl. index seek,cl.index deleteselect: non-cl. index seek, cl. index scan.I understand that during delete, all indexes are going to be excl. locked.But I feel that new situation has less chances for interferience.Actual deadlock could not be re-produced or tested.Please comment.Regarding #1:I had deadlock situation with update vs. another update which I could reproduce. After cl. index replacement for nonclust. version, deadlock could not be reproduced.I could add 1 more index to change index scan to index seek which could reduce footprint and improveefficency. After numerious tests, this index addition seemed irrelevant to deadlock situation.Regarding #2.I review coding where proc 1 starts begin tran block, calls proc 2, then proc 2 calls proc 3 andthat's where deadlock happens. In most situations i can re-produce deadlock when begin tranis not envolved.I usually can reproduce deadlocks. But even after I fix indexes for these statements, I still see deadlocks even though more rearly.Which makes me to conclude next:Deadlocks coming from begin tran logic may have 2 reasons for it:poor index structure for deadlocked statements. additional locks caused by begin tran logic.
Anonymous
February 16, 2014
This is another way to avoid the deadlock:CREATE PROC p1 @p1 int AS declare @c1 int; SELECT @c1 = c1 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1  SELECT c2, c3 FROM t1 WHERE c1 = @c1GOI was surprised that this one worked too (running the reproduction scripts in step 2). Why? Does SQL Server scopes the shared locks on the subqueries?CREATE PROC p1 @p1 int AS select c2, c3 from t1 where c1 in (select c1 from t1 where c2 BETWEEN @p1 AND @p1+1)GO

Udostępnij za pośrednictwem

Deadlock Troubleshooting, Part 3

Comments

Dodatkowe zasoby