Deadlock Issues

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. See the following if you need help providing DDL and sample data: http://www.aspfaq.com/etiquette.asp?id=5006 -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <clubberx@.discussions.microsoft.com> wrote in message news:41ed2763-84cf-4fa2-94d9-6c611f19ff0a@.discussions.microsoft.com...Hi,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 errorsThe 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 information2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Wait-for graph2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Node:12005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-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:02005-10-11 10:39:10.53 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 12005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_execute;12005-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:22005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-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:02005-10-11 10:39:10.53 spid4 SPID: 54 ECID: 0 Statement Type: SELECT Line #: 12005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_prepexec;12005-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:32005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-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:02005-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:62005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-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:02005-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.Hi,
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

|||Hi,

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

dbms_lob conversion

hi,
am interested in porting some of my oracle quries to sqlserver. Here is a sample oracle procedure:
create procedure sp1(ac_clob clob)
as
n number;
position number;
begin
position := 4;
n := dbms_lob.instr(ac_clob,'test', position); --returns the location of the substring
n: = dbms_lob.getlength(ac_clob); --returns the length of the clob object
end;
As the clob object comes as a parameter let me know if it can be converted in sql server. Also I some other functions to dbms_lob packages like: dbms_lob.copy, dbms_lob.write, dbms_lob.trim,Hi,

SQL Server does not have the exact same functionality as the DBMS_LOB package. For example, there is no direct I/O between files and blob variables, and blob variables are not allowed as local variables. You need to use BULK INSERT and/or chunk-mode reads and writes from TSQL. ADO as a Stream class that makes this much easier from a client app.

In SQL2K we do support some functions on text columns (like CLOBs in Oracle) from the TSQL level, including DATALENGTH(), PATINDEX(), and SUBSTRING().

See:
Managing ntext, text, and image Data
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8orl.asp

In SQL 2005, we've introduced a new feature called "large-value types", which allow you to treat CLOBS just like normal string values. You can declare local variables, use them in expressions, pass them as parameters, return them from functions, cast to/from XML, and so on. They have become a first-class data type in the TSQL language. Their declarations look like

varchar(max), nvarchar(max), varbinary(max)

It's really a great SQL 2005 feature, and it makes life *much* easier when dealing with CLOBS and BLOBs from within TSQL.

Regards,
Clifford Dibble
Program Manager, SQL Server|||Thanks for your suggestions|||In addition to what Clifford says, you can take a look at the CHARINDEX, PATINDEX and SUBSTRING functions which give you the functionality you have in your sample code. These should work in both SQL 2000 and SQL 2005.

- Christian