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

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 开发者_JS百科process.

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.

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

This should do the trick

UPDATE [table]
SET Status = 'InProcess'
WHERE Status = 'Pending'

SQL 2008 should take care of any locking for you.

This following is kind of a hack, but it worked for me for atomic read/updates:

declare temp1, temp2, ...;
update table
   set temp1=column1,
       temp2=column2, ... 
       column1=expression1,
       column2=expression2, ...
where conditions;
select temp1, temp2, ...;