How to check the quantity in stock when making concurrent orders?-CodePudding

Let's imagine a DB with two tables: items and orders. The first table contains all items available for sale. The second one keeps track of all orders made by customers. Each item has a column 'quantity', which says how many of these items are available in stock. When making a new order, the backend checks if ordered amount is not greater than amount in stock. If so, the order is not created. Otherwise the order is created and the quantity of available items is updated.

The problem is that when two orders are created simultaneously, both checks are executed at the same time and two orders are created (not knowing about each other). As a result, there are two orders in DB with the total ordered amount larger than the actual quantity in stock.

I've already searched on how to handle this issue and encountered such concepts as transactions, locks, isolation and so on. I got to understand these terms but still didn't get what architectural solution needs to be implemented.

What exactly I need to do to solve this trouble? What SQL query to write for checking the stock before creating an order? Should I wrap it in a transaction and apply some isolation level to it? Or maybe I just need to lock the tables when making an order? Is it possible to just make the order creating operation wait until concurrent order is created? Still have no answers to these questions.

Hope for your help. Thanks!

CodePudding user response：

There are many ways to solve the problem. Assuming you are not trying to create a major retail site here, the simplest is to lock the row in items.

The easiest way is probably not even to check until after the subtraction has been done.

UPDATE ITEMS set quantity_avail=quantity_avail - $2 
    where part_num= $1 
    returning (quantity_avail)

And then if the returned value is less than 0, do a rollback of the transaction and report to the user that it is now out of stock.

But now what happens if the shipping department drops it on the floor and breaks it while preparing it for shipping?

CodePudding user response：

For low to mid volume you can implement real time processing. For high volume you need to settle for near-real time.

For real time processing there are two options:

Optimistic Locking: The app doesn't lock the records that need to modify but only reads them. When the processing is finishing (ideally after a short period of time) the app updates the records with "concurrency check". Typically the records will feature a version number, a timestamp, or in extreme cases the entire record will be compared. If the update pasess the concurrency check, then all is good, the transaction is committed, and the order is complete. If the concurrency check does not pass, compensating logic needs to take place to retry the action, to recover it somehow, or to consider it failed. The benefit of this strategy is that it can process more orders with the same hardware. The downside is that it's a more complex solution since it needs to take care of extra path, not just the happy path.
Pessimistic Locking: The app reads and locks all the necessary records. All locks needs to be taken in the same sequence by all other competing processes. If all the locks are secured, then the order can be processed safely without the fear of a hiccup at the end of the task. The benefit of this strategy is that is simple to understand and to debug. The downside of this strategy is that it locks are expensive to obtain and can significantly impact the bandwidth of the app -- that is, how many orders per minute it can process.

Finally, for high volume you probably need to settle for near real time -- aka deferred processing. There are two strategies:

If your app can triage the orders by some criteria (region, client, type of products, warehouse, etc.) you can implement a microservice or a set of queues where each instance of the service/queue serves separate clients or stock items. This can provide a decent level of parallelism in this case.
If there can be no triage for the orders, then a single queue can process all the orders, one by one. This can be slow for some apps.