Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have?) and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec0x5A2E5578)
Value:0x52fa6780 Cost0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have?) and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec0x5A2E5578)
Value:0x52fa6780 Cost0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robertsql

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

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...
>
>