CODEWITHSUNDEEP
sqlt-sqlsql-server-2008selectatomic
lahsrah
lahsrah

Reputation: 9173

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 process.

Upvotes: 20

Views: 7440

Answers (4)

user3542243
user3542243

Reputation: 25

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, ...;

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 8

Dalex
Dalex

Reputation: 3625

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.

Upvotes: 20

tobias86
tobias86

Reputation: 5029

This should do the trick

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

SQL 2008 should take care of any locking for you.

Upvotes: 4

Related Questions