Atomic comparison and swap in the database

Question

I am working on a queue solution. I want to query the given row in the database where the status column has a specific value, change that value and return the row, and I want to do this atomically so that no other query sees 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) 

for two or more concurrent queries, it should not be possible to return a string (a single query execution will see a string, and its status = y) - sort of like a CompareAndExchange lock operation.

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

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

Answer 1

Is PK the primary key? Then this is not a problem, if you already know the primary key, there is no sport. If pk is the primary key, then the obvious question arises, how do you know that the pk of an element is deactivated ...

The problem is that you do not know the primary key and want to delete the next “available” (that is, status = y) and mark it as dequeued (delete it or set status = z).

The right way to do this is to use a single statement. Unfortunately, the syntax is different between Oracle and SQL Server. SQL Server Syntax:

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

I'm not good enough at the Oracle RETURNING clause to give an example similar to SQL OUTPUT one.

Other SQL Server solutions require proper SELECT lock indications (with UPDLOCK). In Oracle, the preferred aspect is using FOR UPDATE, but this does not work in SQL Server, since FOR UPDATE must be used with cursors in SQL.

In any case, the behavior that you have in the original message is incorrect. Several sessions can select the same row (s), and even update all of them, returning the same elements (s) for several readers.

Answer 2

Typically, to perform an operation such as this atom, you need to make sure that you set an exceptional (or update) lock when making the selection so that no other transaction can read the line before your update.

The typical syntax for this is something like:

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

but you need to watch it to be sure.

Answer 3

I have some applications that follow a similar pattern. There is a table like yours, which is a work queue. The table has two additional columns: thread_id and thread_date. When an application requests a working job in a queue, it sends a thread identifier. Then, one update statement updates all applicable rows with the stream identifier column with the identifier provided and the stream date column with the current time. After this update, he selects all rows with this stream identifier. This way you do not need to declare an explicit transaction. A “lock” occurs during the initial update.

The thread_date column is used to ensure that you are not getting lost work items. What happens if you pull items out of the queue and then your application crashes? You should be able to try these work items again. Thus, you can capture all the elements from the queue that were not marked as completed, but were assigned a thread with a thread date in the distant past. It is up to you to define "remote."

Answer 4

Try it. Validation is performed in the UPDATE statement.

the 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