Atomic update with joins in SQL

Question

I've got a table that tracks "checked out" objects, but objects live in various other tables. The goal is to allow users to check out objects that match their criteria such that a given object can only be checked out once (i.e. no two users should be able to check out the same object). In some cases, a single object may span multiple tables, requiring the use of joins to check for all the user's criteria (in case that matters).

Here's a very simple example query (hopefully you can infer the schema):

update top (1) Tracker
set IsCheckedOut = 1
  from Tracker t
  join Object o on t.ObjectId = o.Id
  join Property p on p.ObjectId = o.Id
  where t.IsCheckedOut = 0
  and o.SomePropertyColumn = 'blah'
  and p.SomeOtherPropertyColumn = 42

Due to the from subquery, I suspect that this query is not atomic and therefore two users requesting the same flavor of object at the same time can end up checking out the same object.

Is that true? And if so, how do I fix this?

I thought about adding an output DELETED.* clause and having the user retry their query if the returned value of the IsCheckedOut column is 1, which I think would work (correct me if I'm wrong)... But I'd like to get something where the user doesn't have to worry about retries.

EDIT

For a thorough explanation see SqlZim's answer below, but for this simple case I can just add the hints directly to the query posted above:

update top (1) Tracker
set IsCheckedOut = 1
  from Tracker t (updlock, rowlock, readpast)
  join Object o on t.ObjectId = o.Id
  join Property p on p.ObjectId = o.Id
  where t.IsCheckedOut = 0
  and o.SomePropertyColumn = 'blah'
  and p.SomeOtherPropertyColumn = 42

Mind fleshing that out a bit more concretely with the given example? I'll accept it as the answer if it turns out to be correct. — gzak, Feb 26 '17 at 09:31

SqlZim · Accepted Answer · 2017-02-26T17:53:19.580

0

Using a transaction and some table hints for locking, we can grab just one row and hold it to be updated.

declare @TrackerId int;

begin tran;

select top 1 @TrackerId = TrackerId
  from Tracker t with (updlock, rowlock, readpast)
    inner join Object o on t.ObjectId = o.Id
    inner join Property p on p.ObjectId = o.Id;
  where t.IsCheckedOut = 0
    and o.SomePropertyColumn = 'blah'
    and p.SomeOtherPropertyColumn = 42;

if @TrackerId is not null 
begin;
update Tracker
  set IsCheckedOut = 1
  where TrackerId = @TrackerId;
end;

commit tran

updlock places an update lock on the row from our select. Other transaction will not be able to update or delete the row but they are allowed to select it, however a concurrent select trying to aquire an update lock on this row (i.e. another run of this procedure from a difference process with the same search criteria) will not be able to select this particular row, it can however select and lock the next row because we are also using readpast.
rowlock tries to only lock the specific row we are going to update, instead of a page or table lock.
readpast skips rows that have row-level locks.

References:

alternate one step code using a common table expression:

begin tran;

  with cte as (
    select top 1 
        t.*
      from Tracker t with (updlock, rowlock, readpast)
        inner join Object o 
          on t.ObjectId = o.Id
        inner join Property p 
          on p.ObjectId = o.Id;
      where t.IsCheckedOut = 0
        and o.SomePropertyColumn = 'blah'
        and p.SomeOtherPropertyColumn = 42
      --order by TrackerId asc /* optional order by */
  )
  update cte
    set IsCheckedOut = 1
    output inserted.*;

commit tran;

edited Feb 26 '17 at 17:53

answered Feb 26 '17 at 15:07

SqlZim

37,248
6
41
59

Would it be correct to just add those hints to my `from` query as is and leave the overall query the same (i.e. don't rewrite it as two separate statements)? – gzak Feb 26 '17 at 17:33
Oh I forgot to mention, I need an `output` clause anyway because I also want to return the checked out row to the user – gzak Feb 26 '17 at 17:44
@gzak Yes, you could also write it using a cte – SqlZim Feb 26 '17 at 17:46
I was thinking even simpler, just take exactly the query I posted and add the hints to it directly on the `from` line without any other changes - would that still work? – gzak Feb 26 '17 at 19:23
Isn't every statement implicitly wrapped in a transaction? Or does it need to be a non-default transaction type? – gzak Feb 27 '17 at 04:56
@gzak I would put it in its own explicit transaction to make sure locks are released promptly. If this was called as part of a larger transaction it could hold locks longer than necessary. I think you are referring to autocommit transactions, as implicit transactions are different. https://technet.microsoft.com/en-us/library/ms187878(v=sql.105).aspx – SqlZim Feb 27 '17 at 05:19
Ah yes, in a larger overall transaction that makes good sense. In this particular scenario, that's the whole query, there are no other steps. – gzak Feb 27 '17 at 06:11

Atomic update with joins in SQL

1 Answers1

Linked