sql-server - 使用 WITH (READPAST, ROWLOCK, XLOCK) 优化 Select-then-update 模式的并发解决方案

Question

假设我需要编写一个售票系统。一些门票放在池中出售。下订单时，我会更新票证记录以标记票证已绑定到订单。票单关系表如下。3张票被放入池中进行测试。

IF OBJECT_ID (N'Demo_TicketOrder', N'U') IS NOT NULL DROP TABLE [Demo_TicketOrder];
CREATE TABLE [dbo].[Demo_TicketOrder] (
  [TicketId] int NOT NULL,
  [OrderId] int NULL
    INDEX IX_OrderId_TicketId (OrderId, TicketId),
);
INSERT INTO Demo_TicketOrder VALUES (1, NULL)
INSERT INTO Demo_TicketOrder VALUES (2, NULL)
INSERT INTO Demo_TicketOrder VALUES (3, NULL)
SELECT * FROM Demo_TicketOrder

下面是我编写的将由 ASP.NET 应用程序调用的脚本。@OrderId 将作为参数从应用程序传递。出于测试目的，我将其硬编码为 1。我打开了另一个窗口，@OrderId 设置为 2。现在我可以模拟 2 个请求的并发性..

DECLARE @OrderId AS INT = 1

BEGIN TRANSACTION PlaceOrder
    BEGIN TRY
        DECLARE @ticketId AS INT;
        SELECT TOP 1 @ticketId = TicketId FROM Demo_TicketOrder WITH (READPAST, ROWLOCK, XLOCK) WHERE [OrderId] is NULL ORDER BY TicketId;
        IF @@ROWCOUNT != 1 THROW 50001, 'No tickets left!', 1;

        WAITFOR DELAY '00:00:5'; -- Simulate some delay that incurrs concurrent requests
        UPDATE Demo_TicketOrder WITH (ROWLOCK) SET [OrderId] = @OrderId WHERE [OrderId] IS NULL AND [TicketId] = @ticketId AND NOT EXISTS (SELECT 1 FROM Demo_TicketOrder WHERE OrderId = @OrderId );
        IF @@ROWCOUNT != 1
        BEGIN
            DECLARE @ErrorMessage AS NVARCHAR(MAX) = CONCAT('Optimistic lock activated! TicketId=', CAST(@ticketId AS VARCHAR(20)));
            THROW 50002, @ErrorMessage, 2;
        END
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION PlaceOrder;
        THROW
    END CATCH;      
COMMIT TRANSACTION PlaceOrder;
SELECT * FROM Demo_TicketOrder WHERE [TicketId] = @ticketId;

我的目标是让这段代码

1. 有效地处理并发请求
   这就是为什么我不能简单地这样做SELECT，UPDATE WHERE OrderId IS NULL因为当请求量增加时很多请求会失败。

2. 不允许将两个订单绑定到一张票。
   通过在 SELECT 中使用 ROWLOCK、XLOCK，我假设每个请求都会得到一张空票。此外，UPDATE 语句中仍然有一个乐观的比较和更新机制，作为锁定失败时的安全网。

3. 在处理请求时，不要阻止新的请求。
   通过使用READPAST，我希望所有新请求都将立即获得下一张可用票，而无需等待第一个请求完成 COMMIT。

4. 万一出现两个具有相同 OrderId 的请求，请确保只提供一个请求根据 UPDATE 语句
   的NOT EXISTS条件，我假设这已经完成。

为什么要问这个问题： 我自己想出了这个解决方案，因为经过广泛的搜索，我没有找到成熟的模式。但我认为这种问题很常见，这让我担心我可能过于复杂或遗漏了一些东西，因为我是 T-SQL 的新手（一直在使用 EF6）。更让我担心的是，除了反对它的建议外，我什至从未在网上看到 XLOCK 被使用过。Days 已经开始测试这段代码，到目前为止看起来还不错，但我只是想确定一下。

问题 A. 此代码是否涵盖我的目标？是否可以简化（在应用程序级别不使用排队中间件 - 那是另一回事）？

问题 B. 在测试时，我发现复合索引INDEX IX_OrderId_TicketId (OrderId, TicketId)是必要的。我不明白为什么如果我省略了 OrderId（只有 IX_TicketId），我将 - 100% 可复制 - 在第二个请求上出现死锁。

标签： sql-servertsqlconcurrencylocking

Answer 1

在我看来，这对于需要来说过于复杂了。考虑一个唯一的过滤索引，OrderId以确保订单仅分配给一张票。我希望默认的悲观并发技术可以提供足够的吞吐量（> 每秒 1K），而无需求助于READPAST：

IF OBJECT_ID (N'Demo_TicketOrder', N'U') IS NOT NULL
    DROP TABLE [Demo_TicketOrder];
CREATE TABLE dbo.Demo_TicketOrder (
      TicketId int NOT NULL
        CONSTRAINT PK_Demo_TicketOrder PRIMARY KEY NONCLUSTERED
    , OrderId int NULL
);
CREATE CLUSTERED INDEX Demo_TicketOrder_OrderId ON Demo_TicketOrder(OrderId);
CREATE UNIQUE INDEX Demo_TicketOrder_OrderId_NotNull ON Demo_TicketOrder(OrderId) WHERE OrderId IS NOT NULL;
GO

CREATE OR ALTER PROC dbo.usp_UpdateTicket
    @OrderID int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
UPDATE TOP(1) dbo.Demo_TicketOrder
SET OrderId = @OrderId
WHERE OrderID IS NULL;
IF @@ROWCOUNT = 0 THROW 50001, 'No tickets left!', 1;
GO

关于没有OrderId作为第一列的死锁，因为其中的子查询UPDATE是由OrderId所以必须在没有支持索引的情况下扫描表。当扫描遇到被另一个会话锁定的行时，它会被阻止。另一个会话在尝试执行更新时同样被阻塞，从而导致死锁。

编辑：

使用上述UPDATE TOP(1)方法未定义分配票证的顺序。没有ORDER BY这种语法的规定，但如果票是同质的，这并不重要。

如果您需要按TicketId顺序将订单分配给工单，您可以使用 CTE 或类似技术以及UPDLOCK提示（以避免死锁）并添加TicketId到聚集索引键（以有效地找到最低的 unassigned TicketId.

CREATE CLUSTERED INDEX idx_Demo_TicketOrder_OrderId_TicketId ON Demo_TicketOrder(OrderId, TicketId);
GO

CREATE OR ALTER PROC dbo.usp_UpdateTicketV2
    @OrderID int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
WITH next_available_ticket AS (
    SELECT TOP(1)
          TicketID
        , OrderId
    FROM dbo.Demo_TicketOrder AS t WITH(UPDLOCK)
    WHERE t.OrderId IS NULL
    ORDER BY t.TicketId
    )
UPDATE next_available_ticket
SET OrderId = @OrderId;
IF @@ROWCOUNT = 0 THROW 50001, 'No tickets left!', 1;
GO