Deadlock Troubleshooting, Part 1

re: Deadlock Troubleshooting, Part 1

ice9 — Mon, 11 Sep 2006 19:21:34 GMT

Great Article, Can you recommend me some tips to solve a deadlock problem?
I have a Master Stored Procedure, which internally calls other procedures as needed. This master proc is invoked from a Web Application (ASPX). Sporadically I get error messages
Error Detail: System.Data.SqlClient.SqlException: Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

1. How do detect where the problem is...means which section of the code is causing deadlock?
2. How should I rectify them?

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

TIA

re: Deadlock Troubleshooting, Part 1

bartd — Mon, 11 Sep 2006 19:36:46 GMT

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

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

> 2. How should I rectify them?
Once you identify the queries involved in the deadlock, follow steps 4-8 in the post.

re: Deadlock Troubleshooting, Part 1

rogerlin — Tue, 24 Oct 2006 19:33:34 GMT

Hi Bart,

In the decoding process, you idetify one as conflicting Update lock, another one you just call Update lock. What's the difference between a conflicting lock and a ordinary lock? Does the trace flag 1222 deadlock graph provide the detail so we would know which one is a conflicting lock and which is not? Does it makes a difference when a lock is conflicting or not?

Thanks a lot,

Roger

re: Deadlock Troubleshooting, Part 1

bartd — Tue, 24 Oct 2006 20:20:56 GMT

Roger,

By "conflicting" I simply meant that that the existing lock was incompatible with the new lock request. "Conflicting lock" == blocking lock. That's just my word choice, not a technical term. If two lock requests are compatible (e.g. two shared locks), they will both be granted. If the two requests conflict, one will be blocked and the other granted.

The 1222 output does identify which lock requests have been granted (<owner-list>) and which are blocked (<waiter-list>).

Bart

re: Deadlock Troubleshooting, Part 1

serhat — Mon, 13 Nov 2006 20:34:59 GMT

Thanks so much for the info... It helped me tremendously especially when I was trying to repeat the problem. FYI: my problem was related with indexes. We have a legacy app with stored procedures updating the same row for different purposes. And one of the columns being updated has also a non-clustered index. I open 2 query analyser and put while 1=1 to run 2 SPs and then Boom, I get the error in either fex seconds or 20 seconds. the only time I don't get an error iswhenI remove all the indexes which is not an option. Btw, this table doesn't have a primary key... I know I know.... It wasn't me who created and that person is no longer with the company... :) But I have to fix it.

re: Deadlock Troubleshooting, Part 1

sqlminds — Wed, 13 Dec 2006 22:36:29 GMT

Check the Companion tool at www.sqlminds.com It will do for you all of the above steps and more. For example, if you have 3 or 4 SIDs deadlocking and each SIDs has multiple statements per DB transaction, the above approach will fail since it will report ONLY the statements, which deadlocked:

SID1 - begin tran update t1 ... where PK = 1

SID2 - begin tran update t1 ... where PK = 2

SID3 - begin tran update t1 ... where PK = 3

then

SID1 - select * from t1 where PK = 3

SID2 - select * from t1 where PK = 1

SID3 - select * from t1 where PK = 2

This is where the deadlock monitor (spid=4) will kick in and guess what, you'll be getting the last three statements in the output

SID1 - select * from t1 where PK = 3

SID2 - select * from t1 where PK = 1

SID3 - select * from t1 where PK = 2

I don't think you can figure out the deadlock given only these three statements. You can do some tedious digging into the outstanding locks and figure out the deadlock but this can be done with the assumption that you know intimately your statements (i.e. what if you are an ASP - App service provider...). Check out the tool I've mentioned; it will give you the blocking chain PLUS the timing. HTH

re: Deadlock Troubleshooting, Part 1

bartd — Tue, 19 Dec 2006 09:52:16 GMT

The tool you describe sounds pretty cool.

FWIW, step 8 in the instructions above mentions that a profiler trace may be necessary if one or more of the deadlock participants are involved in a multi-batch transaction.

re: Deadlock Troubleshooting, Part 1

Tushar — Wed, 03 Jan 2007 09:59:22 GMT

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

tnks a lot for any help !

re: Deadlock Troubleshooting, Part 1

bartd — Wed, 03 Jan 2007 19:31:21 GMT

Possibly. One such case would be if the SELECT statements used a hint to change the type of locks being acquired (e.g. UPDLOCK, XLOCK). You could also see this if the SELECT statements were part of a multi-statement transaction. For example, these two transactions could deadlock on the SELECT statements:

Connection 1:
     begin tran
     update t1 set ... where c1 = x
     select * from t1 where c1 = y

Connection 2:
     begin tran
     update t1 set ... where c1 = y
     select * from t1 where c1 = x

Troubleshoot these just as you would any other deadlock.

re: Deadlock Troubleshooting, Part 1

Prasanna Prabhu — Fri, 09 Feb 2007 03:58:44 GMT

In the text above, on point #2 you have highlighted the TEXT with YELLOW back-ground, which is really cool.

Will this feature (text with Yellow back-ground) be available out-of-box, if it can be, then it will be really cool

re: Deadlock Troubleshooting, Part 1

bartd — Wed, 28 Feb 2007 16:03:07 GMT

Prasanna,

No, the SQL errorlog is just a plain text file; the yellow text highlighting is my emphasis. I did it to call out some of the data points in the 1222 output that can be the most useful when trying to understand a deadlock. You'll have to locate these data points in your own -T1222 output yourself.

Bart

re: Deadlock Troubleshooting, Part 1

Dmitrey — Tue, 27 Mar 2007 05:19:34 GMT

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

You:

Spid 52 is running a DELETE statement on line 6 of the stored proc spClearItemStatus. He holds an X lock on the key resource KEY: 7:2121058592:2 (a70064fb1eac). This lock is blocking spid 52, who is waiting to acquire a U lock on the same key.

Q: Should it say: This lock is blocking spid 51, instead?

You:

Spid 51 is running an UPDATE statement on line 47 of the stored proc spUpdateItemProp. He holds an X lock on key KEY: 7:1977058079:1 (02014f0bec4e). His X lock is blocking spid 51, who is waiting to acquire an X lock on the same key.

Q: Should it say: This lock is blocking spid 52, instead?

Regards,

Dmitrey

re: Deadlock Troubleshooting, Part 1

bartd — Wed, 28 Mar 2007 23:13:26 GMT

Dmitrey,

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

Thanks!

Bart

re: Deadlock Troubleshooting, Part 1

Bill — Tue, 10 Apr 2007 22:49:43 GMT

Hi, Bart

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

Thank you,

Bill

If one or both spids is running a multi-statement transaction, you may need to capture a profiler trace that spans the deadlock in order to identify the full set of queries that were involved in the deadlock. Unfortunately, both -T1204 and -T1222 only print out the two queries that “closed the loop”, and it’s possible that one of the blocking locks was acquired by an earlier query run within the same transaction.

re: Deadlock Troubleshooting, Part 1

Bill — Wed, 11 Apr 2007 02:11:27 GMT

Hi,

Which profiler events capture such scenario?

Bill

re: Deadlock Troubleshooting, Part 1

bartd — Thu, 12 Apr 2007 22:18:14 GMT

Bill,

Capture RPC:Starting/Completed, SQL:BatchStarting/Completed, and SQLTransaction at minimum. If possible without introducing undue load on the server, add SQL:StmtStarting, RPC:StmtStarting, and a pre-execution showplan event like Showplan All/Showplan XML. -T1222 will tell you what resources (including table names) were involved in the deadlock. Those profiler events will tell you what tables were modified within the participating spids' transactions. Taken together, trace flag 1222 and the profiler trace should allow you to figure out which queries acquired the locks that were involved in the deadlock.

HTH,

Bart

re: Deadlock Troubleshooting, Part 1

Bill — Fri, 13 Apr 2007 04:03:48 GMT

Hi, Bart

Can you give me an example of more about the statement below and why Deadlock Graph\-T1222 can not accomplish? I guess i stll dont understand..

Thank you,

Bill

re: Deadlock Troubleshooting, Part 1

bartd — Mon, 16 Apr 2007 01:17:04 GMT

Bill,

Consider the simple deadlock scenario described at the beginning of http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx. In a case like this, only query #3 would be shown in the -T1222 (or -T1204) output. You would need a profiler trace if you wanted to see query #2 for each transaction, which played an important role in the deadlock.

HTH,

Bart

re: Deadlock Troubleshooting, Part 1

Anders — Tue, 17 Apr 2007 22:51:46 GMT

Is there a reason why adding the "Lock:Deadlock graph" event class will not generate the event when the Lock:Deadlock event is recorded?

In addition, the -T1222 output is indicating two different resources for a RID lock (names have been changed to protect the innocent :O). Why would it deadlock on statements in two different tables?

ridlock fileid=1 pageid=1711 dbid=5 objectname=db.dbo.t1 id=lock39ebac0 mode=X associatedObjectId=72057594039959552

and

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

Thanks,

Anders

re: Deadlock Troubleshooting, Part 1

bartd — Sun, 22 Apr 2007 21:58:33 GMT

Anders:

> “Is there a reason why adding the "Lock:Deadlock graph" event class will not generate the event when the Lock:Deadlock event is recorded?”

Sorry, could you clarify this question? Capturing the "Lock:Deadlock Graph” event in a profiler trace will provide the same information that you can get in the errorlog via -T1222. To see the raw XML instead of the graphical view of the deadlock that the profiler GUI shows, right-click on the event and select “Extract event data”. (The graphical view is pretty and easier to understand, but it is also only provides a subset of the information that is available in the raw XML.)

> “In addition, the -T1222 output is indicating two different resources for a RID lock (names have been changed to protect the innocent :O). Why would it deadlock on statements in two different tables?”

Every deadlock must involve a minimum of two *different* lock resources. Each resource is owned by one of the deadlock participants. Each deadlock participant is blocked, waiting to get access to the resource currently locked by the other participant. That’s the way deadlocks work; if only one lock resource was involved, it could only be a simple blocking incident, not a deadlock.

Bart Duncan's SQL Weblog : Deadlock Troubleshooting, Part 3

Bart Duncan's SQL Weblog : Deadlock Troubleshooting, Part 3 — Tue, 01 May 2007 22:08:17 GMT

PingBack from http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx

re: Deadlock Troubleshooting, Part 1

adam.bean — Fri, 11 May 2007 23:16:15 GMT

Hello Bart,

Great article, best I've read on 2005 deadlocking thus far. I've always used the -T1204 and -T3605 in 2000. I was getting ready to add these to our new 2005 servers when I thought I should look for an update ... low and behold there is.

I've added the -T1222 via config manager, restarted, and created a deadlock (http://wardyit.com/blog/blog/archive/2005/12/12/65.aspx) yet I see no messages in my eventviewer nor displayed on screen ... just the deadlock error itself.

Thanks

re: Deadlock Troubleshooting, Part 1

bartd — Fri, 11 May 2007 23:36:41 GMT

Adam,

Did you enable the trace flag via DBCC TRACEON, or as a server startup parameter? If you enabled it as a server startup param, did you bounce the SQL service? If you enabled it via DBCC TRACEON, be sure that you set it as a global trace flag (note the "-1" in "DBCC TRACEON (1222, -1)"). The trace flag infrastructure is implemented completely differently in SQL 2005, so there are certain trace flags that now must be enabled globally when a simple "DBCC TRACEON (flag)" would suffice in SQL 2000. That might be worth a blog post someday...

If you're pretty sure that this is what you did, run "DBCC TRACESTATUS (-1)". If the trace flag is set correctly, you'll see 1222 in the list of enabled trace flags, and the "Global" column will be set to 1. If 1222 shows up in the list but the Global column is set to 0, you set the trace flag at the session level (meaning only for your spid -- it won't apply to the deadlock monitor thread, which is the only spid that can spew -T1222 to the errorlog).

If the trace flag is definitely set globally, recreate your deadlock, wait for it to be detected and resolved, then run "sp_readerrorlog". You should see the -T1222 output near the end of the log (search for the text "deadlock").

Hope this helps,

Bart

re: Deadlock Troubleshooting, Part 1

adam.bean — Sat, 12 May 2007 00:05:24 GMT

Thanks for the fast reply Bart.

Yeah I added it via the gui (Config Manager) -T1222; at the end of the startup parameters. Restarted SQL afterwards and still not seeing any information in the error log.

Running DBCC TRACESTATUS (-1) returns nothing but completed successfully.

I don't like the new config manager ... what here is wrong?

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1222

re: Deadlock Troubleshooting, Part 1

bartd — Sat, 12 May 2007 03:00:39 GMT

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

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

You should have a "SQLArg3" value there with the data "-T1222". If it seems to be in order, is there any chance that you copied and pasted the text "-T1222" when you added the param originally? Word and some other editors have an annoying habit of replacing a normal ASCII dash "-" character with a long dash. Visually, the long dash is very difficult to differentiate from the ASCII dash, but it is sufficient to make SQL unable to understand the command line param.

Just a guess... If this is a possibile explanation, edit the value in regedit, re-type the string, then bounce SQL so it will re-read that reg key. In the short-term you can enable the trace flag temporarily by running "DBCC TRACEON (1222, -1)", just don't forget the -1. But it should work to add the trace flag as a SqlArg -- SQL wouldn't be able to start at all if it couldn't read the params listed in that reg key, as it is the only way the server can find out where the master database lives.

HTH,

Bart

re: Deadlock Troubleshooting, Part 1

adam.bean — Mon, 21 May 2007 22:54:37 GMT

Just wanted to follow up and say I got it working ... I had an extra space before the -T1222 flag!

Anyways, thanks for the help and this useful information.

re: Deadlock Troubleshooting, Part 1

Shagun — Wed, 06 Jun 2007 10:37:47 GMT

Hi Bart,

I'll really appreciate if you can interpret following deadlock graph on SQL Server 2000

2007-06-04 16:28:32.21 spid4 Node:1

2007-06-04 16:28:32.21 spid4 KEY: 8:1358627883:2 (9e045f9bca23) CleanCnt:2 Mode: Range-S-S Flags: 0x0

2007-06-04 16:28:32.21 spid4 Grant List 0::

2007-06-04 16:28:32.22 spid4 Owner:0x42cd4160 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:139 ECID:0

2007-06-04 16:28:32.24 spid4 SPID: 139 ECID: 0 Statement Type: INSERT Line #: 1

2007-06-04 16:28:32.24 spid4 Input Buf: RPC Event: sp_execute;1

2007-06-04 16:28:32.24 spid4 Grant List 1::

2007-06-04 16:28:32.24 spid4 Requested By:

2007-06-04 16:28:32.26 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:138 ECID:0 Ec:(0x44843508) Value:0x42cdce40 Cost:(0/24D0)

2007-06-04 16:28:32.29 spid4

2007-06-04 16:28:32.35 spid4 Node:2

2007-06-04 16:28:32.35 spid4 KEY: 8:1358627883:2 (9e045f9bca23) CleanCnt:2 Mode: Range-S-S Flags: 0x0

2007-06-04 16:28:32.37 spid4 Grant List 0::

2007-06-04 16:28:32.37 spid4 Grant List 1::

2007-06-04 16:28:32.37 spid4 Owner:0x42cddc60 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:138 ECID:0

2007-06-04 16:28:32.38 spid4 SPID: 138 ECID: 0 Statement Type: INSERT Line #: 1

2007-06-04 16:28:32.43 spid4 Input Buf: RPC Event: sp_execute;1

2007-06-04 16:28:32.43 spid4 Requested By:

2007-06-04 16:28:32.57 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:139 ECID:0 Ec:(0x4469F508) Value:0x42cd41e0 Cost:(0/166C)

2007-06-04 16:28:32.68 spid4 Victim Resource Owner:

2007-06-04 16:28:32.68 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:139 ECID:0 Ec:(0x4469F508) Value:0x42cd41e0 Cost:(0/166C)

2007-06-04 16:28:42.80 spid4

Thanks

-Shagun

re: Deadlock Troubleshooting, Part 1

Bas Vedder — Tue, 24 Jul 2007 18:01:39 GMT

Wondering if someone can explain the following:

Create a table with no indexes, an Identity column (int), two varchar(50) columns and one smallint column.

The last three columns all nullable.

Enter two rows with values. Now in two Query windows execute the following:

1) BEGIN TRAN

SELECT * FROM TestRowID WITH (UPDLOCK)

WHERE ID = 1

2) BEGIN TRAN

SELECT * FROM TestRowID WITH (UPDLOCK)

WHERE ID = 2

The statement in the second is blocked by the first statement....

If we look at the activity monitor in SQL 2005 we for :

1) DATABASE 0 S Lock Granted

OBJECT 2073058421 IX Lock Granted

PAGE 72057594038321152 IU Lock Granted 1:154

RID 72057594038321152 U Lock Granted 1:154:0

2) DATABASE 0 S Lock Granted

OBJECT 2073058421 IX Lock Granted

PAGE 72057594038321152 IU Lock Granted 1:154

RID 72057594038321152 U Lock Wait 1:154:0

Object 2073058421 is the table in question.

Now the question is, why is the second window trying to take out an U lock on RID 0?

It should lock another row, right?

Even when we introduce an non-clustered index on the ID Column, the same issue still occurs.

The database is SQL Server 2005 SP 2, but the same is observed on SQL 2000. However, introducing an

index on SQL 2000 does remove the blocking behaviour....

re: Deadlock Troubleshooting, Part 1

bartd — Wed, 22 Aug 2007 05:58:24 GMT

Bas, because there is no index on the table, SQL must visit and evaluate every row to see if it needs to be updated. Because the row might be updated, SQL must acquire an Update lock on each row as part of this evaluation (to prevent a common type of deadlock).

Your first transaction acquires and holds a lock on a row that is incompatible with the Update lock that the second transaction will acquire.

Creating an index might avoid the blocking if SQL chooses to use it. However, SQL may choose to use a table scan if (as in your example) the number of rows in the table is very small (or the percentage of the table that the QO estimates it will need to be update is large). If SQL chooses to scan the table despite the presence of an index, you will still see the same locking behavior. The fundamental rules of governing this decision haven't changed in SQL 2005, but there are subtle changes in the costing of possible plans in different versions of SQL that could cause a scan to be costed as slightly cheaper on one version and a seek to be costed as a bit cheaper on a different version.

re: Deadlock Troubleshooting, Part 1

Mpls Mike — Sat, 06 Oct 2007 00:08:03 GMT

Bart,

Great post!

What are your thoughts on using SET CONTEXT_INFO (or sp_bindsession) ? I have an ETL process that has frequent deadlocks, even though the data is strictly partitioned - doesn't overlap. Don't want index maint on loading/transforming, and when disabled page locks, the process slowed down tremendously.

Thanks

Mike

re: Deadlock Troubleshooting, Part 1

bartd — Sun, 07 Oct 2007 09:05:32 GMT

Mike,

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

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

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

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

Bart

Best SQL server deadlock debugging instructions ever

DotNetKicks.com — Sun, 14 Oct 2007 23:45:08 GMT

You've been kicked (a good thing) - Trackback from DotNetKicks.com

re: Deadlock Troubleshooting, Part 1

Bas Vedder — Tue, 16 Oct 2007 10:37:26 GMT

Thanks Bart.

re: Deadlock Troubleshooting, Part 1

adam.bean — Tue, 13 Nov 2007 22:27:38 GMT

Bart,

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

Thanks!

re: Deadlock Troubleshooting, Part 1

Mpls Mike — Fri, 16 Nov 2007 21:02:30 GMT

Bart,

The bindsession (context_info) method worked like a charm.

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

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

Thanks!

Not published yet

Andrew Calvett — Thu, 24 Jan 2008 15:13:36 GMT

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

SSMS Log file viewer and Deadlock Graphs

Andrew Calvett — Thu, 24 Jan 2008 15:13:36 GMT

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

re: Deadlock Troubleshooting, Part 1

Rony — Thu, 28 Aug 2008 10:07:28 GMT

Hi bart,

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

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

inputbuf

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

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

executionStack

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

inputbuf

resource-list

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

owner-list

owner id=process88b1f8 mode=IX

waiter-list

waiter id=processbc44d8 mode=U requestType=wait

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

owner-list

owner id=processbc44d8 mode=U

waiter-list

waiter id=process88b1f8 mode=U requestType=wait

Do you have any idea whats going on ?

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

Thanks in advance

re: Deadlock Troubleshooting, Part 1

bartd — Tue, 02 Sep 2008 21:00:19 GMT

Rony -

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

HTH,

Bart

re: Deadlock Troubleshooting, Part 1

Rony — Thu, 04 Sep 2008 05:15:24 GMT

Hi Bart,

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

Any other suggestions bart ?

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

Rony

Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"

Bart Duncan's SQL Weblog — Wed, 24 Sep 2008 23:57:13 GMT

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

re: Deadlock Troubleshooting, Part 1

Satish — Thu, 30 Oct 2008 11:04:09 GMT

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

Please suggest how to trace dead lock information?

re: Deadlock Troubleshooting, Part 1

bartd — Thu, 30 Oct 2008 14:19:54 GMT

Satish -

1. What is the exact text of the error message you received in the application?

2. What is the exact text of the DBCC command you ran to turn on the trace flags?

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

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

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

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

HTH -

Bart

Reproducing deadlocks involving only one table

Alexander Kuznetsov — Fri, 02 Jan 2009 02:21:57 GMT

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

re: Deadlock Troubleshooting, Part 1

FrankG_sar — Mon, 19 Jan 2009 13:53:54 GMT

Very interestings posts (the three ones).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

re: Deadlock Troubleshooting, Part 1

bartd — Mon, 19 Jan 2009 20:41:51 GMT

FrankG, you may have meant it tongue-in-cheek :), but yes, I think your best bet may be to contact MS support for assistance with your deadlock involving MS repl-created tables. The only option available to you without modifying system procs or system tables would be to force a different plan with a plan guide, and that approach to a solution may not "stick" across service packs or QFEs if the change updates the merge trigger or the MSMerge stored proc involved in the deadlock.

Reproducing one more intermittent deadlock on only one table

Alexander Kuznetsov — Thu, 26 Mar 2009 19:00:54 GMT

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

re: Deadlock Troubleshooting, Part 1

Joon — Tue, 28 Apr 2009 09:39:51 GMT

This is a really great article.

I wish that I had access to something like this resource 5 years ago when I had to solve some spectacular deadlocking issues on a SQL Server 2000 app.

I especially like the index tuning advisor hint - that is sooooo true.

re: Deadlock Troubleshooting, Part 1

Rashmi — Tue, 28 Jul 2009 22:58:46 GMT

Can 2 processes acquire rowlock on same row?

I'm facing a deadlock where 2 processes have acquired row lock on same row and waiting for eachother. Here is the deadlock graph.

2009-07-28 14:13:29.50 spid18s ridlock fileid=1 pageid=10089 dbid=5 objectname=dcmdb.dcmdbuser.fs_payaccount id=lock1254ce80 mode=X associatedObjectId=72057595031977984

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

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

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

2009-07-28 14:13:29.50 spid18s waiter id=process93a988 mode=U requestType=wait

2009-07-28 14:13:29.50 spid18s ridlock fileid=1 pageid=10089 dbid=5 objectname=dcmdb.dcmdbuser.fs_payaccount id=lock1257ba00 mode=X associatedObjectId=72057595031977984

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

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

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

2009-07-28 14:13:29.50 spid18s waiter id=process93af28 mode=U requestType=wait

As you can see above the associatedObjectId is same for both ridlocks.

When can this happen? From the logic of my program 2 threads will never update the same row in fs_payaccount table.

re: Deadlock Troubleshooting, Part 1

bartd — Tue, 28 Jul 2009 23:44:23 GMT

Rashmi,

"associatedObjectId" isn't a row identifier; it's the heap or B-tree identifier (HoBT ID -- see http://msdn.microsoft.com/en-us/library/ms178104.aspx for documentation of the 1211 output fields that aren't doc'ed in this post). In other words, it just identifies the table or index. You can look up the object name associated with a HoBT ID using "SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = xxx", but it would just tell you that the row lock is on table "dcmdb.dcmdbuser.fs_payaccount", which you can already tell from the rest of the 1211 output.

In other words, there are two different rows (from the same table) involved in this deadlock. There's not enough information here to be sure, but my guess would be that one or both of the queries involved are scanning this table to locate the rows to update. This would acquire Update locks on all rows.

Bart

re: Deadlock Troubleshooting, Part 1

Cihat — Tue, 08 Sep 2009 17:16:53 GMT

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