Is there any difference in atomicity and isolation between the two following SQL statements?
Batch A:
UPDATE Orders
SET InProgress = 1
WHERE Completed = 0
and
Batch B:
UPDATE Orders
SET InProgress = 1
WHERE OrderID in (SELECT OrderID FROM Orders WHERE Completed = 0)
Under the default isolation level in SQL Server of READ COMMITTED, I believe you could have concurrency issues with the batch B. After the subquery select, another process could set Completed to 1 on some of the selected rows, but batch B wouldn't know it and would still set InProgress = 1 on them.
But what about batch A? Could another process change Completed on any rows before InProgress is set to 1 on them by batch A? Or is it atomic and isolated from any other process?
Another way to ask this is, when is the update (or exclusive) lock placed on the table under READ COMMITTED isolation? In batch A, I'd assume it happens before the WHERE clause is evaluated, making the batch atomic and isolated. What about batch B? Is the update/exclusive lock placed on Orders before or after the subquery is evaluated?
Best Answer
The rows that qualify for an update are always stabilized during discovery by using (at least) U locks (Update locks, see Lock Compatibility). When is time to actually update them, the U lock is upgraded to X lock. Because of this stabilization the row cannot disappear nor can it be modified between the discovery and the update. Nor can a second UPDATE statement discover the same row: a concurrent UPDATE will have to wait for first to commit even if it only needs to 'check' the row and not actually update it. So Batch A is 100% bulletproof. For Batch B, most times the engine will understand what you mean and scan the join with update semantics (your IN clause will be rewritten as a JOIN during query normalization).
There are also differences in behavior depending on whether the plan requires Halloween Protection or not, but that only means the U locks is longer lived or not.
As a general comment the queries you show are the hallmark queries of using table row state where you should be using queues. The concurrency of
SET InProgress = 1 WHERE Completed = 0is basically 1, because it will always require a scan. An equivalent solution using queues for events to signal requests to transition state will scale basically X CPUs.