Is a transaction that only updates one table always isolated?

Question

According to the UPDATE documentation , UPDATE always gets an exclusive lock for the entire table. However, I am wondering if an exclusive lock will be obtained before the rows to be updated are determined or only before the actual update.

My specific 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'm wondering if it is really guaranteed that there is exactly one Task with Status = 'Active' subsequently if the same statement can be executed with a different type in parallel:

 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, row changes are detected before the lock is obtained, I can complete two active tasks that I must prevent.

If so, how can I prevent it? Can I prevent it by not setting the transaction level to SERIALIZABLE or messing around with lock hints?

From the answer to Is a single SQL Server statement atomic and sequential? I found out that the problem occurs when a nested SELECT accesses another table. However, I am not sure that I need to take care of this problem if it is only an updated table.

Answer 1

No, at least the nested select statement can be processed before the update is launched and locks are received. To ensure that no other request interferes with this update, you must set the transaction isolation level to SERIALIZABLE .

This article (and its series) explains very well the intricacies of concurrency in SQL Server:

http://sqlperformance.com/2014/02/t-sql-queries/confusion-caused-by-trusting-acid

Answer 2

If you want exactly one task with static = active, then tune the table to make sure it is true. Use filtered unique index:

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

The second parallel update may fail, but you will be sure of uniqueness. Application code can handle such failed updates and try again.

Using real update plans can be dangerous. This is why it is safer to have such a database. Key implementation details may vary by environment and version of SQL Server. For example, 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 can not resist. To improve performance, consider writing a 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 the indexes on Tasks(Status) and Tasks(Type, Id) . In fact, with the right request, you may find that the request is so fast (despite updating the index) that your concern about current updates is greatly alleviated. This would not solve the state of the race, but it could at least make it rare.

And if you commit errors, then using a unique filtered index, you can simply 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 result in an error if the row is already active.

Note. All of these queries and concepts can be applied to "one active for each group." This answer solves the question you asked. If you have a “one active for each group” problem, try asking another question.

Answer 3

This is not the answer to your question ... But your request is a pain for my eyes :)

 ;WITH cte AS ( SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id) FROM Tasks ) UPDATE cte SET [Status] = 'Active' WHERE RowNum = 1 AND [type] = 1 AND NOT EXISTS( SELECT 1 FROM Tasks WHERE [Status] = 'Active' )