Deadlock Issue - Cannot pinpoint resource causing contention

All,
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
Value:0x2e7feac0 Cost0/80)
Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
Value:0x2e893d60 Cost0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
Value:0x2e893d60 Cost0/80)
End deadlock search 75296 ... a deadlock was found.
Hi
Have you seen
http://msdn.microsoft.com/library/de...abse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
http://msdn.microsoft.com/library/de...netchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default...b;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540)
> Value:0x2e7feac0 Cost0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>
|||Well - I think I answered my own question after seeing other similar threads.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun.
"John" wrote:

> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0 Cost0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>
|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:

> Hi
> Have you seen
> http://msdn.microsoft.com/library/de...abse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> http://msdn.microsoft.com/library/de...netchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default...b;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
>
>