We have a site setup using MsSQL 2000 SP4, Coldfusion and IIS. We are seeing constant deadlock issues which are slowing the site and resulting in errors.
As well as the usual restarts and reboots - steps taken so far have included:
*Configured Ms SQL to only use one processor at a time - as per
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=91297
*Turned off Coldfusion global client variable updates as per
http://www.houseoffusion.com/cf_lists/messages.cfm/forumid:4/Threadid:41512#213650
*Turned on MsSQL trace for deadlock errors
The Errors are as follows:
Coldfusion:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The error occurred on line 406.
MsSQL Trace:
Deadlock encountered .... Printing deadlock information
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Wait-for graph
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:1
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:0
2005-10-11 10:39:10.53 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 1
2005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_execute;1
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x42D01500) Value:0x42bfbaa0 Cost:(0/0)
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:2
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Owner:0x42bf6540 Mode: IS Flg:0x0 Ref:1 Life:02000000 SPID:54 ECID:0
2005-10-11 10:39:10.53 spid4 SPID: 54 ECID: 0 Statement Type: SELECT Line #: 1
2005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_prepexec;1
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x445C9500) Value:0x42c296e0 Cost:(0/0)
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:3
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:0
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x445D9500) Value:0x42bf6580 Cost:(0/0)
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:6
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:0
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x42EC1500) Value:0x42c29760 Cost:(0/0)
2005-10-11 10:39:10.53 spid4 Victim Resource Owner:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x445D9500) Value:0x42bf6580 Cost:(0/0)
2005-10-11 10:39:13.73 spid4
Any help greatly Appreciated,
Regards,
Chris.
|||You might want to use Profiler and evaluate the Lock:Escalation event. I'm betting you'll find that one of your queries is trying to escalate to a table lock, which is causing the deadlock. Just a hunch... Often these kinds of issues can crop up if someone has changed an index and removed a column you need for the query to be able to use a lower-granularity lock, or if some statistics are stale. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <clubberx@.discussions.microsoft.com> wrote in message news:0f0e4727-5cb4-45c3-9fc2-8bce55e77036@.discussions.microsoft.com... Hi - Thanks for your response.>You need to post detail about your queries. Show DDL, sample data, and the two queries that >are deadlocking, and we can help you re-write them so that they don't deadlock.I am pretty sure it is not the queries - this is old code that we have used before many times with minimal changes - also, last night I tested this on one of our development servers with the same software platform (Coldfusion 7, MsSQL 2000 SP4) and we saw no deadlocks at all. Regards,Chris. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net|||
Hi - Thanks for your response.
>You need to post detail about your queries. Show DDL, sample data, and the two queries that >are deadlocking, and we can help you re-write them so that they don't deadlock.
I am pretty sure it is not the queries - this is old code that we have used before many times with minimal changes - also, last night I tested this on one of our development servers with the same software platform (Coldfusion 7, MsSQL 2000 SP4) and we saw no deadlocks at all.
Regards,
Chris.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
I have enabled profiler for
Lock:Cancel
Lock:Deadlock
Lock:Deadlock Chain
Lock:Escalation
Lock:Timeout
All I am seeing is 'Lock:Deadlock' and 'Lock:Deadlock Chain' Events,
regards,
Chris.
|||
Looking at you deadlock infomation, looks like all sessions are getting lock at the table level with session 57 having SIX lock on the table and wanting to upgrade it to X while session 54 has IS lock and waiting to acquire S lock.
Since you are not seeing any lock escalation, I think the SQL Server is choosing too coarse (in your case, a table) a locking granularity. SQL Server has some heuristic to determine the locking granularity. I believe it also depends on some statistical information on the table. I will recommend running update statistics to see if it helps.
Thanks
No comments:
Post a Comment