CODEWITHSUNDEEP
sql-server
Ed Manet
Ed Manet

Reputation: 3178

Can I Select and Update at the same time?

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?

Upvotes: 5

Views: 9159

Answers (6)

Turnkey
Turnkey

Reputation: 9406

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.

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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

Upvotes: 8

DanDan
DanDan

Reputation: 10562

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

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.

Upvotes: 1

jkp
jkp

Reputation: 81278

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.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

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: Row locks - manually using them

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.

Upvotes: 3

Related Questions