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?
(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):
... do anything ...
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.