代码之家 › 专栏 › 技术社区 › Daniel James Bryars

T-SQL是带有更新的更新限制原子的子查询?

sql-server-2008-r2 tsql sql-server

Daniel James Bryars · 技术社区 · 16 年前

我在mssqlserver2008r2中有一个简单的队列实现。以下是排队的要点:

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
)

所以我做了一个in子句来限制更新,子查询执行order by(见下文)。我的问题是,这整个语句是原子的,还是需要用事务来包装它?

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

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

2 回复 | 直到 16 年前

Martin Smith 15 年前

如果我理解问题的动机,您希望避免两个并发事务同时执行子查询以获取要处理的前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

测试脚本(在两个并发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

S 在识别 TOP 1 priority 必须在它获得一个锁定,然后两个事务继续获取行 U型 和 X 按顺序锁定。

Heap

ALTER TABLE JobsToProcess ADD PRIMARY KEY CLUSTERED (priority) 然后死锁几乎立即发生,就像在本例中 S公司 锁没有释放,一个事务获取一个 U型 锁定行并等待将其转换为 十 锁和另一个事务仍在等待转换其锁定到 U型

Clustered Index

如果上面的查询更改为使用 MIN 而不是 TOP

WHERE priority = (SELECT MIN(priority)
                   FROM JobsToProcess 
                   WHERE isprocessed=0 
                   )

然后,SQL Server设法从计划中完全消除子查询,并 U型 一直锁着。

enter image description here

Philip Kelley 16 年前

顺便说一下,查看用于检索一组项的排名函数,特别是row_number()。语法可能有点笨拙,但总的来说,它们是灵活和强大的工具。(在讨论这些问题和答案的下面,有一大堆的问题和答案。)