6

if we have an UPDATE with a sub-SELECT, can the subquery execute concurrently or not under READ COMMITTED isolation?

In other words, is there a race condition present in the following:

update list set [state] = 'active' 
where
    id = (select top 1 id from list where [state] = 'ready' order by id)

In yet other words, if many connections are simulataneously executing this SQL, can we guarantee that one row is in fact updated per invocation (so long as rows in 'ready' state exist)?

The Dag
  • 1,811
  • 16
  • 22
  • Shoving multiple operations into a single statement does not help at all with concurrency. Statements provide no concurrency-related guarantees. – usr May 09 '14 at 10:56
  • That's incorrect. Shared locks are taken and released for one SELECT statement at a time under READ COMMITTED. But I am not sure what order locks are taken for the example. I'm also quite happy to put begin tran and commit around it, though I am quite sure that makes no difference since a transaction can never be any less than a single statement. – The Dag May 09 '14 at 11:03
  • 1
    No. READ COMMITTED releases locks asap, often after each row. For pages that do not have uncommitted data it does not even take locks at all. This is a little-known optimization.; "I'm also quite happy to put begin tran and commit around it" it is another misconception that this would change anything. In auto-commit mode statements still run under an implicit transaction. You're right in that regard. – usr May 09 '14 at 11:05
  • I wonder why the MSDN doc then states so unequivocally "Because shared locks [in REPEATABLE READ] are held to the end of a transaction instead of being released AT THE END OF EACH STATEMENT [emphasis mine], concurrency is lower than the default READ COMMITTED isolation level." – The Dag May 09 '14 at 11:23
  • 1
    This is a statement about REPEATABLE READ which takes S-locks forever to ensure that data is stable. READ COMMITTED does not guarantee stable data. It guarantees almost nothing. – usr May 09 '14 at 11:31
  • http://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level Great series on this site about locking. – usr May 09 '14 at 11:36
  • Yes of course it is written under the REPEATABLE READ section - that's why I ADDED that to the quote, in square brackets and all. The point is that it explicitly says shared locks are released at the end of each statement under READ COMMITTED. I see your linked resource claims otherwhise tho. – The Dag May 09 '14 at 12:07
  • Oh now I see what you mean. Try this: Scan a 1TB table under RC and use sp_locks midway to see that only a tiny amount of locks exist. No S-lock at the table-level. Or, read the series I linked to. It is very good.; Yes, the MSDN statement is misleading. A documentation bug. – usr May 09 '14 at 12:08
  • You can also see each and every lock taken using SQL Profiler. – usr May 09 '14 at 12:09
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/52388/discussion-between-the-dag-and-usr) – The Dag May 09 '14 at 12:10

1 Answers1

2

The answer is yes, there is a race condition and two transactions may execute the subquery concurrently, leading to the same row being subsequently updated twice.

This can be fixed by rewriting the update as

update TEMP 
set [state] = 'active' 
from 
    (select top 1 * from list where [state] = 'ready' order by id) TEMP

I frankly don't know why this should be different, but it is. SQL Server will now take update locks ("intent to update") when executing the subquery, preventing concurrent transactions from picking the same row.

The Dag
  • 1,811
  • 16
  • 22