atomic compare and swap in a database

Question

I am working on a work queueing solution. I want to query a given row in the database, where a status column has a specific value, modify that value and return the row, and I want to do it atomically, so that no other query will see it:

begin transaction
select * from table where pk = x and status = y
update table set status = z where pk = x
commit transaction
--(the row would be returned)

it must be impossible for 2 or more concurrent queries to return the row (one query execution would see the row while its status = y) -- sort of like an interlocked CompareAndExchange operation.

I know the code above runs (for SQL server), but will the swap always be atomic?

I need a solution that will work for SQL Server and Oracle

Answer 1

As a general rule, to make an operation like this atomic you'll need to ensure that you set an exclusive (or update) lock when you perform the select so that no other transaction can read the row before your update.

The typical syntax for this is something like:

 select * from table where pk = x and status = y for update

but you'd need to look it up to be sure.

Answer 2

Is PK the primary key? Then this is a non issue, if you already know the primary key there is no sport. If pk is the primary key, then this begs the obvious question how do you know the pk of the item to dequeue...

The problem is if you don't know the primary key and want to dequeue the next 'available' (ie. status = y) and mark it as dequeued (delete it or set status = z).

The proper way to do this is to use a single statement. Unfortunately the syntax differs between Oracle and SQL Server. The SQL Server syntax is:

update top (1) [<table>]
set status = z 
output DELETED.*
where  status = y;

I'm not familiar enough with Oracle's RETURNING clause to give an example similar to SQL's OUTPUT one.

Other SQL Server solutions require lock hints on the SELECT (with UPDLOCK) to be correct. In Oracle the preffered avenue is use the FOR UPDATE, but that does not work in SQL Server since FOR UPDATE is to be used in conjunction with cursors in SQL.

In any case, the behavior you have in the original post is incorrect. Multiple sessions can all select the same row(s) and even all update it, returning the same dequeued item(s) to multiple readers.