SQL Server 2014 Concurrent input issue

Question

In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID where OrderID can be duplicated but OrderLineID has to be unique within an order.

Since orders can be amended, the stored procedure first checks if the received order's OrderLineID is already present in the table, and then decides to insert or to update. To do this we:

• build the insert and update statements dynamically from the XML input
• insert into customers table
• insert into shippingAddresses table

and then the main table:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID ......)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID

Or does the MERGE function offer better performances/control?

But the question is the following:

Due to line issue/server busy and so on, the Order message (or the modification), could be sent more than once, and we do not know in which sequence. Therefore, to avoid the Order arriving after the amendment, and therefore overwriting the amendment, we added a time column:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID AND LastModified<@CreatedTime

In this way if the later message is older than the previous, it has no effect on the table.

But, it could happen that the message and its modification are sent twice (or more) in so little timeframe that the latter message arrives before the former has been saved. Therefore the IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID) is TRUE for both executions of the stored procedure, and both times it generates an INSERT and we find duplicated rows.

Perhaps this can be avoided by simply setting OrderLineID as unique key?

I also read something about using:

set transaction isolation level serializable

but I'm not sure how this is handled. I not only want to avoid the duplicate rows but to be sure the later message executes the UPDATE query and not throw a unique key violation.

Answer 1

_{Community wiki answer:}

MERGE has some issues you should be aware of, see Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.

More specifically to your present situation, MERGE provides no additional protection against concurrency issues beyond an automatic containing transaction. You would need to add a SERIALIZABLE hint (or its synonym, HOLDLOCK) to the MERGE statement as well, see “UPSERT” Race Condition With MERGE by Dan Guzman. Note that an UPDLOCK hint is not necessary with MERGE.

For a more general overview of the issues and options, see the following by Michael J. Swart:

• SQL Server UPSERT Patterns and Antipatterns
• Mythbusting: Concurrent Update/Insert Solutions

A popular and robust solution is to add a WITH (UPDLOCK, SERIALIZABLE) hint to table access in the NOT EXISTS check, with a containing transaction around the whole UPSERT operation.

Using your example:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT * 
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orders () VALUES ()
ELSE
    UPDATE Orders 
    SET ... 
    WHERE OrderLineID=@OrderLineID 
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

Or equally:

BEGIN TRANSACTION;

INSERT INTO Orders ()
SELECT ...
WHERE NOT EXISTS
(
    SELECT * 
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
);

IF @@ROWCOUNT = 0
BEGIN
    UPDATE Orders 
    SET ... 
    WHERE OrderLineID=@OrderLineID 
    AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

The order of the INSERT and UPDATE operations can be switched so that whichever you expect to succeed most often is tried first. The hints must always be applied to the first operation.

For example, if updates are expected to be most numerous:

BEGIN TRANSACTION;

UPDATE Orders WITH (UPDLOCK, SERIALIZABLE)
SET ... 
WHERE OrderLineID=@OrderLineID 
AND LastModified<@CreatedTime;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Orders ()
    SELECT ...
END;

COMMIT TRANSACTION;

The uniqueness constraint mentioned in the question is a good idea for consistency in general, but not strictly needed for the methods noted here. If you were to use Michael Swart's Just Do It pattern, a uniqueness guarantee would be required for correct operation.

As a reminder, if you prefer MERGE for whatever reason, you must use a SERIALIZABLE hint against the target table.

While it is true in general that locking hints should be avoided as far as possible, this is one example of a valid use case.

Answer 2

The serializable transaction guarantees that all your transactions that are running concurrently will make the same effect as they run one after another.

This means that if you have 2 transactions that wants to query the same rows in the same table, the one which starts later will wait until the first one commit/roolback.

For example we have 2 rows in table orderLabel where orderID = 1

12:00 Serializable Transaction 1 starts with query - select count(*) from orderLable where orderID = 1

12:01 Serializable Transaction 2 starts with query - insert into orderLabel(orderID) values(1) -- this will wait until Transaction 1 commit/roolback

12:02 T1 commits and returs 2

12:03 T2 inserts 1 row with orderID = 1 and commits.

In your case Serializable will do the work, because when it it reaches the line IF NOT EXISTS(Select 1 from Orders where OrderLineID=@OrderLineID ......) It will put lock on that rows and no other transaction will be able to insert data with the same @orderLineID.