atomic compare and swap in a database

Question

Hello:

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

I have some applications that follow a similar pattern. There is a table like yours that represents a queue of work. The table has two extra columns: thread_id and thread_date. When the app asks for work froom the queue, it submits a thread id. Then a single update statement updates all applicable rows with the thread id column with the submitted id and the thread date column with the current time. After that update, it selects all rows with that thread id. This way you dont need to declare an explicit transaction. The "locking" occurs in the initial update.

The thread_date column is used to ensure that you do not end up with orphaned work items. What happens if items are pulled from the queue and then your app crashes? You have to have the ability to try those work items again. So you might grab all items off the queue that have not been marked completed but have been assigned to a thread with a thread date in the distant past. Its up to you to define "distant."

Answer 2

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 3

Try this. The validation is in the UPDATE statement.

Code

IF EXISTS (SELECT * FROM sys.tables WHERE name = 't1')
    DROP TABLE dbo.t1
GO
CREATE TABLE dbo.t1 (
    ColID       int         IDENTITY,
    [Status]    varchar(20)
)
GO

DECLARE @id             int
DECLARE @initialValue   varchar(20)
DECLARE @newValue       varchar(20)

SET @initialValue = 'Initial Value'

INSERT INTO dbo.t1 (Status) VALUES (@initialValue)
SELECT @id = SCOPE_IDENTITY()

SET @newValue = 'Updated Value'

BEGIN TRAN

UPDATE dbo.t1
SET
    @initialValue = [Status],
    [Status]      = @newValue
WHERE ColID    = @id
  AND [Status] = @initialValue

SELECT ColID, [Status] FROM dbo.t1

COMMIT TRAN

SELECT @initialValue AS '@initialValue', @newValue AS '@newValue'

Results

ColID Status
----- -------------
    1 Updated Value

@initialValue @newValue
------------- -------------
Initial Value Updated Value

Answer 4

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.