Solved

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

Posted on 2015-02-20
13
102 Views
Last Modified: 2015-02-23
Hi Experts,

With SQL Server, how does one do something like this?

Update tasks set Status=1 WHERE Status=0 ORDER BY otherColumn LIMIT 1

I've seen this done as a with statement with a select- but this doesn't seem to lock the row - and I need to lock the row...

Thanks,
Mike
0
Comment
Question by:thready
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:OriNetworks
Comment Utility
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

Open in new window

0
 
LVL 1

Author Comment

by:thready
Comment Utility
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?
0
 
LVL 1

Author Comment

by:thready
Comment Utility
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 :)
0
 
LVL 17

Expert Comment

by:OriNetworks
Comment Utility
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

Open 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.
0
 
LVL 1

Author Comment

by:thready
Comment Utility
Oh this sounds good.  Now I'll read it ten times and try to understand (not that you weren't very clear)... THANK YOU!
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:thready
Comment Utility
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....
0
 
LVL 17

Expert Comment

by:OriNetworks
Comment Utility
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

Open 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.
0
 
LVL 1

Author Comment

by:thready
Comment Utility
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

Open 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
0
 
LVL 1

Author Comment

by:thready
Comment Utility
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.... ?
0
 
LVL 17

Accepted Solution

by:
OriNetworks earned 500 total points
Comment Utility
If you leave out the table hints the update will still use rowlocks and i think as long as the rows are less than 5000 it will use rowlocks. If you try to update more than that it will locks the entire table. Also all columns in the where clause must be indexed or rowlock wont be used. By leaving out the table hint in the select, the current machine may also run into this locking situation while waiting for open transactions to continue.

The locking type can be tested by running begin tran, run the update  and instead of running commit, open activity monitor to see what locks exist, then after making sure no table locks are open, you can commit or rollback the transaction. As for analyzing what each transaction type is, thats a whole different question you can feel free to open if you need to but a simple google search should be simple enough. Im sure experts may have other solutions as well but my opinion is that there is not only 1 correct solution in this case.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
This is a duplicate question, please add comments here:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28621288.html
0
 
LVL 1

Author Closing Comment

by:thready
Comment Utility
Thank you very much for all your time and answers, much appreciated.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now