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.
No, at least the nested select statement can be processed before the update is started and locks are acquired. To make sure that no other query interferes with this update it is required to set the transaction isolation level to SERIALIZABLE.
This article (and the series it is part of) explains very well the subtleties of concurrency in SQL server:
http://sqlperformance.com/2014/02/t-sql-queries/confusion-caused-by-trusting-acid