sql-server - SQL Server 中的并发读-修改-写

Question

我想用 SQL 做:

• 第一步:从表A、B、C中读取数据
• 第 2 步:进行一些计算
• 将数据写回表A、B、C

但这必须是并发证明，这意味着一旦第 1 步完成，所有其他实例都需要等待第 1 步，直到第 3 步完成，因为它会更改数据来进行计算。这是一个简化的示例(我省略了一些声明并使用了硬编码值):

CREATE PROCEDURE AddOrder AS BEGIN
    -- Step 1: read (every other call to AddOrder should wait here until this procedure has finished
    SELECT @TotalOrderAmount = sum(Amount) FROM Orders WHERE CustomerID = 5

    -- Step 2: modify
    SELECT @DiscountPct = CASE WHEN @TotalOrderAmount > 1000.00 THEN 0.10 ELSE 0.00 END
    SELECT @Amount = 9.99 * (1 - @DiscountPct)

    -- Step 3: write
    INSERT INTO Orders(CustomerID, Amount) VALUES (5, @Amount)
END

我想到的第一件事当然是使用具有更高隔离级别的事务:

SET TRANSACTION ISOLATION LEVEL REPEATBLE READ
BEGIN TRAN
    -- Step 1
    -- Step 2
    -- Step 3
COMMIT TRAN

但这并不能解决任何问题。假设 2 个连接同时执行该过程。第 1 步将放置并持有一个 shared_read 锁，并且两个连接都将通过已经错误的第一步。但更糟的是，由于在步骤 3 中将更新的表上有 2 个锁，因此会出现死锁。

我不想将所有内容组合到一个语句中(如果这能解决任何问题)，因为我的真实情况当然比示例更复杂。

我还想使用现代 SQL Server 的范围锁定而不是锁定整个表，这样只有该 CustomerID 的行才会被锁定。最后，我不会乐观锁定，所以这两个调用应该总是成功。

有没有人有解决这个问题的简单方法？

更新:

起初，似乎使用表提示 UPDLOCK 可以解决问题。例如:

    BEGIN TRAN
        -- Step 1: read or wait until other instance has finished
        SELECT @TotalOrderAmount = sum(Amount) FROM Orders with (UPDLOCK, ROWLOCK) WHERE CustomerID = 5

        -- Step 2: modify
        SELECT @DiscountPct = CASE WHEN @TotalOrderAmount > 1000.00 THEN 0.10 ELSE 0.00 END
        SELECT @Amount = 9.99 * (1 - @DiscountPct)

        -- Step 3: write
        INSERT INTO Orders(CustomerID, Amount) VALUES (5, @Amount)
    COMMIT TRAN

最大的好处是只有 CustomerID = 5 的订单行会被锁定，因此大多数调用根本不会等待，因为它们用于不同的客户。

但这种方法仍然存在一个主要缺点:对于新客户来说，它根本不起作用，因为还没有要锁定的行。因此，具有相同新 CustomerID(还没有订单)的 2 个并发调用不会相互等待。

所以除了UPDLOCK，ROWLOCK我还需要类似的东西

• 如果范围存在，则执行 ROWLOCK
• 如果范围不存在，则执行 TABLOCK(或类似“新行锁定”的操作)

有点像

BEGIN TRAN
    IF EXISTS(SELECT * FROM Orders WHERE CustomerID = 5)
        SELECT @TotalOrderAmount = sum(Amount) FROM Orders with (UPDLOCK, ROWLOCK) WHERE CustomerID = 5
    ELSE
        SELECT @TotalOrderAmount = sum(Amount) FROM Orders with (UPDLOCK, TABLOCK) WHERE CustomerID = 5

但是在1条语句中(因为IF EXISTS也需要并发证明)。 The TABLOCK also doesn't seems the best solution because when a new customer is selected, the existing customers (acquiring a ROWLOCK) are also waiting for the release of the TABLOCK.这就是为什么我在上面提到“新行锁”。

Answer 1

您可以在开始事务后在 SELECT 上使用 UPDLOCK/XLOCK 提示。 像这样。

示例表结构

CREATE TABLE Orders
(
    OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID INT NOT NULL,
    Amount NUMERIC(18,2) NOT NULL
);

CREATE INDEX IDX_Cutomer_Orders ON Orders(CustomerID) INCLUDE(Amount);

INSERT INTO Orders VALUES(1,123.25),(1,55),(2,8765900),(7,900);

INSERT INTO Orders VALUES(5,123.25),(5,8765900);

程序

CREATE PROCEDURE AddOrder
@CustomerID INT
AS
BEGIN

    BEGIN TRANSACTION

    DECLARE @TotalOrderAmount NUMERIC(18,2),@Amount NUMERIC(18,2),@DiscountPct NUMERIC(4,2)
    -- Step 1: read (every other call to AddOrder should wait here until this procedure has finished
    SELECT @TotalOrderAmount = SUM(Amount) FROM Orders WITH (UPDLOCK ,ROWLOCK)
    WHERE CustomerID = @CustomerID

    -- Step 2: modify
    SELECT @DiscountPct = CASE WHEN @TotalOrderAmount > 1000.00 THEN 0.10 ELSE 0.00 END
    SELECT @Amount = 9.99 * (1 - @DiscountPct)

    WAITFOR DELAY '00:00:10'
    -- Step 3: write
    INSERT INTO Orders(CustomerID, Amount) VALUES (@CustomerID, @Amount)

    SELECT * FROM Orders WHERE CustomerID = @CustomerID

    COMMIT
END

在这里，对 EXEC AddOrder 1 的同时调用将等待第一个调用提交/回滚。

对 EXEC AddOrder 1 和 EXEC AddOrder 5 的调用将并行工作，不会相互阻塞。