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 子句来限制更新，并且子查询执行 order by(见下文).我的问题是，这整个语句是原子的，还是需要将其包装在事务中?

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屋！