Geek Answers Handbook

Can I select and update at the same time?

This is an overly simplistic explanation of what I'm working on.
I have a status table. Several instances of the application will download the contents of the first line with the status NEW, update the status to WORKING, and then proceed to work on the contents.
This is quite simple to do with two database queries; first SELECT, then UPDATE. But I want to do all this in one call so that another instance of the application does not pull the same line. Sounds like a thing SELECT_AND_UPDATE.

Is a stored procedure the best way?

+5
source share
6 answers

, , .

, , , , UPDLOCK READPAST: -

sproc.

, , , .

+2

OUTPUT.

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
+8

, SELECT ... WITH (UPDLOCK), UPDATE.. . , , , , , , . Oracle (MySQL, ) SELECT ... FOR UPDATE.

. , , , .

+1

:

  • , .
  • , .
  • : .

:

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

UPDATE  …

.

+1

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

+1
source

Yes, and maybe use the rowlock hint to isolate it from other threads, for example.

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

EDIT: Rowlock will be better as suggested by Quassnoi, but the same idea applies for updating in a single request.

+1
source

More articles:


All Articles