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.

score 20 · Accepted Answer · answered Apr 20 '11 at 06:38

20

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.

answered Apr 20 '11 at 06:38

Dalex

3,585
19
25

1

+1. Learned something new today :). This will return the record just before updating it – tobias86 Apr 20 '11 at 06:42
Thanks a lot. this is awesome. Exactly what I was looking for. BTW if anyone is interested this works in SQL2005 and up. – lahsrah Apr 20 '11 at 06:48
1

This doesn't just do a single record though as specified in the question. – Martin Smith Apr 20 '11 at 06:55
1

@Martin: I modified the first row to achieve that: UPDATE TOP (1) [table] – lahsrah Apr 20 '11 at 07:05

score 7 · Answer 2 · answered Apr 20 '11 at 06:46

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

score 4 · Answer 3 · answered Apr 20 '11 at 06:31

4

This should do the trick

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

SQL 2008 should take care of any locking for you.

answered Apr 20 '11 at 06:31

tobias86

4,979
1
21
30

1

I also need to select that record. – lahsrah Apr 20 '11 at 06:33
So what needs to happen first? Do you want to select it first and then update, or vice versa? – tobias86 Apr 20 '11 at 06:35

score 1 · Answer 4 · answered Apr 16 '14 at 17:17

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

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

4 Answers4