I store the orders we receive from all shops
Since order can have more lines, among the fields there are
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?