sql-server – T-SQL是更新限定Atomic的子查询吗？

我在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'');

办理要领

在那种环境下,我会行使这种要领.

;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 
                   )

然后SQL Server想法完全消除打算中的子查询并一向回收U锁.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!