Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are
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.