SQL Server 2014 Concurrent input issue

by Joe   Last Updated September 23, 2017 20:06 PM

In Table

Orders

I store the orders we receive from all shops

Since order can have more lines, among the fields there are OrderID and OrderLineID where OrderID can be duplicated but OrderLineID has to be unique.

Since orders can be amended the stored procedure first controls if the received OrderLineID is already present in the table and then decides to insert or to update to do this we use:

build the Insert and update sentences 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 question is the following:

Due to line issue/Server Busy and so on, the Order message (or the modification), could be sent more than 1 time and we do not know in which sequence therefore to avoid that Order arrives after the amendment and therefore overwrites the amendment we added a time Field

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 latter message is older than the previous has not effect on the table

But in some circumstances 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 and therefore the IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID) is TRUE for both execution of the stored procedure and both times it generates an INSERT and we find with 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 latter message execute the UPDATE query and not throw a unique key violation.

Can suggest asolution?

Thanks Joe



Related Questions


NULL values in unique constraints

Updated September 07, 2017 07:06 AM


Creating the same unique ID in multiple tables

Updated May 18, 2018 00:06 AM