Determining SQL statements involved in deadlock
-
I was wondering if anyone can provide some insights on this. As I am dealing with a vendor-supplied system, I have only limited access to the VB code that is generating SQL calls. I have been successful in tracing and correcting a number of deadlocking issues with this system mostly by using info about the SQL statements captured in the deadlock traces and then tracking that back to the appropriate VB app. However, there are a few that are still eluding me because the trace has not captured exactly what SQL statements were executing. Below is the trace info from one such event. Thanks!
Deadlock encountered .... Printing deadlock information
2004-03-29 09:00:54.39 spid4
2004-03-29 09:00:54.39 spid4 Wait-for graph
2004-03-29 09:00:54.39 spid4
2004-03-29 09:00:54.39 spid4 Node:1
2004-03-29 09:00:54.39 spid4 KEY: 7:1659152956:1 (0d00858caf6f) CleanCnt:1 Mode: X Flags: 0x0
2004-03-29 09:00:54.39 spid4 Grant List::
2004-03-29 09:00:54.39 spid4 Owner:0x2b7e2c20 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
2004-03-29 09:00:54.40 spid4 SPID: 52 ECID: 0 Statement Type: SELECT Line #: 19
2004-03-29 09:00:54.42 spid4 Input Buf: RPC Event: sp_executesql;1
2004-03-29 09:00:54.42 spid4 Requested By:
2004-03-29 09:00:54.42 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:67 ECID:0 Ec
0x2ef87598) Value:0x2b7e3740 Cost0/1218)2004-03-29 09:00:54.42 spid4
2004-03-29 09:00:54.42 spid4 Node:2
2004-03-29 09:00:54.42 spid4 KEY: 7:1659152956:1 (7b007c8bf01b) CleanCnt:1 Mode: X Flags: 0x0
2004-03-29 09:00:54.42 spid4 Grant List::
2004-03-29 09:00:54.42 spid4 Owner:0x2b7e2d40 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:67 ECID:0
2004-03-29 09:00:54.42 spid4 SPID: 67 ECID: 0 Statement Type: SELECT Line #: 19
2004-03-29 09:00:54.42 spid4 Input Buf: RPC Event: sp_executesql;1
2004-03-29 09:00:54.42 spid4 Requested By:
2004-03-29 09:00:54.42 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec
0x2c1cf518) Value:0x2b7e3700 Cost0/10D0)2004-03-29 09:00:54.42 spid4 Victim Resource Owner:
2004-03-29 09:00:54.42 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec
0x2c1cf518) Value:0x2b7e3700 Cost0/10D0)2004-03-29 09:00:54.45 spid52 Error: 1205, Severity: 13, State: 50
2004-03-29 09:00:54.45 spid52 Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction..
-
Have you thought about running a trace against the server to capture the textdata information so that you can go back and see what that spid was attempting to execute at the time of the deadlock?
-
I have run some Profiler traces but am not sure how to definitively capture the exact SQL queries that will correlate back to the SPIDS of the deadlock participants. Also, is there a way to make sure of that correlation, other than the time stamps/spids? I have noticed that several different queries or batches might execute on the same spid very rapidly around the time of the deadlock.
-
In the trace properties you could remove everything from the events to trace other than Locks
eadlock and Lockseadlock Chain. This would give you more relevant information. -
That may be helpful paring down the amount of data in the trace, as I am down to maybe 1 to 5 deadlock events occurring a day on this fairly busy system, and the amount of data that would be captured otherwise is tremendous. Will these particular trace events give me the SQL statements involved?
-
The "text" on how to solve blocking problems... be aware there is a lot of data collected.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
K. Brian Kelley
@kbriankelley -
Brian and stacenic, I certainly appreciate the info and the link. I will post back here if I encounter any questions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply