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