SQL Server中的并发读写

Question

我想在SQL中这样做：

• 步骤1:从表A、B、C中读取数据
• 第二步:做一些计算
• 将数据写回表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

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

我不想把所有的东西组合成一个单独的语句(如果这能解决任何问题)，因为我的实际情况当然比这个例子更复杂。

我还希望使用现代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 (还没有订单)的并发调用将不会等待对方。

因此，除了UPDLOCK，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存在也需要并发证明)。TABLOCK似乎也不是最好的解决方案，因为当选择一个新客户时，现有客户(获取一个行锁)也在等待TABLOCK的发布。这就是为什么我在上面提到“新行锁”的原因。

Answer 1

您可以在启动事务后对UPDLOCK / XLOCK提示在SELECT上使用。就像这样。

样本表结构

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的同时调用将等待对commit / rollback的初始调用。

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