I had turned on trace(1204) on my server and from the log seems like 2 SELECT statements are involved in the deadlock.
How can a select which puts only a shared lock cause this?
Any ideas ?
ResType:LockOwner Stype:'OR' Mode: S SPID:307 ECID:0 Ec
0x291B7560) Value:0x1e
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:307 ECID:0 Ec
0x291B7560) Value:0x1e
Requested By:
Input Buf: RPC Event: sp_executesql;1
SPID: 139 ECID: 0 Statement Type: SELECT Line #: 1
Owner:0x1e42da00 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:139 ECID:0
Grant List 3::
RID: 9:1:1693069:74 CleanCnt:1 Mode: X Flags: 0x2
Node:2
ResType:LockOwner Stype:'OR' Mode: S SPID:139 ECID:0 Ec
0x29357560) Value:0x1e
Requested By:
Input Buf: RPC Event: sp_executesql;1
SPID: 307 ECID: 0 Statement Type: SELECT Line #: 1
Owner:0x1dac3da0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:307 ECID:0
Grant List 2::
RID: 9:1:1782852:49 CleanCnt:1 Mode: X Flags: 0x2
Node:1
Wait-for graph
...
How can a select which puts only a shared lock cause this?
Any ideas ?
ResType:LockOwner Stype:'OR' Mode: S SPID:307 ECID:0 Ec
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:307 ECID:0 Ec
Requested By:
Input Buf: RPC Event: sp_executesql;1
SPID: 139 ECID: 0 Statement Type: SELECT Line #: 1
Owner:0x1e42da00 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:139 ECID:0
Grant List 3::
RID: 9:1:1693069:74 CleanCnt:1 Mode: X Flags: 0x2
Node:2
ResType:LockOwner Stype:'OR' Mode: S SPID:139 ECID:0 Ec
Requested By:
Input Buf: RPC Event: sp_executesql;1
SPID: 307 ECID: 0 Statement Type: SELECT Line #: 1
Owner:0x1dac3da0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:307 ECID:0
Grant List 2::
RID: 9:1:1782852:49 CleanCnt:1 Mode: X Flags: 0x2
Node:1
Wait-for graph
...