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

Question

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

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

注意：这是此处提出的问题的溢出：FIFO 队列的 SQL 线程安全 UPDATE TOP 1

有问题的声明

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）的第一条记录。
• 可能有多个工作进程/会话处理来自该队列的消息。
• 我们希望确保队列中的每个记录仅被拾取一次（即由单个工作人员），并且每个工作人员按照消息在队列中出现的顺序处理消息。
• 一个工作人员比另一个工作人员工作得更快是可以的（即，如果工作人员 A 获取记录 1，然后工作人员 B 获取记录 2，如果工作人员 B 在工作人员 A 完成处理记录 1 之前完成记录 2 的处理，则可以）。我们只关心获取记录的情况。
• 没有正在进行的交易；即我们只想从队列中取出记录；在我们回来处理状态之前，我们不需要将其保持锁定状态Processing to 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：因为我们选择记录来更新其状态，所以我们需要确保在我们读取该记录之后但在更新之前读取该记录的任何其他会话将无法读取该记录更新它（或者更确切地说，这样的语句必须等到我们执行更新并释放锁后，其他会话才能看到我们的记录及其新值）。
• ROWLOCK：当我们锁定记录时，我们希望确保我们的锁只影响我们锁定的行；即，因为我们不需要锁定许多资源/我们不想影响其他进程/我们希望其他会话能够读取队列中的下一个可用项目，即使该项目与我们锁定的记录位于同一页面中。
• READPAST：如果另一个会话已经从队列中读取一个项目，我们的会话应该选择队列中的下一个可用（未锁定）记录，而不是等待该会话释放其锁定。

也就是说，如果我们运行下面的代码，我认为这是有意义的：

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`

然而，当选择和更新发生在同一个语句中时，我会假设没有其他会话可以在我们的会话的读取和更新之间读取相同的记录；所以不需要显式的锁定提示。

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

John 是对的，因为这些是优化，但在 SQL 世界中，这些优化可能意味着“快速”与“难以忍受的数据大小慢”之间的差异和/或“有效”与“无法使用的死锁混乱”之间的差异。

读过去的提示很清楚。对于另外两个，我觉得我需要添加更多背景信息：

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

• 需要UPDLOCK来防止升级锁死锁的情况。 UPDATE 语句在逻辑上分为搜索需要更新的行，然后更新这些行。搜索需要锁定它评估的行。如果该行符合条件（满足 WHERE 条件），则该行将被更新，并且更新始终是独占锁。那么问题是如何在搜索过程中锁定行？如果您使用共享锁，那么两个 UPDATE 将查看同一行（它们可以，因为共享锁允许它们），都决定该行是否合格，并且都尝试将锁升级为独占 -> 死锁。如果您在搜索期间使用排它锁，则不会发生死锁，但是 UPDATE 将在与任何其他读取评估的所有行上发生冲突，即使该行不符合条件（更不用说排它锁无法在不破坏的情况下提前释放）两相锁定）。这就是为什么存在 U 模式锁，一种与 Shared 兼容（以便候选行的 UPDATE 求值不会阻塞读取）但与另一个 U 不兼容（以便两个 UPDATE 不会死锁）的原因。基于 CTE 的典型出队需要此提示有两个原因：

  1. 因为是 CTE，所以查询处理并不总是理解 CTE 内的 SELECT 是 UPDATE 的目标，并且应该使用 U 模式锁，并且
  2. 出队操作将始终在相同的行之后进行更新（“出队”的行），因此死锁很常见。