Solve the problem of inventory minus negative in concurrency

Scene:

A product is in stock, the order is checked before the inventory, if the >0, put inventory 1 and then order, if <=0, can not place the order, the transaction contains two SQL statements:

Select  from the products where id=3set quantity = ($quantity-1) where id=3;

In the concurrency situation, the inventory may be reduced to negative (two processes at the same time the select out of the >0, and then will perform the update), so need to lock,

InnoDB supports the display of locking through specific statements:

Select...lock in Share mode

Select...for udpate

Select  from Products where id=3forset quantity = ($quantity-1) where ID =3;

But performing a for update has some other impact

1.select statement slows down

2. Some optimizations do not work properly, such as index overlay scanning

3. It is easy to create a server lock requisition problem

So adopt some ingenious method can avoid with for update, can put udpate statement in front, first put quantity-1, then select out inventory if >-1 commits, otherwise rollback.

Set quantity = quantity-1 WHERE id=3; Select  from the products WHERE id=3 for update;

Update is performed on the top of the transaction, so the id=3 row is added with a row lock and only Commit/rollback is released. A good solution to the concurrency problem.

Solve the problem of inventory minus negative in concurrency