Concurrent read-modify-write in SQL Server

Question

I want to do in SQL:

• step 1: read data from tables A, B, C
• step 2: do some calculations
• write back data to tables A, B, C

But this must be concurrent proof, meaning that once step 1 is done, every other instance needs to wait for step 1 until step 3 is finished since it changes the data to do the calculations. Here is a simplified example (I´ve left out some declarations and used hard coded values):

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

The first thing in my mind was of course use a transaction with a raised isolation level:

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

But this won't solve anything. Assume that 2 connections execute the procedure at exactly the same time. Step 1 will place and hold a shared_read lock and both connections will go trough step one which is already wrong. But it gets worse, since there are 2 locks on the table that will be updated in step 3, there will be a deadlock.

I don't want to group everything into a single statement (if this would solve anything) because my real situation is of course more complex than the example.

I also would like to use the range locking of a modern SQL Server rather than locking the entire table, so that only the rows for that CustomerID will be locked. And finally, i don't won´t optimistic locking, so both calls should always succeed.

Does anyone has a simple solution for this problem?

UPDATE:

At first, it seems that using a table hint UPDLOCK would solve the issue. For example:

    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

The great benefit is that only the order rows for CustomerID = 5 will be locked so most of the calls won't even wait at all since they are used with different customers.

But this approach still leaves one major shortcoming: for new customers it won't work at all, since there are no rows yet to lock. So 2 concurrent calls with the same new CustomerID (that has no orders yet) won't wait for each other.

So in addition to UPDLOCK, ROWLOCK i would need something like

• if the range exist, do a ROWLOCK
• if the range doesn't exist, do a TABLOCK (or something like a 'new row lock')

Something like

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

But in 1 statement (because the IF EXISTS also needs to be concurrent proof). 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. That's why I mention 'new row lock' above.

Answer 1

You can use UPDLOCK / XLOCK hint on the SELECT after starting a transaction. Something like this.

Sample Table Structure

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);

PROCEDURE

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

Here, Simultaneous calls to EXEC AddOrder 1 will wait for the initial one to commit / rollback.

Calls to EXEC AddOrder 1 and EXEC AddOrder 5 will work in parallel without blocking each other.