michaelpg
asked on
Deadlock - Key
Hi There
I have the following information for a deadlock trace:
==========================
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
KEY: 10:1662733076:1 (c60896dae9f2) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x79cd5620 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:157 ECID:0
SPID: 157 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x00D64518) Value:0x3022bda0 Cost:(0/5C)
Node:2
KEY: 10:1662733076:3 (350ae0dbc094) CleanCnt:1 Mode: S Flags: 0x0
Grant List 1::
Owner:0x302c19c0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
SPID: 130 ECID: 0 Statement Type: EXECUTE Line #: -1
Input Buf: RPC Event: sp_cursorexecute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:157 ECID:0 Ec:(0x06F84518) Value:0x5026be20 Cost:(0/5C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:157 ECID:0 Ec:(0x06F84518) Value:0x5026be20 Cost:(0/5C)
==========================
I have 2 questions, firstly the KEY for node 1 is 10:1662733076:1 BUT the KEY for node 2 is 10:1662733076:3, now i find it odd that it is a defferent index for each node, does'nt a deadlock on a key occur on the same index range? I may be completely wrong but all the other key deadlocks i have seen have been for the same index?
Secondly i would like to know what actual row or range the table this is happening on. How do i get this information? Any way to get the actual row or range that the dealock is occuring on would be appreciated.
I have a feeling poifiler is my only real solution but i do not know when the deadlock will occur, and leaving a trace runing for days is a scenario i would rather not have? Any other means to do this?
Thanx
I have the following information for a deadlock trace:
==========================
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
KEY: 10:1662733076:1 (c60896dae9f2) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x79cd5620 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:157 ECID:0
SPID: 157 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x00D64518) Value:0x3022bda0 Cost:(0/5C)
Node:2
KEY: 10:1662733076:3 (350ae0dbc094) CleanCnt:1 Mode: S Flags: 0x0
Grant List 1::
Owner:0x302c19c0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
SPID: 130 ECID: 0 Statement Type: EXECUTE Line #: -1
Input Buf: RPC Event: sp_cursorexecute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:157 ECID:0 Ec:(0x06F84518) Value:0x5026be20 Cost:(0/5C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:157 ECID:0 Ec:(0x06F84518) Value:0x5026be20 Cost:(0/5C)
==========================
I have 2 questions, firstly the KEY for node 1 is 10:1662733076:1 BUT the KEY for node 2 is 10:1662733076:3, now i find it odd that it is a defferent index for each node, does'nt a deadlock on a key occur on the same index range? I may be completely wrong but all the other key deadlocks i have seen have been for the same index?
Secondly i would like to know what actual row or range the table this is happening on. How do i get this information? Any way to get the actual row or range that the dealock is occuring on would be appreciated.
I have a feeling poifiler is my only real solution but i do not know when the deadlock will occur, and leaving a trace runing for days is a scenario i would rather not have? Any other means to do this?
Thanx
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Also i do not understand why index 3 is involved in the deadlock, as it is a 1 column key purely used for replication, the application is unaware of it, which really confuses me.???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No this is for sql2000.
These queries are application driven i do not control the locks, but i am sure they use row locks, my real concern is how to narrow down the query, but i think profiler is the way to go.
Is there anywhere to set the lock timeout for the server to a longer time period?
Thanx