Is a transaction that only updates a single table always isolated?

Question

According to the UPDATE documentation, an UPDATE always acquires an exclusive lock on the whole table. However, I am wondering if the exclusive lock is acquired before the rows to be updated are determined or only just before the actual update.

My concrete problem is that I have a nested SELECT in my UPDATE like this:

UPDATE Tasks
SET Status = 'Active'
WHERE Id = (SELECT TOP 1 Id 
            FROM Tasks
            WHERE Type = 1
                AND (SELECT COUNT(*) 
                     FROM Tasks 
                     WHERE Status = 'Active') = 0
            ORDER BY Id)

Now I am wondering whether it is really guaranteed that there is exactly one task with Status = 'Active' afterwards if in parallel the same statement may be executed with another Type:

UPDATE Tasks
SET Status = 'Active'
WHERE Id = (SELECT TOP 1 Id 
            FROM Tasks
            WHERE Type = 2           -- <== The only difference
                AND (SELECT COUNT(*) 
                     FROM Tasks 
                     WHERE Status = 'Active') = 0
            ORDER BY Id)

If for both statements the rows to change would be determined before the lock is acquired, I could end up with two active tasks which I must prevent.

If this is the case, how can I prevent it? Can I prevent it without setting the transaction level to SERIALIZABLE or messing with lock hints?

From the answer to Is a single SQL Server statement atomic and consistent? I learned that the problem arises when the nested SELECT accesses another table. However, I'm not sure if I have to care about this issue if only the updated table is concerned.

Answer 1

If you want exactly one task with static = active, then set up the table to ensure this is true. Use a filtered unique index:

create unique index unq_tasks_status_filter_active on tasks(status)
    where status = 'Active';

A second concurrent update might fail, but you will be ensured of uniqueness. Your application code can process such failed updates, and re-try.

Relying on the actual execution plans of the updates might be dangerous. That is why it is safer to have the database do such validations. Underlying implementation details could vary, depending on the environment and version of SQL Server. For instance, what works in a single threaded, single processor environment may not work in a parallel environment. What works with one isolation level may not work with another.

EDIT:

And, I cannot resist. For efficiency purposes, consider writing the query as:

UPDATE Tasks
    SET Status = 'Active'
    WHERE NOT EXISTS (SELECT 1
                      FROM Tasks
                      WHERE Status = 'Active'
                     ) AND
          Id = (SELECT TOP 1 Id 
                FROM Tasks
                WHERE Type = 2           -- <== The only difference
                ORDER BY Id
               );

Then place indexes on Tasks(Status) and Tasks(Type, Id). In fact, with the right query, you might find that the query is so fast (despite the update on the index) that your worry about current updates is greatly mitigated. This would not solve a race condition, but it might at least make it rare.

And if you are capturing errors, then with the unique filtered index, you could just do:

UPDATE Tasks
    SET Status = 'Active'
    WHERE Id = (SELECT TOP 1 Id 
                FROM Tasks
                WHERE Type = 2           -- <== The only difference
                ORDER BY Id
               );

This will return an error if a row already is active.

Note: all these queries and concepts can be applied to "one active per group". This answer is addressing the question that you asked. If you have a "one active per group" problem, then consider asking another question.