代码之家 › 专栏 › 技术社区 › JMarsch

数据库中的原子比较和交换

oracle database sql-server sql

JMarsch · 技术社区 · 15 年前

我正在研究一个工作排队的解决方案。我想查询数据库中的一个给定行,其中一个状态列有一个特定的值,修改该值并返回该行,我想按原子方式执行,这样其他查询就看不到它:


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)

两个或多个并发查询必须不可能返回行(一个查询执行将看到该行,而其状态为y)--有点像联锁的compareandexchange操作。

我知道上面的代码(对于sql server)运行,但是交换是否总是原子的?

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

4 回复 | 直到 15 年前

Remus Rusanu 15 年前

pk是主键吗?那么这就不是问题了,如果你已经知道主键就没有运动了。如果PK 是主键,这就引出了一个显而易见的问题怎样你知道要出列的项目的主键吗…

问题是如果你不要知道主键并希望将下一个“可用”(即status=y)出列,并将其标记为出列(删除它或设置status=z)。

正确的方法是使用一个语句。不幸的是,oracle和sql server的语法不同。SQL Server语法为:

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

我对oracle的returning子句还不够熟悉,无法给出一个类似于sql输出语句的示例。

其他sql server解决方案要求select上的锁提示(使用updlock)是正确的。在Oracle中,首选的方法是使用for update,但这在SQL Server中不起作用,因为for update将与SQL中的游标一起使用。

无论如何,你在原帖中的行为是不正确的。多个会话都可以选择同一行,甚至全部更新它,将相同的退行项返回给多个读取器。

Adrian Pronk 15 年前

作为一般规则,要进行这种原子操作,您需要确保在执行select时设置一个独占(或更新)锁,以便在更新之前没有其他事务可以读取行。

典型的语法如下:

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

但你得查一下才能确定。

Matt Wrock 15 年前

我有一些应用程序遵循类似的模式。有一张桌子和你的一样,代表一个工作队列。该表有两个额外的列:thread_id和thread_date。当应用程序请求队列中的工作时,它会提交一个线程ID。然后,一个UPDATE语句用提交的ID更新线程ID列,用当前时间更新线程日期列中的所有适用行。更新之后,它将选择具有该线程ID的所有行。这样就不需要声明显式事务。“锁定”发生在初始更新中。

thread_date列用于确保不会以孤立的工作项结束。如果从队列中提取项目,然后应用程序崩溃,会发生什么情况?你必须有能力再次尝试这些工作项。因此,您可以从队列中获取尚未标记为已完成但已分配给线程的所有项目,该线程的日期在过去很长一段时间。由你来定义“遥远”。

Rob Garrison 15 年前

试试这个。验证在update语句中。

代码

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'

结果

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

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