Quote:
Originally Posted by itHighway
My questions are...
1) Why does SQL not figure out that query 1 is making an update and to wait a few seconds until running query 2?
2) How can I avoid these in ASP/VBScript? Is there a Try>Catch statement I can write? Is there some additional parameters I can add to the T-SQL or SP that will make the SQL server wait if the table is locked?
|
(1)
(a) Because that is the responsibility of the user. How can SQL Server tell if user 1 is going to actually *DO* anything more with in "a few seconds"??
(b) "a few seconds" is an ETERNITY in computer time frames! Maybe a few *nanoseconds* AT MOST would make sense.
(2) Several ways.
(a) do everything inside of a transaction. ADO supports transactions. You can do a ROLLBACK if the sequence does not succeed.
(b) Do everything inside a transaction inside of a Stored Procedure. *MUCH* the better route. If you can put your query 1 in a SP, why can't you put query 2 into a different SP and have it invoke SP1 first??
(c) It's a horribly ugly solution, and I wouldn't use it on ANY really busy site, but... ASP provides a way to do this that doesn't involve a DB at all (which is of course why it was created...for non-DB deadlock problems):
Code:
<%
...
Application.Lock
... do anything ...
Applcation.Unlock
...
%>
So long as all users of the same resource use Application locks, this is a guaranteed way of "serializing" access to the Application object and hence to any code at all between the lock and unlock.
Avoiding SQL Deadlocks in ASP/VBScript
