update with select followed by where clause

Hi Experts,

If I have a query like so:
UPDATE Tasks
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
WHERE t.MachineName=''

Open in new window


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!
Mike
LVL 1
threadyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Shouldn't be a problem, as the WHERE clause will filter the entire set, insuring that no rows are updated only WHERE t.MachineName <> ''

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
threadyAuthor Commented:
I do trust you, but is there a way I can prove this to my superiors?  This is a concern...
threadyAuthor Commented:
I have two colleagues that say otherwise.  I'm still with you however.
grendel777Commented:
Update locking is a cornerstone of relational databases, so Jim is correct. You can do a quick web search to pull up some explanations, though you may need to translate them for laymen. If they're still unconvinced, you could have the job run on one machine, handing a set of tasks to each sub-machine in turn, but note that you'll need to update this code whenever you add or remove a sub-machine:
UPDATE t
	SET Status='RESERVED', MachineName='machine1'
FROM Tasks t 
	INNER JOIN (SELECT TOP 100 * FROM Tasks WHERE Status='QUEUED' AND MachineName='') t2 
		ON t.ID = t2.ID
WHERE t.MachineName=''
--	---------------------------------
GO
UPDATE t
	SET Status='RESERVED', MachineName='machine2'
FROM Tasks t 
	INNER JOIN (SELECT TOP 100 * FROM Tasks WHERE Status='QUEUED' AND MachineName='') t2 
		ON t.ID = t2.ID
WHERE t.MachineName=''
--	---------------------------------
GO...

Open in new window


Here's a link with lock info: http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
threadyAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.