0

It seems that this topic is asked often, and I think I found an answer to my original question, but now I'm curious about a difference between that (and a few other answers I found) and my SQL:

update Foos set Owner = 'me' OUTPUT INSERTED.id where Owner is null and id in
  (select top 1 id from Foos where Owner is null)

I now understand that my original hypothesis is correct regarding the sub-select and concurrency, where a concurrent thread may select the same id as another thread is about to update that id. However, does my where clause in the update piece help prevent this race condition (in read committed level)?

My theory is that, while two threads may get the same id from the sub-select, only one would be able to update, because the update is atomic and includes the condition. The other thread would fail, or update zero records. Is that true?

Community
  • 1
  • 1
Josh
  • 6,944
  • 8
  • 41
  • 64
  • Oops it looks like maybe this should have been posted on dba? Never knew that existed... – Josh Sep 29 '14 at 16:23
  • In addition to your concurrency concerns you are doing a top 1 with no order by. You may not always get the same 1 unless you specify an order. – Sean Lange Sep 29 '14 at 16:30

1 Answers1

0

Try this

;WITH CTE AS 
 (
  SELECT ID, [Owner] 
     , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) rn 
  FROM Foos
  WHERE [Owner] IS NULL
 ) 
update CTE 
  SET [Owner] = 'me' 
OUTPUT INSERTED.id 
WHERE rn = 1
M.Ali
  • 67,945
  • 13
  • 101
  • 127