Deadlock Trace - can you interpret?

Hi all.
I'm confused by the deadlock trace posted at the end of this post. If anyone
knows where a more complete listing of the information in trace flag 1204
output exists, please let me know - BOL doesn't appear to tell all in this
case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
Does the trace mean that two SPIDs have exclusive locks on the same KEY in a
clustered index, and both SPIDS need a shared lock on the same KEY,
resulting in the deadlock?
Or, is it that two SPIDS have exclusive locks on different KEY values in the
same clustered index, and each SPID needs shared access to the other's KEY,
resulting in the deadlock?
The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
is this a specific key value in the clustered index? Also, what do the
following mean?
"Life:02000000",
"ResType:LockOwner",
"Stype:'OR' ",
"Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
"CleanCnt: 1"
Any help would be tremendously appreciated. I've been working heavily with
SQL Server and Sybase for 8 years now, and have traced deadlocks before, but
this one is leaving me feeling kinda naked.
Cheers,
Steve.
---begin deadlock
trace---
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
ECID:0
SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
Value:0x4679a220 Cost0/B0)
Node:2
KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
ECID:0
SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
Value:0x5b7462e0 Cost0/41AF8)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
Value:0x4679a220 Cost0/B0)
---end deadlock
trace---
Hello Steve,
Currently I am looking for somebody who is familar with it. We will reply here with more information as soon as possible.
If you have any more concerns on it, please feel free to post here. Thanks very much for your patience.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Steve
My comments inline:
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I'm confused by the deadlock trace posted at the end of this post. If
anyone
> knows where a more complete listing of the information in trace flag 1204
> output exists, please let me know - BOL doesn't appear to tell all in this
> case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> Does the trace mean that two SPIDs have exclusive locks on the same KEY in
a
> clustered index, and both SPIDS need a shared lock on the same KEY,
> resulting in the deadlock?
No, the next description's the right one.

> Or, is it that two SPIDS have exclusive locks on different KEY values in
the
> same clustered index, and each SPID needs shared access to the other's
KEY,
> resulting in the deadlock?
This is absolutely correct. The two nodes in the deadlock graph contain
eXclusive locks on different clustered index keys and are also trying to
acquire shared
locks on each others already locked keys. This is an example of the fairly
common
cyclical deadlock scenario.
Another way you can read your deadlock graph is:
GRANTED LOCKS
Node #1: SPID 65 is granted an X lock on KEY: 9:1109578991:1 (e3018fb914ef -
hash of key value)
Node #2: SPID 74 is granted an X lock on KEY: 9:1109578991:1 (290271463fe1-
hash of key value)
REQUESTED LOCKS
Node #1: SPID 74 requests an S lock on KEY: 9:1109578991:1 (e3018fb914ef -
hash of key value)
Node #2: SPID 65 requests an S lock on KEY: 9:1109578991:1 (290271463fe1-
hash of key value)
SPID 74 was chosen as the deadlock victim in your case, but keep in mind
that the choice of deadlock victim isn't just who "closed" the deadlock
embrace; the "cost" of work already performed is also taken into account.
Note that spid 74's cost was Cost0/B0) & 65's was Cost0/41AF8), although
I'm not sure how to really interpret those cost indicators.

> The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
> documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
e3018fb914ef represents a hashed value of the key. I've been told before
that this is a one way hash, so you can't reverse out the actual value. Howe
ver, this probably doesn't mean much to you as the actual key that the
deadlock occurs on probably won't help you solve the deadlock problem.

> is this a specific key value in the clustered index? Also, what do the
> following mean?
> "Life:02000000",
> "ResType:LockOwner",
> "Stype:'OR' ",
> "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
> "CleanCnt: 1"
I'm not sure what the other bits mean, so I'm hoping you get a more detailed
answer from the MS guys as well in this thread.
However, you have enough information to solve the problem anyhow. Your
analysis of the deadlock cause is correct. The thing to do now is solve it,
which is often easiest done by either :
(a) re-coding the spSSA_DataLog_AddRecord (if that's an option)
(b) implementing re-try logic in the client app
(c) using hints to acquire higher level locks instead of shared locks
(d) volunteering lower priority processes to be deadlock victims
(e) lowering transaction isolation levels
Without seeing your stored proc, it's hard to even make a suggestion as to
what the best approach is, but given you've been doing this a while, perhaps
you're right with the resolution part of this anyway.
I'm certainly looking forward to some more information on the details you've
requested as well.
HTH
Regards,
Greg Linwood
SQL Server MVP

> Any help would be tremendously appreciated. I've been working heavily with
> SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
> this one is leaving me feeling kinda naked.
> Cheers,
> Steve.
> ---begin deadlock
> trace---
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> ECID:0
> SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> Node:2
> KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> ECID:0
> SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
> Value:0x5b7462e0 Cost0/41AF8)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> ---end deadlock
> trace---
>
|||Greg Linwood has done a good job to explain the deadlock output. For more
info. check the following articles:
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8i93.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_9m43.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_3hdf.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_5xrn.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8um1.asp
| From: "Steve Cockayne" <steve@.nospam.com>
| Subject: Deadlock Trace - can you interpret?
| Date: Wed, 30 Jun 2004 11:14:08 -0700
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|
| Hi all.
|
| I'm confused by the deadlock trace posted at the end of this post. If
anyone
| knows where a more complete listing of the information in trace flag 1204
| output exists, please let me know - BOL doesn't appear to tell all in this
| case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
|
| Does the trace mean that two SPIDs have exclusive locks on the same KEY
in a
| clustered index, and both SPIDS need a shared lock on the same KEY,
| resulting in the deadlock?
|
| Or, is it that two SPIDS have exclusive locks on different KEY values in
the
| same clustered index, and each SPID needs shared access to the other's
KEY,
| resulting in the deadlock?
|
| The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
| documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
| is this a specific key value in the clustered index? Also, what do the
| following mean?
| "Life:02000000",
| "ResType:LockOwner",
| "Stype:'OR' ",
| "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
| "CleanCnt: 1"
|
| Any help would be tremendously appreciated. I've been working heavily with
| SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
| this one is leaving me feeling kinda naked.
|
| Cheers,
| Steve.
|
| ---begin deadlock
| trace---
| Deadlock encountered ... Printing deadlock information
|
| Wait-for graph
|
| Node:1
| KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 0::
| Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| ECID:0
| SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
|
| Node:2
| KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 1::
| Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| ECID:0
| SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
| Value:0x5b7462e0 Cost0/41AF8)
|
| Victim Resource Owner:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
| ---end deadlock
| trace---
|
|
|
|||Hi Steve,
Did you get my Emails? I am not sure if your Email address
'steve@.nospam.com' is correct or not. So, would plese send me an Email with
your contact information at a-virenp@.microsoft.com?
Thanks.
| From: "Steve Cockayne" <steve@.nospam.com>
| Subject: Deadlock Trace - can you interpret?
| Date: Wed, 30 Jun 2004 11:14:08 -0700
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|
| Hi all.
|
| I'm confused by the deadlock trace posted at the end of this post. If
anyone
| knows where a more complete listing of the information in trace flag 1204
| output exists, please let me know - BOL doesn't appear to tell all in this
| case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
|
| Does the trace mean that two SPIDs have exclusive locks on the same KEY
in a
| clustered index, and both SPIDS need a shared lock on the same KEY,
| resulting in the deadlock?
|
| Or, is it that two SPIDS have exclusive locks on different KEY values in
the
| same clustered index, and each SPID needs shared access to the other's
KEY,
| resulting in the deadlock?
|
| The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
| documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
| is this a specific key value in the clustered index? Also, what do the
| following mean?
| "Life:02000000",
| "ResType:LockOwner",
| "Stype:'OR' ",
| "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
| "CleanCnt: 1"
|
| Any help would be tremendously appreciated. I've been working heavily with
| SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
| this one is leaving me feeling kinda naked.
|
| Cheers,
| Steve.
|
| ---begin deadlock
| trace---
| Deadlock encountered ... Printing deadlock information
|
| Wait-for graph
|
| Node:1
| KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 0::
| Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| ECID:0
| SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
|
| Node:2
| KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 1::
| Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| ECID:0
| SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
| Value:0x5b7462e0 Cost0/41AF8)
|
| Victim Resource Owner:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
| ---end deadlock
| trace---
|
|
|
|||Hi Viren.
If you've got more to add to this post, why not do it in the thread so we
can all benefit?
Regards,
Greg Linwood
SQL Server MVP
"Viren Parikh" <a-virenp@.microsoft.com> wrote in message
news:MGPDBEEYEHA.2752@.cpmsftngxa06.phx.gbl...
> Hi Steve,
> Did you get my Emails? I am not sure if your Email address
> 'steve@.nospam.com' is correct or not. So, would plese send me an Email
with
> your contact information at a-virenp@.microsoft.com?
> Thanks.
> --
> | From: "Steve Cockayne" <steve@.nospam.com>
> | Subject: Deadlock Trace - can you interpret?
> | Date: Wed, 30 Jun 2004 11:14:08 -0700
> | Lines: 68
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> |
> | Hi all.
> |
> | I'm confused by the deadlock trace posted at the end of this post. If
> anyone
> | knows where a more complete listing of the information in trace flag
1204
> | output exists, please let me know - BOL doesn't appear to tell all in
this
> | case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> |
> | Does the trace mean that two SPIDs have exclusive locks on the same KEY
> in a
> | clustered index, and both SPIDS need a shared lock on the same KEY,
> | resulting in the deadlock?
> |
> | Or, is it that two SPIDS have exclusive locks on different KEY values in
> the
> | same clustered index, and each SPID needs shared access to the other's
> KEY,
> | resulting in the deadlock?
> |
> | The resource: 9:1109578991:1 is a clustered index. I haven't yet found
any
> | documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)"
means -
> | is this a specific key value in the clustered index? Also, what do the
> | following mean?
> | "Life:02000000",
> | "ResType:LockOwner",
> | "Stype:'OR' ",
> | "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
> | "CleanCnt: 1"
> |
> | Any help would be tremendously appreciated. I've been working heavily
with
> | SQL Server and Sybase for 8 years now, and have traced deadlocks before,
> but
> | this one is leaving me feeling kinda naked.
> |
> | Cheers,
> | Steve.
> |
> | ---begin deadlock
> | trace---
> | Deadlock encountered ... Printing deadlock information
> |
> | Wait-for graph
> |
> | Node:1
> | KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> | Grant List 0::
> | Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> | ECID:0
> | SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> | Requested By:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> | Value:0x4679a220 Cost0/B0)
> |
> | Node:2
> | KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> | Grant List 1::
> | Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> | ECID:0
> | SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> | Requested By:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
> | Value:0x5b7462e0 Cost0/41AF8)
> |
> | Victim Resource Owner:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> | Value:0x4679a220 Cost0/B0)
> | ---end deadlock
> | trace---
> |
> |
> |
>
|||Hi all.
Special thanks to Greg, Viren, and Yan-Hong for posting back - I'll post
once I've come up with a solution.
Greg - I'd rather fix the problem with your option (1) - rewrite the sproc -
as I'm pretty bummed it can deadlock itself. In other times I have written
client logic in my low-level database classes to re-try on deadlocks, so
that is an option.
I'll also look at taking exclusive locks instead of shared, but I think the
problem might not go away - follow my thinking here:
I'm using a C# SqlTransaction object to wrap multiple calls to my sproc,
which contains multiple base-sql statements (like, say 10). The table that
produces the deadlock has two indexes. The first is clustered on non-unique
fields to give range-search capability, but also contains the unique key.
The second index is nonclustered, solely on the unique key. The
SqlTransaction essentially ensures that a dataset is submitted through my
sproc, as an all-or-nothing transaction - the C# code loops through the
dataset, calling the sproc once for each data record.
There are multiple threads that could be doing this at the same time. I'm
thinking that the deadlock occurs because the series of calls to the sproc
is unordered - thus one call to the sproc from a SPID would lock a certain
pair of keys through two separate sproc calls, whilst another SPID could
require the keys in a different order, due to calling the sprocs in a
different order.
I'll certainly review the locking in this sproc in-depth, to be sure of what
locks actually are acquired in all cases, and how they are escalated, but I
wonder if sorting the my dataset in C# - before looping over the data to
call the sproc - would buy me deadlock-safety without changing the sproc.
I'll also review why I have the unique key from the nonclustered index as a
field in the clustered index. I think I might have put it in there as a
covering field, but I'm thinking this is not required in a clustered index,
as the leaf level of a clustered index is the data pages, so the dreaded
bookmark-lookup should not be required, right?
I'll certainly post once I know exactly why I'm deadlocking, and what
solution I choose, and when I get the e-mails from Viren, I'll review them
and post salient details to the newsgroup.
Cheers,
Steve.
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I'm confused by the deadlock trace posted at the end of this post. If
anyone
> knows where a more complete listing of the information in trace flag 1204
> output exists, please let me know - BOL doesn't appear to tell all in this
> case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> Does the trace mean that two SPIDs have exclusive locks on the same KEY in
a
> clustered index, and both SPIDS need a shared lock on the same KEY,
> resulting in the deadlock?
> Or, is it that two SPIDS have exclusive locks on different KEY values in
the
> same clustered index, and each SPID needs shared access to the other's
KEY,
> resulting in the deadlock?
> The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
> documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
> is this a specific key value in the clustered index? Also, what do the
> following mean?
> "Life:02000000",
> "ResType:LockOwner",
> "Stype:'OR' ",
> "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
> "CleanCnt: 1"
> Any help would be tremendously appreciated. I've been working heavily with
> SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
> this one is leaving me feeling kinda naked.
> Cheers,
> Steve.
> ---begin deadlock
> trace---
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> ECID:0
> SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> Node:2
> KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> ECID:0
> SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
> Value:0x5b7462e0 Cost0/41AF8)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> ---end deadlock
> trace---
>
|||Hi Steve
Your idea of sorting the set of rows operated on by the SqlTransaction is a
good idea and may help some.
The information you provided on your indexes is perhaps useful. I'd suggest
that you try out changing the clustered index from its current composite
column to the unique index column. Then create a non-clustered index for the
range selects, perhaps covering the columns to avoid bookmark lookups.
Generally speaking, it's a good idea to keep clustered indexes as narrow as
possible because all non-clustered indexes use the key values from clustered
indexes as lookup keys (where a clustered index exists). There are also some
minor processing overheads added for clustered indexes on non-unique columns
(uniquefiers). Bookmark lookups are a trade off, but certainly not always
dreaded! There are some big benefits, especially in the area of index
maintenance but I won't digress further.
Altering the indexes shouldn't be a hard thing to do. This may be a silver
bullet & you can always put them back to how they are now if you don't get
any benefits fro the change. I'd suggest it's worth a try. If you've got the
option to re-write the stored proc, that might be the best solution. One
suggestion I'd make there is that if you've got concurrent processes that
can run the stored proc, you might consider designing it so that each
instance operates on a range of rows so that deadlocking incidents resulting
from the updates / inserts / deletes not only against rows but index keys
(ranges) are reduced by virtue of the fact that each concurrent process is
operating on it's own "range" of rows rather than interleaving with other
concurrent processes through a shared, sorted rowset.
Regards,
Greg Linwood
SQL Server MVP
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uvuKlTrYEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Hi all.
> Special thanks to Greg, Viren, and Yan-Hong for posting back - I'll post
> once I've come up with a solution.
> Greg - I'd rather fix the problem with your option (1) - rewrite the
sproc -
> as I'm pretty bummed it can deadlock itself. In other times I have written
> client logic in my low-level database classes to re-try on deadlocks, so
> that is an option.
> I'll also look at taking exclusive locks instead of shared, but I think
the
> problem might not go away - follow my thinking here:
> I'm using a C# SqlTransaction object to wrap multiple calls to my sproc,
> which contains multiple base-sql statements (like, say 10). The table that
> produces the deadlock has two indexes. The first is clustered on
non-unique
> fields to give range-search capability, but also contains the unique key.
> The second index is nonclustered, solely on the unique key. The
> SqlTransaction essentially ensures that a dataset is submitted through my
> sproc, as an all-or-nothing transaction - the C# code loops through the
> dataset, calling the sproc once for each data record.
> There are multiple threads that could be doing this at the same time. I'm
> thinking that the deadlock occurs because the series of calls to the sproc
> is unordered - thus one call to the sproc from a SPID would lock a certain
> pair of keys through two separate sproc calls, whilst another SPID could
> require the keys in a different order, due to calling the sprocs in a
> different order.
> I'll certainly review the locking in this sproc in-depth, to be sure of
what
> locks actually are acquired in all cases, and how they are escalated, but
I
> wonder if sorting the my dataset in C# - before looping over the data to
> call the sproc - would buy me deadlock-safety without changing the sproc.
> I'll also review why I have the unique key from the nonclustered index as
a
> field in the clustered index. I think I might have put it in there as a
> covering field, but I'm thinking this is not required in a clustered
index,[vbcol=seagreen]
> as the leaf level of a clustered index is the data pages, so the dreaded
> bookmark-lookup should not be required, right?
> I'll certainly post once I know exactly why I'm deadlocking, and what
> solution I choose, and when I get the e-mails from Viren, I'll review them
> and post salient details to the newsgroup.
> Cheers,
> Steve.
>
> "Steve Cockayne" <steve@.nospam.com> wrote in message
> news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> anyone
1204[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
> a
> the
> KEY,
any[vbcol=seagreen]
means -[vbcol=seagreen]
with
> but
>
|||Hi Steve,
You should have got my other Emails but if not then here is what I have to
say on this subject.
I think Greg has done an excellent job to explain the deadlock output but
here is what I say:
What is a waits-for graph?
A waits-for graph is a directed graph that draws out who (which spid+ecid)
is waiting for which (key,row,table etc) resource. Deadlock detection
algorithm tries to find a loop in the directed graph to detect a deadlock.
And finally as the deadlock is detected, deadlock monitoring thread kills
the spid with the smallest cost.
What is different... 7.0 <>8.0?
Different from SQL Server 7.0 you have to think about resources as being
nodes instead of the session threads.
In addition, you may get some threads that does not participate in the
deadlock loop in the output.
One way to read the output:
To draw the graph make each Locked Resource (RID, KEY etc) is a node and
mark their owners. Then draw the arrow from the requestor to the owner for
each line in the output and you should end up with the loop. One of the
participants of this loop will get rolled back according to their costs.
See under BOL\Index\"deadlocks, troubleshooting" for the additional info or
check the following links:
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_7wxf.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_58z1.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_5xrn.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8i93.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_9m43.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_3hdf.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8um1.asp
Let me know if you have any more questions or want us to trouble shoot your
case.
Thanks.
Viren Parikh
| Newsgroups: microsoft.public.sqlserver.server
| From: a-virenp@.microsoft.com (Viren Parikh)
| Organization: Microsoft
| Date: Fri, 02 Jul 2004 14:29:33 GMT
| Subject: RE: Deadlock Trace - can you interpret?
| X-Tomcat-NG: microsoft.public.sqlserver.server
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
|
| Hi Steve,
|
| Did you get my Emails? I am not sure if your Email address
| 'steve@.nospam.com' is correct or not. So, would plese send me an Email
with
| your contact information at a-virenp@.microsoft.com?
|
| Thanks.
| --
| | From: "Steve Cockayne" <steve@.nospam.com>
| | Subject: Deadlock Trace - can you interpret?
| | Date: Wed, 30 Jun 2004 11:14:08 -0700
| | Lines: 68
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| | Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| |
| | Hi all.
| |
| | I'm confused by the deadlock trace posted at the end of this post. If
| anyone
| | knows where a more complete listing of the information in trace flag
1204
| | output exists, please let me know - BOL doesn't appear to tell all in
this
| | case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
| |
| | Does the trace mean that two SPIDs have exclusive locks on the same KEY
| in a
| | clustered index, and both SPIDS need a shared lock on the same KEY,
| | resulting in the deadlock?
| |
| | Or, is it that two SPIDS have exclusive locks on different KEY values
in
| the
| | same clustered index, and each SPID needs shared access to the other's
| KEY,
| | resulting in the deadlock?
| |
| | The resource: 9:1109578991:1 is a clustered index. I haven't yet found
any
| | documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)"
means -
| | is this a specific key value in the clustered index? Also, what do the
| | following mean?
| | "Life:02000000",
| | "ResType:LockOwner",
| | "Stype:'OR' ",
| | "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
| | "CleanCnt: 1"
| |
| | Any help would be tremendously appreciated. I've been working heavily
with
| | SQL Server and Sybase for 8 years now, and have traced deadlocks
before,
| but
| | this one is leaving me feeling kinda naked.
| |
| | Cheers,
| | Steve.
| |
| | ---begin deadlock
| | trace---
| | Deadlock encountered ... Printing deadlock information
| |
| | Wait-for graph
| |
| | Node:1
| | KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| | Grant List 0::
| | Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| | ECID:0
| | SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| | Requested By:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| | Value:0x4679a220 Cost0/B0)
| |
| | Node:2
| | KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| | Grant List 1::
| | Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| | ECID:0
| | SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| | Requested By:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
| | Value:0x5b7462e0 Cost0/41AF8)
| |
| | Victim Resource Owner:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| | Value:0x4679a220 Cost0/B0)
| | ---end deadlock
| | trace---
| |
| |
| |
|
|||Hi Steve,
Still one more thing here. The email of you is a no spam email address and so we can't contact you through email. However, you can reach
us by removing online from our email address here.
Viren has posted a reply here. If you have any more concerns, please feel free to reply his post or send us email. We will post back with
more information in the newsgroup.
Thanks very much.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.