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.

CodePudding user response：

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.

CodePudding user response：

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