Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Feb 4 '13 at 19:38

This question came from our site for professional and enthusiast programmers.

1 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 = 0 is 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.

share|improve this answer
    
I also recommend reading the links in Craig's post linked above, specially blogs.msdn.com/b/craigfr/archive/2007/08/15/… and blogs.msdn.com/b/bartd/archive/2006/07/27/680518.aspx –  Remus Rusanu Feb 4 '13 at 18:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.