• Sergiy - Sunday, March 19, 2017 6:04 AM

    sqlrob - Sunday, March 19, 2017 5:33 AM

    Sergiy - Saturday, March 18, 2017 4:33 PM

    How many records per SKU?
    Is it a unique key?
    Is the uniqueness enforced through the UNIQUE constraint?

    Apart from that - the logic of the query seems troubled.

    SKU(pkey) is the primary Key and Uniqueness is enforced through Primary Key Constraint.

    Apart from that - the logic of the query seems troubled - Will you be able detail , what you mean by this ?

    There is a technique named "warming up the updated pages" which in some cases helps improve performance of updates by putting the pages into the buffer using SELECT:

    BEGN TRANSACTION
    IF (SELECT TOP 1 available + overselling - reserved
    FROM  Inventory
    WHERE PKey = 500 ) > 5
    UPDATE Inventory
    SET reserved = reserved + 1
    ,Modtime = getdate()
    WHERE PKey = 500
    COMMIT
    go

    As for logic - the query increases reserved number on some condition.
    But it does not actually reserve any particular item.
    An actual item reservation must be happening somewhere else.
    On a busy system with multiple reservations happening in parallel the condition may change while execution moves from individual reservation to updating the aggregates.
    So, you might have the situation when an item was actually reserved but Inventory was not updated because another reservation updated the Inventory in between those events.

    This update must be executed only as a follow-up of an actual item reservation.
    And it must be unconditional (only SKU filter to be applied).
    If an item has been reserved then aggregated "reserved" value must be increased regardless of any other conditions.

    Thanks Sergiy for the quick response. I will try warming up the technique.
    This query does not the Reserve the Item, provided there is stock avialable . The maths part checks for Stock availbility. It is not handled some where else.