By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

deadlock problem

P: n/a
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.

Jun 3 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
> 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" <xa***********@gmail.com> wrote in message
news:11*********************@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.

Jun 4 '06 #2

P: n/a
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


Jun 5 '06 #3

P: n/a
shark (xa***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.