tsql - 为什么原子语句需要锁提示？

Question

问题

对以下语句应用锁有什么好处？

同样，如果我们不包含这些提示，我们会​​看到什么问题？也就是说，它们是防止竞争条件、提高性能还是其他什么？询问它们可能是为了防止一些我没有考虑过的问题，而不是我假设的竞争条件。

注意:这是此处提出的问题的溢出:SQL Threadsafe UPDATE TOP 1 for FIFO Queue

有问题的陈述

WITH nextRecordToProcess AS
(
    SELECT TOP(1) Id, StatusId
    FROM    DemoQueue
    WHERE   StatusId = 1 --Ready for processing
    ORDER BY DateSubmitted, Id 
)
UPDATE nextRecordToProcess
SET StatusId = 2 --Processing
OUTPUT Inserted.Id 

要求

• SQL 用于从队列中检索未处理的记录。
• 要获取的记录应该是队列中状态为就绪(StatusId = 1)的第一条记录。
• 可能有多个工作进程/ session 处理来自该队列的消息。
• 我们要确保队列中的每条记录仅被拾取一次(即由单个工作人员)，并且每个工作人员按照消息在队列中出现的顺序处理消息。
• 一个 worker 比另一个 worker 工作得更快是可以的(即，如果 worker A 拿起记录 1，然后 worker B 拿起记录 2，如果 worker B 在 worker A 完成处理记录 1 之前完成记录 2 的处理是可以的)。我们只关心获取记录的上下文。
• 没有正在进行的交易；即我们只想从队列中取出记录；在我们回来将状态从 Processing 进展到 Processed 之前，我们不需要保持锁定状态。

上下文的附加 SQL:

CREATE TABLE Statuses
(
    Id SMALLINT NOT NULL PRIMARY KEY CLUSTERED
    , Name NVARCHAR(32) NOT NULL UNIQUE
)
GO
INSERT Statuses (Id, Name)
VALUES (0,'Draft')
, (1,'Ready')
, (2,'Processing')
, (3,'Processed')
, (4,'Error')
GO
CREATE TABLE DemoQueue
(
    Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , StatusId SMALLINT NOT NULL FOREIGN KEY REFERENCES Statuses(Id)
    , DateSubmitted DATETIME --will be null for all records with status 'Draft'
)
GO

建议声明

在各种讨论队列的博客中，以及引起该讨论的问题中，建议将上述语句更改为包含如下锁提示:

WITH nextRecordToProcess AS
(
    SELECT TOP(1) Id, StatusId
    FROM    DemoQueue WITH (UPDLOCK, ROWLOCK, READPAST)
    WHERE   StatusId = 1 --Ready for processing
    ORDER BY DateSubmitted, Id 
)
UPDATE nextRecordToProcess
SET StatusId = 2 --Processing
OUTPUT Inserted.Id 

我的理解

我知道需要锁定这些提示的好处是:

• UPDLOCK:因为我们正在选择记录来更新它的状态，所以我们需要确保在我们读取它之后但在我们更新它之前读取该记录的任何其他 session 将无法读取记录更新它的意图(或者更确切地说，这样的声明必须等到我们执行更新并释放锁，然后其他 session 才能看到我们的记录及其新值)。
• ROWLOCK:当我们锁定记录时，我们希望确保我们的锁定只影响我们锁定的行；即因为我们不需要锁定很多资源/我们不想影响其他进程/我们希望其他 session 能够读取队列中的下一个可用项目，即使该项目与我们锁定的记录在同一页面中.
• READPAST:如果另一个 session 已经在读取队列中的项目，而不是等待该 session 释放它的锁定，我们的 session 应该选择队列中的下一个可用(未锁定)记录。

即如果我们运行下面的代码，我认为这是有道理的:

DECLARE @nextRecordToProcess BIGINT

BEGIN TRANSACTION

SELECT TOP (1) @nextRecordToProcess = Id
FROM    DemoQueue WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE   StatusId = 1 --Ready for processing
ORDER BY DateSubmitted, Id 

--and then in a separate statement

UPDATE DemoQueue
SET StatusId = 2 --Processing
WHERE Id = @nextRecordToProcess

COMMIT TRANSACTION

--@nextRecordToProcess is then returned either as an out parameter or by including a `select @nextRecordToProcess Id`

However when the select and update occur in the same statement I'd have assumed that no other session could read the same record between our session's read & update;因此不需要显式锁定提示。

我是否从根本上误解了锁的工作原理？或者这些提示的建议是否与其他一些相似但不同的用例相关？

Answer 1

John 是对的，因为这些是优化，但在 SQL 世界中，这些优化可能意味着“快速”与“难以忍受的数据大小缓慢”之间的区别和/或“有效”与“不可用”之间的区别死锁困惑'。

readpast 提示很清楚。对于另外两个，我觉得我需要添加更多的上下文:

• ROWLOCK 提示是为了防止页面锁定粒度扫描。锁定粒度(行与页)在查询开始时预先确定，并基于对查询将扫描的页数的估计(第三种粒度，表，仅在特殊情况下使用，不适用于此处).通常，出列操作永远不必扫描如此多的页面，以便引擎考虑页面粒度。但是当引擎决定使用页面锁定粒度时，我看到过“在野外”的情况，这会导致出队中的阻塞和死锁

• 需要 UPDLOCK 来防止升级锁死锁情况。 UPDATE 语句在逻辑上分为搜索需要更新的行然后更新行。搜索需要锁定它评估的行。如果该行符合条件(满足 WHERE 条件)，则更新该行，并且更新始终是独占锁。所以问题是你如何在搜索过程中锁定行？如果您使用共享锁，则两个 UPDATE 将查看同一行(它们可以，因为共享锁允许它们)，两者都确定该行符合条件，并且都尝试将锁升级为独占 -> 死锁。如果在搜索过程中使用排他锁，则不会发生死锁，但是 UPDATE 将在与任何其他读取评估的所有行上发生冲突，即使该行不符合条件(更不用说排他锁不能提前释放 w/o breaking two-phase-locking ).这就是为什么有一个 U 模式锁，它与 Shared 兼容(这样候选行的 UPDATE 评估不会阻塞读取)但与另一个 U 不兼容(这样两个 UPDATE 就不会死锁)。典型的基于 CTE 的出队需要这个提示有两个原因:

  1. 因为是 CTE，查询处理并不总是理解 CTE 内的 SELECT 是 UPDATE 的目标，应该使用 U 模式锁和
  2. 出队操作将始终在更新相同的行(行被“出队”)之后进行，因此经常发生死锁。