SQL Server 2014 Concurrent input issue

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

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.

StackExchange Database Administrators Q#186741, answer score: 12