'How to control updates to product count?
I have a table called products which contains a field named product_count which tells us about the count of product type we have.
I need to manage the product count when multiple updates happen from different users at the same time.
For Example Consider when 4 users tried to purchase the same product but database has only one product remaining. Now 4 requests come to our API to buy the product , I want to ensure that only one user will get the product as we have only one product left in db. Which technique we should use to achieve this?
I am using Java, Spring boot and MYSQL db.
Solution 1:[1]
When you update the stock, run the query like:
update products set stock = stock - 1 where id = xxx and stock >= 1;
And this sql will return the count of rows affected, if success, result will be 1, and if the stock is not enough, the result of this sql will be 0 , and you will know this user purchase failed.
Solution 2:[2]
This is achieved at database level. RDBMS implements ACID. At backend level (Spring boot) you implement the operations of buying (@Repository / @Query) and exception handling (@ExceptionHandler) of stock-checking (e.g. "product not available").
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | bubbak |
| Solution 2 |