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