Is there any difference in atomicity and isolation between the two following SQL statements?
UPDATE Orders SET InProgress = 1 WHERE Completed = 0
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?