T-SQL 是更新限制原子的子查询与更新吗? [英] T-SQL Is a sub query for an Update restriction Atomic with the update?

问题描述

我在 MS Sql Server 2008 R2 中有一个简单的队列实现.这是队列的精髓:

I've got a simple queue implementation in MS Sql Server 2008 R2. Here's the essense of the queue:

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.

I want to atomically select the top n rows ordered by the priority and the id where IsBeingProcessed is false and update those rows to say they are being processed. I thought I'd use a combination of Update, Top, Output and Order By but unfortunately you can't use top and order by in an Update statement.

所以我创建了一个 in 子句来限制更新，并且该子查询按顺序执行(见下文).我的问题是，这整个语句是原子的，还是我需要将它包装在事务中?

So I've made an in clause to restrict the update and that sub query does the order by (see below). My question is, is this whole statement atomic, or do I need to wrap it in a transaction?

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:

Here's some sql to insert some dummy rows:

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 行的可能性继续更新相同的行?

If I understand the motivation for the question you want to avoid the possibility that two concurrent transactions could both execute the sub query to get the top N rows to process then proceed to update the same rows?

在这种情况下，我会使用这种方法.

In that case I'd use this approach.

;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 行.情况似乎并非如此.

I was a bit uncertain earlier whether SQL Server would take U locks when processing your version with the sub query thus blocking two concurrent transactions from reading the same TOP N rows. This does not appear to be the case.

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 锁.

Almost immediately execution stops as both concurrent transactions update the same row so the S locks taken whilst identifying the TOP 1 priority must get released before it aquires a U lock then the 2 transactions proceed to get the row U and X lock in sequence.

如果添加 CI ALTER TABLE JobsToProcess ADD PRIMARY KEY CLUSTERED (priority) 则死锁几乎立即发生，因为在这种情况下，行 S 锁没有获得释放后，一个事务在该行上获取 U 锁并等待将其转换为 X 锁，而另一个事务仍在等待转换其 S 锁到 U 锁.

If a CI is added ALTER TABLE JobsToProcess ADD PRIMARY KEY CLUSTERED (priority) then deadlock occurs almost immediately instead as in this case the row S lock doesn't get released, one transaction aquires a U lock on the row and waits to convert it to an X lock and the other transaction is still waiting to convert its S lock to a U lock.

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

If the query above is changed to use MIN rather than TOP

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

然后 SQL Server 设法从计划中完全消除子查询，并一直采用 U 锁.

Then SQL Server manages to completely eliminate the sub query from the plan and takes U locks all the way.

这篇关于T-SQL 是更新限制原子的子查询与更新吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！