Can I Select and Update at the same time?

Question

This is an over-simplified explanation of what I'm working on. I have a table with status column. Multiple instances of the application will pull the contents of the first row with a status of 'NEW', update the status to 'WORKING' and then go to work on the contents. It's easy enough to do this with two database calls; first the SELECT then the UPDATE. But I want to do it all in one call so that another instance of the application doesn't pull the same row. Sort of like a SELECT_AND_UPDATE thing.

Is a stored procedure the best way to go?

Answer 1

Not quite, but you can SELECT ... WITH (UPDLOCK), then UPDATE.. subsequently. This is as good as an atomic operation as it tells the database that you are about to update what you previously selected, so it can lock those rows, preventing collisions with other clients. Under Oracle and some other database (MySQL I think) the syntax is SELECT ... FOR UPDATE.

Note: I think you'll need to ensure the two statements happen within a transaction for it to work.

Answer 2

Sounds like a queue processing scenario, whereby you want one process only to pick up a given record.

If that is the case, have a look at the answer I provided earlier today which describes how to implement this logic using a transaction in conjunction with UPDLOCK and READPAST table hints: http://stackoverflow.com/questions/2295385/row-locks-manually-using-them/2295437#2295437

Best wrapped up in sproc.

I'm not sure this is what you are wanting to do, hence I haven't voted to close as duplicate.

Answer 3

You could use the OUTPUT statement.

DECLARE @Table TABLE (ID INTEGER, Status VARCHAR(32))
INSERT INTO @Table VALUES (1, 'New')
INSERT INTO @Table VALUES (2, 'New')
INSERT INTO @Table VALUES (3, 'Working')

UPDATE  @Table
SET     Status = 'Working'
OUTPUT  Inserted.*
FROM    @Table t1
        INNER JOIN (
          SELECT  TOP 1 ID 
          FROM    @Table
          WHERE   Status = 'New'
        ) t2 ON t2.ID = t1.ID

Answer 4

You should do three things here:

1. Lock the row you're working on
2. Make sure that this and only this row is locked
3. Do not wait for the locked records: skip the the next ones instead.

To do this, you just issue this:

SELECT  TOP 1 *
FROM    mytable (ROWLOCK, UPDLOCK, READPAST)
WHERE   status = 'NEW'
ORDER BY
        date

UPDATE  …

within a transaction.

Answer 5

A stored procedure is the way to go. You need to look at transactions. Sql server was born for this kind of thing.

Answer 6

Yes, and maybe use the rowlock hint to keep it isolated from the other threads, eg.

UPDATE
Jobs WITH (ROWLOCK, UPDLOCK, READPAST)
SET Status = 'WORKING'
WHERE JobID =
(SELECT Top 1 JobId FROM Jobs WHERE Status = 'NEW')

EDIT: Rowlock would be better as suggested by Quassnoi, but the same idea applies to do the update in one query.