SQL Server - how to Update limited number of rows, ordered by certain criteria, atomically

Explain to us how you can UPDATE rows with an ORDER BY clause.
You'll probably need to provide us a before and after data mockup here.

You can put your order by in a subquery and join that back to the main table. WITH ROWLOCK hint with force rowlock

UPDATE tasks WITH (ROWLOCK)
	SET status=0
	FROM tasks t
	inner join (SELECT TOP 1 * from tasks ORDER BY otherColumn) t2
	on t.RECORD_ID =t2.RECORD_ID

Select allOpen in new window

that's what I was trying to get the syntax of, thank you OriNetworks...  And I realized I had LIMIT 1 in my question - but it was supposed to be LIMIT 10 (or maybe 1000)...

My concern with this is - if I have multiple machines executing this query at the same time (and this will happen), I will have to re-run these queries because of some kind of deadlock errors?

The true query will be something like this (I guess):
UPDATE tasks WITH (ROWLOCK)
      SET status=0, machineName='m1'
      FROM tasks t
      inner join (SELECT TOP 1000 * from tasks ORDER BY otherColumn) t2
      on t.RECORD_ID =t2.RECORD_ID

I guess I can expect some problems if I have say, ten machines doing this query at about the same time?  Deadlocks?

So that's why I was interested in something atomic.. I know I'm asking for a lot here.....  Just hoping for some nice magic before the weekend :)

with a rowlock will only lock the specific rows and release the lock after it is complete. You will want to want to customize the subquery to filter rows you want to update.

UPDATE tasks WITH (ROWLOCK)
	SET status=0
	FROM tasks t WITH (NOLOCK)
	inner join (SELECT TOP 1 * from tasks WITH (NOLOCK) WHERE Status=1 ORDER BY otherColumn) t2
	on t.RECORD_ID =t2.RECORD_ID

Select allOpen in new window

I havent tested this last one but I used NOLOCK on the select in order to 'dirty read' any records that may simultaneously be already updated but uncommitted. The subquery is also filtered to limit what records will be updated, which is also uncommitted read so it wont try to update rows a different machines is already updating.

Oh this sounds good.  Now I'll read it ten times and try to understand (not that you weren't very clear)... THANK YOU!

I think this dirty data returned from the select breaks what I'm trying to do.  If some other machine selects the same rows - and then my machine does the same select, we'll both update those same rows.  Unless I'm missing something, I'm not sure this works...  :-(

Basically, I'm just trying to reserve 1000 rows for a given machine- once they're reserved, no one else can steal them....

You can further prevent locks by filtering where machinename has not be updated yet.

UPDATE tasks WITH (ROWLOCK)
	SET status=0, machineName='m'
	FROM tasks t WITH (NOLOCK)
	inner join (SELECT TOP 1 * from tasks WITH (NOLOCK) WHERE Status=1 and machineName is null ORDER BY otherColumn) t2
	on t.RECORD_ID =t2.RECORD_ID
        WHERE machineName is null

Select allOpen in new window

First, we have the subquery which will look for rows that have not been updated (status=1 and machinename is null). If a different server is already updating one of more of these rows, the NOLOCK hint will also the query to read this 'dirty data' if it is not yet written to the table. The update will then lock the row so other machines cannot update it, updates the matching row while also validating machineName is still null to make sure it isn't overwriting a change that somehow got in there.

I guess the downside of this method is that it's possible that zero rows get reserved - and at the very least, I have an undefined number of reserved rows.  Let me ask you this - If I use the default locks and just do this, then the read committed default takes over:

UPDATE tasks
      SET status='RESERVED', machineName='m'
      FROM tasks t
      inner join (SELECT TOP 1000 * from tasks WHERE Status=null and machineName is null ORDER BY CreationTime) t2
      on t.RECORD_ID =t2.RECORD_ID
/*        WHERE machineName is null                is this line even needed?    */

What happens here?  Do these block and everything just works and I get up to 1000 rows (it there are at least 1000 rows in the database?)  I think if this is the case, then this is what I want.

I have no idea how to verify how this will be treated with sample data.  I saw how I could use transactions to do the following:

I could start transaction, do the select and basically session2 would just wait.. But how can I verify how the same concurrent update is treated?  i.e., will the other always wait to start like a critical section (if read committed is the isolation level)?

Is the above update with the inner join on the select similar to the way the following transactions would be treated?

session 1                                 session 2
-------------------                          --------------
Start transaction                    Start transaction
Do a select                               Do a select
do an update                          do an update
commit                                     commit

Select allOpen in new window

So sorry for all these questions... Just really hoping to get this going.. You will basically be saving my life.

Thanks a lot,
Mike

Basically, I'm wondering if UPDATE tasks (inner join select...) will be treated as a transaction since it's one statement (or is it considered two?)

.... and since the NOLOCK is NOT specified, I get what's expected, at the cost of more blocking.... ?

This is a duplicate question, please add comments here:
https://www.experts-exchange.com/questions/28621288/SQL-Server-Update-order-by-limit.html

Thank you very much for all your time and answers, much appreciated.