我在MS sql Server 2008 R2中有一个简单的队列实现.这是队列的本质：
CREATE TABLE ToBeProcessed
(
Id BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,[Priority] INT DEFAULT(100) NOT NULL,IsBeingProcessed BIT default (0) NOT NULL,SomeData nvarchar(MAX) NOT null
)
我想原子地选择按优先级排序的前n行和IsBeingProcessed为false的id,并更新这些行以表示它们正在被处理.我以为我会使用Update,Top,Output和Order By的组合,但不幸的是你不能在Update语句中使用top和order by.
所以我创建了一个in子句来限制更新,并且子查询按顺序执行(见下文).我的问题是,这整个语句是原子的,还是我需要将它包装在一个事务中？
DECLARE @numberToProcess INT = 2
CREATE TABLE #IdsToProcess
(
Id BIGINT NOT null
)
UPDATE
ToBeProcessed
SET
ToBeProcessed.IsBeingProcessed = 1
OUTPUT
INSERTED.Id
INTO
#IdsToProcess
WHERE
ToBeProcessed.Id IN
(
SELECT TOP(@numberToProcess)
ToBeProcessed.Id
FROM
ToBeProcessed
WHERE
ToBeProcessed.IsBeingProcessed = 0
ORDER BY
ToBeProcessed.Id,ToBeProcessed.Priority DESC)
SELECT
*
FROM
#IdsToProcess
DROP TABLE #IdsToProcess
这是一些插入一些虚拟行的sql：
INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N'');
解决方法
如果我理解问题的动机,你想避免两个并发事务都可以执行子查询以获得前N行进行处理然后继续更新相同行的可能性？
在那种情况下,我会使用这种方法.
;WITH cte As
(
SELECT TOP(@numberToProcess)
*
FROM
ToBeProcessed WITH(UPDLOCK,ROWLOCK,READPAST)
WHERE
ToBeProcessed.IsBeingProcessed = 0
ORDER BY
ToBeProcessed.Id,ToBeProcessed.Priority DESC
)
UPDATE
cte
SET
IsBeingProcessed = 1
OUTPUT
INSERTED.Id
INTO
#IdsToProcess
我之前有点不确定sql Server是否会在使用子查询处理您的版本时使用U锁,从而阻止两个并发事务读取相同的TOP N行.这似乎不是这种情况.
测试表
CREATE TABLE JobsToProcess ( priority INT IDENTITY(1,1),isprocessed BIT,number INT ) INSERT INTO JobsToProcess SELECT TOP (1000000) 0,0 FROM master..spt_values v1,master..spt_values v2
测试脚本(在2个并发SSMS会话中运行)
BEGIN TRY
DECLARE @FinishedMessage VARBINARY (128) = CAST('TestFinished' AS VARBINARY (128))
DECLARE @SynchMessage VARBINARY (128) = CAST('TestSynchronising' AS VARBINARY (128))
SET CONTEXT_INFO @SynchMessage
DECLARE @OtherSpid int
WHILE(@OtherSpid IS NULL)
SELECT @OtherSpid=spid
FROM sys.sysprocesses
WHERE context_info=@SynchMessage and spid<>@@SPID
SELECT @OtherSpid
DECLARE @increment INT = @@spid
DECLARE @number INT = @increment
WHILE (@number = @increment AND NOT EXISTS(SELECT * FROM sys.sysprocesses WHERE context_info=@FinishedMessage))
UPDATE JobsToProcess
SET @number=number +=@increment,isprocessed=1
WHERE priority = (SELECT TOP 1 priority
FROM JobsToProcess
WHERE isprocessed=0
ORDER BY priority DESC)
SELECT *
FROM JobsToProcess
WHERE number not in (0,@OtherSpid,@@spid)
SET CONTEXT_INFO @FinishedMessage
END TRY
BEGIN CATCH
SET CONTEXT_INFO @FinishedMessage
SELECT ERROR_MESSAGE(),ERROR_NUMBER()
END CATCH
几乎立即执行停止,因为两个并发事务都更新同一行,因此在识别TOP 1优先级时采用的S锁必须在获取U锁之前释放,然后2个事务继续按顺序获得行U和X锁.
如果添加了CI ALTER TABLE JobsToProcess ADD PRIMARY KEY CLUSTERED(优先级),那么死锁几乎立即发生,因为在这种情况下,行S锁没有被释放,一个事务获取行上的U锁并等待将其转换为一个X锁,另一个事务仍在等待将其S锁转换为U锁.
如果上面的查询更改为使用MIN而不是TOP
WHERE priority = (SELECT MIN(priority)
FROM JobsToProcess
WHERE isprocessed=0
)
