Deadlocking Question

I have a ColdFusion web application running on a SQL Server 7.0 back-end.

Within this application, there is are three queries in a row where the second query deadlocks 1-2 times a day, which is too high. The queries do the following:

1. Insert member details from a web form into member table
2. Select ID (key) of what was just inserted into the member table
3. Update a third table with the member ID

As I said earlier, the 2nd query is the one that I see deadlocked in the ColdFusion error logs. I am unable to replicate the problem, so I have not been able to troubleshoot using the procedures described in SQL-BOL (unless I am mis-understanding the documentation).

This sequence runs an average of 150 times per day, but it can be anywhere from 100 to 500 times, so the failure rate is about 1%.

Any ideas on why this is happening and what I can do to prevent it?

Thanks,
CybermudA spid that does only one thing can't deadlock, it isn't possible.

When a spid accesses an object, it normally takes a lock (of some kind) on that object.

When a spid has an exclusive lock on an object, and another spid tries to access that object, the new spid is "blocked". When a spid is blocked, it stops executing until the object that is causing the blocking becomes available again.

When two spids are running (lets call them 69 and 70), spid 69 locks object A, spid 70 locks object B and everything is still happy. Then 69 attempts to lock object B, but it becomes blocked because 70 already has it locked. Then 70 tries to lock object A, which causes it to be blocked and now we have a deadlock! Both spids are blocked, waiting for each other. SQL Server detects this condition, and picks one of the deadlocked spids as the "victim" and automagically kills the victim (allowing the other spid to proceed).

The best way to avoid deadlocks is to keep your locks small. Don't lock objects for long periods of time. When you do have to lock objects, try to always lock them in the same sequence so that blocks rarely become deadlocks.

If you set Trace flag 1204 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_646r.asp), you'll get more diagnostic information about the cause of the deadlock in your SQL errorlog file.

-PatP|||Pat:

Thanks for the reply. I do not understand what you mean when you say

"A spid that does only one thing can't deadlock, it isn't possible."

Are you saying that since there is only one Cold Fusion application, there will only be one spid? I do understand the principles of deadlocking but can't seem to figure out how to apply them to my situation.

Also, is there any performance hit from leaving trace flag 1204 on for an extended period of time?

Thanks,
Cybermud|||If you think about what a deadlock is, an spid with only one object locked CAN'T deadlock. In order for a deadlock to occur, you must already have one object locked, then try to lock another.

Spid usage depends on how your ColdFusion engine is configured, but typically it will support many threads (therefore many spids). R937 would be able to answer this kind of question much better than I can, although he might prefer you to post it in the ColdFusion (http://www.dbforums.com/f223) forum.

While traceflag 1204 used to impose some significant overhead, I don't believe that is the case anymore. I'd go ahead and run it for a while, but watch for any signs of server distress (just in case!).

-PatP|||Pat:

Thanks for the quick reply. I think I am understanding it correctly...that sequence of the three queries can't be causing a deadlock by itself, because its only one spid, right?

That means there is something else going on...and I will be taking this to the ColdFusion forum.

I also plan on leaving flag 1204 on for the night to see if it turns anything up. Thanks again for all your help.

Cybermud

Deadlock Issue when dropping/creating tables

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

Quote:

Originally Posted by DWiggin

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...

Deadlock Condition

We are working on VB 6.0 as front-end tool and sqlserver 7.0 as back-end.
Currently we have started experiencing deadlock condition mostly when we are firing the update statements.
We haev tried closing all recordsets after using them and setting them to 'Nothing', but hasn't helped.
The number of users has nothing to do with this problem experienced.
As sometimes even with around 65-70 users we don't have this and sometimes even 1-2 people working on the network experience this.Hi Geeta,
It doesnt matter whether 50 users use the system simultaneously or no ... A deadlock can even occur when there are 2 users in the system. It depends on what tables of the database are being used and for what purpose.It is very likely that there are long running queries which are holding locks on the table while another user is either trying to query or update the same table.
There can be many reasons as to why a query/update suddenly starts running slowly all of a sudden... the simples reasons can be that the table size has grown a lot larger than what it used to be or there can be external factors like CPU being used by another process which keeps the SQL server process to starve...
You will have to be very specific as to when u observe the deadlocks ...esp because u are saying that they dont occur all the time.
It will be really nice if you can provide more information
Cheers
Sachin|||Oohhh, your problem is such general, that only general statements can be made.

Are you using DAO of ADO of ADO.NET, or are using Java or Borland technology?

First of all, I'm not sure whether you have a deadlock situation at all. In a deadlock situation, two transactions started, and one will be forced to roll-back. I guess, you have simply a locking problem, which occurs when you are updating a record, and a second process wants to read it.

Second thought: such a locking problem can een happen within 1 program, running by one user! I had that problem with two concurrent threads. So, the number of concurrent users isn't really an issue, if you have designed your application properly.

I don't have my old sources right here, but i remember that I had to set a kind of WaitForTransaction timeout, which was by default 0.|||Hi,

I am using ADO technology.
Please tell me more about what needs to be added into
the code so that I can get rid of this problem.

Thanks.

Geeta|||BOL:

Minimizing Deadlocks

Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

Rolled back, undoing all the work performed by the transaction.
Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks:

Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.

dbSeeChanges Problem

I have successfully been using this audit trail process on my .mdb back-end. I have now upsized my back-end to SQL, and low and behold I get an error on the following in my audit module;

The error is 3622, and insists I need to use the "dbSeeChanges" option on the "OpenRecordSet"... I've looked at this till I'm blue in the face - can ANYONE help me out here?
:eek:

Dim DB As DAO.Database
Dim sSQL As String

Set DB = DBEngine(0)(0)

If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
DB.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
DB.Execute sSQL, dbFailOnError
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
DB.Execute sSQL, dbFailOnError
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
DB.Execute sSQL, dbFailOnError
End If
AuditEditEnd = TrueTry:

DB.Execute sSQL, dbSeeChanges|||I did that, and it worked great..... but don;t I need dbFailOnError as another option as well? How do I show multiple options??