在数据库中进行原子比较和交换 [英] atomic compare and swap in a database

问题描述

我正在开发工作排队解决方案。我想查询数据库中的给定行，其中状态列具有特定值，修改该值并返回该行，并且我想以原子方式执行，以便其他查询不会看到它：

 
  
开始事务
 select * from table where pk = x and status = y 
 update表集状态= z其中pk = x 
提交事务
  - （将返回该行）
  
 

它必须是不可能的2个或更多的并发查询返回行（一个查询执行将看到该行，而其状态= y） - 类似一个互锁的CompareAndExchange操作。 / p>

我知道上面的代码运行（对于SQL服务器），但是交换总是原子的？

需要一个适用于SQL Server和Oracle的解决方案

解决方案

PK是主键吗？然后这是一个非问题，如果你已经知道主键没有运动。如果pk 是主键，那么这提出了一个明显的问题： 你知道该项目的pk出队...

问题是如果你不知道主键，并想要出队下一个可用（即status = y），并将其标记为出列（删除它或set status = z）。

这样做的正确方法是使用单个语句。不幸的是，Oracle和SQL Server之间的语法不同。 SQL Server语法为：

 更新top（1）[< table>] 
 set status = z 
 output DELETED。* 
其中status = y; 
 



我不太熟悉Oracle的RETURNING子句来给出类似于SQL的OUTPUT的例子。

其他SQL Server解决方案需要SELECT（使用UPDLOCK）的锁定提示才能正确。
在Oracle中，优先的大道是使用FOR UPDATE，但是在SQL Server中不起作用，因为FOR UPDATE将与SQL中的游标一起使用。

无论如何，您在原始帖子中的行为不正确。多个会话可以选择相同的行，甚至全部更新它，将相同的出列项目返回给多个读者。

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

解决方案

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.

这篇关于在数据库中进行原子比较和交换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！