How to Select a record from the database and update it in an atomic query

Question

I have a number of records in a table with a Status column and I want to select a single record where Status = Pending and in the same atomic query mark it as Status = InProcess. What's the best way to do that?

This is needed because multiple queries can be running at the same time trying to process these records and I don't want two threads to be picking up the same record to process.

Answer 1

You can use OUTPUT clause:

UPDATE [table]
SET Status = 'InProcess'
OUTPUT deleted.*
WHERE Status = 'Pending'

Here you can use inserted table name if you want to get row with new status or deleted when old.

Answer 2

Here is an article about Using tables as Queues.

With this table create table T (ID int identity, Status varchar(15)) Something like this should keep you safe from deadlocks.

;with cte as
(
  select top 1 *
  from T with (rowlock, readpast)
  where Status = 'Pending'
  order by ID
)
update cte
set Status = 'InProcess'
output inserted.ID, inserted.Status