| Groups | Blog | Home |
sql server (alternate) : deadlock problem
-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.
[quoted text, click to view]
> 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 [quoted text, click to view] "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::
hi dan,
thanks for your help . a few queries ............ you have mentioned about [quoted text, click to view] >1) review your indexing strategy to prevent scans how do i do this?do u mean that i should reconsider the columns that iuse in clustered index? will using an index hint help in this case? thanks once again . [quoted text, click to view] 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:
[quoted text, click to view] > 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. [quoted text, click to view] > 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/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |