most of my clients started with a much simpler Cart, years ago. It has been pretty much a standard practice to notify the customer that the item went out of stock just before their order, due to the popularity of it. Then give the customer some options on other products, or a refund. I has seemed to keep the customer happy and they were glad for the customer service. Made repeat customers out of them.
Matt Warnock sent the following on 8/14/2010 1:02 PM:
Thank you David for the detailed and thoughtful explanation. I think I see the issue now. However, I still see it as largely a business process issue, not a programming issue. You're right, you don't want to tell a customer that a product is available, then later tell them it isn't, because someone beat them to it. But the real fact of life is that shopping carts are abandoned all the time, so you really shouldn't commit inventory to any order until the order is placed and approved. There are lots of potential slips between order placement and shipping (when the product REALLY moves out of inventory)-- abandoned carts, declined credit, bad shipping address, no shipping service to that location, import/export restrictions, management fiat, whatever. I exercised management fiat just yesterday. :) Near-simultaneous inventory transactions *per se* shouldn't be a problem. If I order 5 items, then when I commit, my code sets "qty = qty - 5" in a single sql statement and is further enclosed within a transaction that checks that the resulting (or beginning) qty is not less than zero or some other minimum number, or from mixed lot numbers, or whatever else your business logic may require. Very busy sites won't collide unless they try to commit inventory to "pending" orders with an optimistic lock, which I think is a mistake. If inventory levels are high and goods are fungible, you don't need to worry about it. Tens of seconds, or even days, isn't going to make a difference, and even if the item is serialized, you allocate the serial number when the order is filled. If inventory is low, or unique, then you have a scarcity problem, and you may want to warn the user of that fact, both to encourage the quick order, and to manage expectations, so that they are not disappointed-- but locking the inventory pending an order that may or may not happen seems like a wrong approach to me. If people are doing that, then I certainly understand why they are seeing concurrency issues on busy sites. Another possible approach would be to enter "pending" orders in a separate table (or flagged as such in the same table), and only deduct from the actual inventory if and when the sale is finally approved. That way you could see whether inventory is scarce (orders pending equal or exceed some ratio of inventory on hand) and also impose a "first come, first served" approach on pending orders, while still imposing time limits on completing the order, and possibly even enabling a "waiting list". Kind of like an appointment queue-- "we've had a cancellation, so we can take you today at 3pm." It just seems to me that optimistic locking is not the right solution to a scarce inventory problem. In my view, any database that relaxes the ACID requirements to achieve concurrency is barely worthy of the name. ERP (especially financial accounting) requires ACID, period. You can't run an enterprise on less. But it does mean you need to break your business processes down into small, atomic steps so that-- like using a hammer to drive a screw. isolation requirements do not become a concurrency nightmare. And some longer steps (like internally consistent dumps of the entire database) may need to be timed carefully. Thanks again for your thoughtful explanation.
