I have a situation where users will often be driven to the site at a specific time for a product that has just been released.
ie.  several users hitting the site at the exact same time for the same item.

Looking at the code for adding items to the shopping cart, I see a tremendous potential for concurrency errors.

I think the "add to cart" process needs to be retooled slightly. The issue is that you are oking the inventory in one step and then updating the product in another step thus allowing a good amount of time for things to get off-kilter.

I have some suggestions for how to handle this...

First - setup a table to handle the current pending orders. This table willl have the product id, sessionid, date created, and a time to live.

When an item is added to the cart - it is checked for "GetShoppingCartItemWarnings" - but this does not do an inventory check at this time. If this check passes, then you would move on to checking the validty of the available inventory.

To check the available inventory - you would see if the product has allotment - pending - sold = available inventory and update the shopping cart based upon this availability.

sql example: ***please note this is a simplification of the actual process

--adding new items to cart
INSERT [Pending] (SessionId, iQTy, ProductId, dtCreated, dtTTL)
SELECT       [iQty] = @qty,
            [dtTTL] = @ttl,....
FROM    [Product] itm, [Pending] pending
WHERE  pending.[Id] = @id AND
            pending.[ticketId] = itm.[Id] AND
            itm.[bActive] = 1 AND
            (itm.[iAllotment] - CASE WHEN pending.[iQty] IS NULL THEN 0 ELSE SUM(pending.[iQty]) END - itm.[iSold]) >= @qty


--existing items being updated in an order - change qty
UPDATE [Pending]
SET       [iQty] = pending.[iQty] + @qty,
            [dtTTL] = @ttl
FROM    [Product] itm, [Pending] pending
WHERE  pending.[Id] = @id AND
            pending.[ticketId] = itm.[Id] AND
            itm.[bActive] = 1 AND
            (itm.[iAllotment] - CASE WHEN pending.[iQty] IS NULL THEN 0 ELSE SUM(pending.[iQty]) END - itm.[iSold]) >= @qty

If the @@rowcount is > 0 then you are able to (actually you already have) add that item to the cart.


Upon order completion first update the sold amount of the product AND THEN remove the item from the pending table.

To cleanup orders that were never completed, session timed out, etc - you would run a sql job (or equivalent) every few minutes to clean up the expired pending rows.

Thoughts? I'd be more than happy to expand on this.