|
Posted by Dan Guzman on 09/29/01 11:49
> 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.googlegroups.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.
>
Navigation:
[Reply to this message]
|