-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.> 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.
The exclusive key lock is probably the row-level lock from the previous
uncommitted UPDATE and is not caused by updating key columns. The
subsequent SELECT statement is reported as holding the lock because it's in
the same transaction.
Scans are notorious for causing deadlocks with row-level locking. Consider
this scenario:
Session 1:
BEGIN TRAN
UPDATE T1 row A
Sesion 2:
BEGIN TRAN
UPDATE T1 row B
Session 1:
SELECT * FROM T1 --blocked when row B is encountered
Session 2:
SELECT * FROM T1 --blocked when row A is encountered, causing
deadlock
As far as addressing deadlocks, you can:
1) review your indexing strategy to prevent scans
2) specify a higher-level lock via a table hint (e.g. TABLOCK, HOLDLOCK)
3) retry following a deadlock
--
Hope this helps.
Dan Guzman
SQL Server MVP
"shark" <xavier.sharon@.gmail.com> wrote in message
news:1149358552.884323.98260@.c74g2000cwc.googlegro ups.com...
> 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.|||hi dan,
thanks for your help .
a few queries .........
you have mentioned
about
>1) review your indexing strategy to prevent scans
how do i do this?do u mean that i should reconsider the columns that i
use in clustered index?
will using an index hint help in this case?
thanks once again .
Dan Guzman wrote:
> > 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.
> The exclusive key lock is probably the row-level lock from the previous
> uncommitted UPDATE and is not caused by updating key columns. The
> subsequent SELECT statement is reported as holding the lock because it's in
> the same transaction.
> Scans are notorious for causing deadlocks with row-level locking. Consider
> this scenario:
> Session 1:
> BEGIN TRAN
> UPDATE T1 row A
> Sesion 2:
> BEGIN TRAN
> UPDATE T1 row B
> Session 1:
> SELECT * FROM T1 --blocked when row B is encountered
> Session 2:
> SELECT * FROM T1 --blocked when row A is encountered, causing
> deadlock
> As far as addressing deadlocks, you can:
> 1) review your indexing strategy to prevent scans
> 2) specify a higher-level lock via a table hint (e.g. TABLOCK, HOLDLOCK)
> 3) retry following a deadlock
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||shark (xavier.sharon@.gmail.com) writes:
> thanks for your help .
> a few queries .........
> you have mentioned
> about
>>1) review your indexing strategy to prevent scans
> how do i do this?do u mean that i should reconsider the columns that i
> use in clustered index?
That and non-clustered indexes. Since you did not post tables or the
actual statements, it is of course impossible for us here to suggest
anything.
Also, when you review indexing, you cannot only to this with this
particular deadlock in mind, but you do of course need to consider
other queries.
> will using an index hint help in this case?
Impossible to tell from this distance, but generally you should avoid
index hints, and only use them as a last resort.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
No comments:
Post a Comment