1

I am building a simple shopping cart. Currently, to ensure that a customer can never purchase a product that is out of stock, when processing the order I have a loop for each product in their cart:

-- Begin a transaction -- Loop through each product in the cart and

  1. Select the stock count from the products table

If it is in stock:

  • I will reduce the stock count from the product
  • Add the product to the order items table

Otherwise, I call a rollback and return an error

-- (If there isn't a call for rollback, everything ends off with a commit --

However, if at any given time, the stock count for a product is updated AFTER it has checked for that particular product, there may be inconsistencies.

Question: would it be a good idea to lock the table from writes whenever I am processing an order? So that when the 'loop' above occurs, I can be assured that no one else is able to alter the product count and it will always be accurate.

The idea is that the product count/availability will always be consistent, and there will never be an instance where the stock count goes to -1 (which would be unfulfillable).

However, I have seen so many posts on locks being inefficient/having bad effects. If so, what is the best way to accomplish this?

I have seen alternatives like handling it in an update + select query, but have seen that it may also not be suitable in some cases.

Iva
  • 55
  • 3
  • It is bad design, and inconsistent with InnoDB, to lock anything for more than a few seconds. You probably need some hand-rolled locking and rollback. – Rick James Jun 26 '20 at 06:00

1 Answers1

2

You have at least three strategies:

1. Pessimistic Locking

If your application will experience low activity then you can lock the tables (or single rows) to make sure no other thread changes the values during the processing of a purchase. It works, but it has performance limitations.

2. Optimistic Locking

If your application/web site must serve a high load then you can opt for the "optimistic locking" strategy. In this case you add a version number column to your critical tables and then you use it when reading/writing it.

When updating stock you check the version number you are updating must be the same that you read. If it's not the case anymore (another thread modified it) you roll back the transaction and can retry again a couple of times until you succeed.

It requires more development effor since you need to identify the bad case and implement retry logic (if you want to).

3. Processing Queues

You can implement processing queues. When a thread wants to "purchase an order" it can submit it to a processing queue for purchase orders. This queue can be implemented by one or more threads dedicated to this task; if you choose multiple threads they can be divided by order types, regions, categories, etc. to distribute the load.

This requires more programming effort since you need to manage asynchronous processing, but can sustain much higher levels of load.

You can use this strategy for multiple different tasks: purchasing orders, refilling stock, sending notifications, processing promotions, etc.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thanks for the detailed response. Do you mind elaborating 2 further? Before I begin, I will update the version for the products' e.g. UPDATE products SET version = 1234 WHERE (products are those in the user's cart) UPDATE products SET stock = stock - 1 WHERE product = id AND version = 1234 What happens when I have 7 items in the cart and I iterate em: But while 4 is being checked, person 2 creates an order - the stock count seen by #2 would be the same one as what #1 is seeing, as the transaction is only committed at the end when all 7 products were checked. – Iva Jun 26 '20 at 04:55