Steve, You are misunderstanding the purpose and necessity of locks to maintain database consistency. In the perfect database, all SQL is processed serially which completely isolates each job from the next. In a real database, that is impossible, so threads are executed in parallel and locking (the most common, but not the only form of concurrency) is implemented to provide each thread the level of isolation it needs (as determined by you, the programmer.)
I’m not intamatley famliar with Andrew’s code, but it sounds like he needs at least a “repeatable read” level of isolation which means that once his query reads a record as part of the transaction, no other thread is allowed to modify (or gain an exclusive lock) on that record until he is finished. Imagine an accounting application where a series of bank account transactions are read, totaled, and the sum is stored somewhere. It would be very bad if one of those records were updated to a new value while the first process was still running
Dropping the transaction and using a table variable does not provide that. The table variable does nothing to prevent a second thread from modifying or deleting the rows question before he updates them which is unacceptable in some scenarios.
The correct way to mark those rows as “sacred” until he is done with them is by wrapping the SQL in a transaction and allowing the DBMS to hold locks on them until they are updated. And it is exactly that behavior which is creating the deadlock since multiple threads are allowed to run the select and hold shared locks on the records (shared locks are mutually compatible). However, when each process tries to escalate their shared lock to an exclusive lock, that is where the deadlock comes in.
process A obtains shared lock on record 1
process B obtains shared lock on record 1
Process A tries to escalate to exclusive lock on record 1, but can’t due to process B’s shared lock
Process B tries to escalate to exclusive lock on record 1, but can’t due to process A’s shared lock
A deadlock has occurred!
The only fix to that is by preventing the second process from obtaining a shared lock with the intent to escalate and MS SQL server implements that in the form of an update lock which says “I’m reading now, but I’m about to update, and don’t let anyone else who is about to update read it either”.
process A obtain an update lock on record 1
process B ties to obtain an update lock on record 1, but can’t yet.
Process A escalates to exclusive lock on record 1 and updates it
Process A completes
Precess B now obtains an update lock on record 1
Process B escalates to exclusive lock on record 1 and updates it
Process B completes
And to answer your “offline” E-mail since I’m already typing this reply Andrew, the reason setting your isolation level to “serializable” does not help is because it holds shared range locks on the records you are updating (which is necessary to keep database consistency), but it will still allow OTHER threads to also obtain shared locks on the same records which is what is causing your deadlocks in the first place. Think of an update lock as an exlusive lock (because it is incompatible with another update lock) on the records you are dealing with that doesn’t let any other process place any locks on the same records IF that “other” process has the intent to update them as well.
Thanks!
~Brad