If I have a query like so:
SET Status='RESERVED', MachineName='me'
FROM Tasks t
INNER JOIN (SELECT TOP 100 * FROM Tasks WHERE Status='QUEUED' AND MachineName='') t2
ON t.ID = t2.ID
Basically, I have multiple machines running this query, competing to grab tasks and reserve them for themselves (and the 'me' string changes according to the name of the machine doing the query). The machines are trying to reserve tasks - and once a row gets reserved, I cannot allow any other machine to be allowed to modify that row.
I'm wondering if the last WHERE clause (WHERE t.MachineName='') is evaluated after the inner join is complete. In other words - I am only allowing the row to update as long as the MachineName column is empty.
Technically, if this query run simultaneously as the same query on another machine, the 2 updates can complete
1- Does this do what I want and the last WHERE is evaluated just before that row is worked on, in which case that row is "protected" from double modification.. or
2- the last WHERE is included in the join somehow and the row can be modified by 2 machines?
Thanks for your help!