|
Posted by shark on 06/03/06 18:15
Hi all. i am facing a deadlock problem .i have included the -t1204 and
-T3605 trace flags and have got the following o/p pu tin sqls server
logs.
2006-06-01 17:49:21.84 spid4
2006-06-01 17:49:21.84 spid4 Wait-for graph
2006-06-01 17:49:21.84 spid4
2006-06-01 17:49:21.84 spid4 ...
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Victim Resource Owner:
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event:
RMCMUpdateTrades;1
2006-06-01 17:49:26.92 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
Line #: 1380
2006-06-01 17:49:26.92 spid4 Owner:0x42be8140 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:71 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:2
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:26.92 spid4 SPID: 59 ECID: 0 Statement Type: SELECT
Line #: 1167
2006-06-01 17:49:26.92 spid4 Owner:0x42be8e20 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:59 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:1
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 Wait-for graph
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ...
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:72 ECID:0 Ec:(0x45d214e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Victim Resource Owner:
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:72 ECID:0 Ec:(0x45d214e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:26.92 spid4 SPID: 59 ECID: 0 Statement Type: SELECT
Line #: 1167
2006-06-01 17:49:26.92 spid4 Owner:0x42be8e20 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:59 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:3
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:26.92 spid4 SPID: 72 ECID: 0 Statement Type: SELECT
Line #: 330
2006-06-01 17:49:26.92 spid4 Owner:0x42be84c0 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:72 ECID:0
2006-06-01 17:49:26.92 spid4 Wait List:
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:2
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event:
RMCMUpdateTrades;1
2006-06-01 17:49:26.92 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
Line #: 1380
2006-06-01 17:49:26.92 spid4 Owner:0x42be8140 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:71 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:1
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 Wait-for graph
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ...
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:69 ECID:0 Ec:(0x4583f4e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Victim Resource Owner:
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Requested By:
2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:31.93 spid4 SPID: 69 ECID: 0 Statement Type: SELECT
Line #: 330
2006-06-01 17:49:31.93 spid4 Owner:0x42bdaaa0 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:69 ECID:0
2006-06-01 17:49:31.93 spid4 Wait List:
2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:31.93 spid4 Node:3
2006-06-01 17:49:31.93 spid4
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:70 ECID:0 Ec:(0x458154e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Requested By:
2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event:
RMCMUpdateTrades;1
2006-06-01 17:49:31.93 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
Line #: 1521
2006-06-01 17:49:31.93 spid4 Owner:0x42be8140 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:71 ECID:0
2006-06-01 17:49:31.93 spid4 Grant List::
2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:31.93 spid4 Node:2
2006-06-01 17:49:31.93 spid4
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:69 ECID:0 Ec:(0x4583f4e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Requested By:
2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:31.93 spid4 SPID: 70 ECID: 0 Statement Type: SELECT
Line #: 1167
2006-06-01 17:49:31.93 spid4 Owner:0x42bdc7a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:70 ECID:0
2006-06-01 17:49:31.93 spid4 Grant List::
2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:31.93 spid4 Node:1
2006-06-01 17:49:31.93 spid4
i have two sps says sp1 and sp2 . the logic is as given below.
SP1
Begin Trans
Update table T1 where it goes for Clustered Index Seek. We'r not
updating clustered index columns in update statement
Select From table T1 where it goes for Clustered Index Scan
Update table T2
Select From table T1 where it goes for Clustered Index Scan
Update table T3
Commit Trans
SP2
Begin Trans
Update table T1 where it goes for Clustered Index Seek. We'r not
updating clustered index columns in update statement
Select From table T1 where it goes for Clustered Index Scan
Update table T2
Select From table T1 where it goes for Clustered Index Scan
Update table T3
Commit Trans
SP1 and SP2 can be executed at the same time. This then creates a
deadlock on table T1.
what i fail to understand from the log is
1. in the log it throws an exculsive lock on the select statement
..(but how can a select statement hv an X clusive lock.)
2. moreover it showws that there is a key lock .what i cannot
understand is even in the update statements of the sps i am not updaing
the fileds of the clustered index.
Thanks.
Navigation:
[Reply to this message]
|