Re: Inventory management against orders was Optimistic locking based on timestamps

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: Inventory management against orders was Optimistic locking based on timestamps

BJ Freeman

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.
>