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 Ec:(0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec:(0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/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 Ec:(0xB611B540)
Value:0x2e7feac0 Cost:(0/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 Ec:(0x2ED0F590)
Value:0x2e893d60 Cost:(0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
Value:0x2e893d60 Cost:(0/80)
End deadlock search 75296 ... a deadlock was found.
--Hi
Have you seen
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_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/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default.aspx?scid=kb;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 Ec:(0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/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
> Ec:(0xB611B540)
> Value:0x2e7feac0 Cost:(0/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 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/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 Ec:(0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/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 Ec:(0xB611B540)
> Value:0x2e7feac0 Cost:(0/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 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/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/default.asp?url=/library/en-us/trblsql/tr_servdatabse_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/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default.aspx?scid=kb;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 Ec:(0xB611B540)
> > Value:0x2e7feac0
> > Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0
> > Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> > Ec:(0x2ED0F590) Value:0x2e893d60
> > Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0
> >
> >
> > Deadlock cycle was encountered ... verifying cycle
> > Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> > Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> > Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> > Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/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
> > Ec:(0xB611B540)
> > Value:0x2e7feac0 Cost:(0/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 Ec:(0x2ED0F590)
> > Value:0x2e893d60 Cost:(0/80)
> > Victim Resource Owner:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> > Value:0x2e893d60 Cost:(0/80)
> >
> > End deadlock search 75296 ... a deadlock was found.
> > --
> >
>
>