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