About the write lock function of mysql transaction row lock forupdate _ MySQL

For the write lock function of mysql transaction row lock forupdate, in e-commerce, there are often fewer inventory and a large number of buyers, in the case of high concurrency, how can we ensure that the number of items will not be purchased multiple times.

In fact, it is very simple to use the transaction + for update to solve.

We all know that for update is actually a shared lock and can be read. but how can we not read it during execution.

To put it simply, assume that the inventory is 1 now and A and B are available for both purchase.

Start a transaction first

Begin;

Select stock from good where id = 1 for update; // query the number of stock items in the good table.

Check whether the stock is 0 in the program. (it's none of my business)

Finally, execute

Update good set stock = stock-1 where id = 1

Finally

Commit

However, at this time, B is also select stock from good where id = 1 for update; Note: for update cannot be omitted .. at this time, it will be locked and cannot be read.

Therefore, this ensures the consistency of the remaining number of items as 1.