Solve the problem that inventory is reduced to negative in the case of concurrency, and the inventory is negative.

Solve the problem that inventory is reduced to negative in the case of concurrency, and the inventory is negative.

Scenario:

If a commodity is in stock, check the inventory before placing the order. If it is greater than 0, place inventory-1 and then place the order. If it is <= 0, the order cannot be placed. The transaction contains two SQL statements:

select quantity from products WHERE id=3;
update products set quantity = ($quantity-1) WHERE id=3;

In the case of concurrency, the inventory may be reduced to a negative number (if both processes select more than 0 at the same time, then update will be executed). What should I do?

Method 1:

InnoDB supports displaying and locking through specific statements:

Select... lock in share mode

Select... for udpate

select quantity from products WHERE id=3 for update;
update products set quantity = ($quantity-1) WHERE id=3;

However, executing for update may have some other impacts.

1. Slow select statement

2. Some optimizations cannot be used normally, such as index coverage scanning.

3. It is easy to cause server lock contention problems.

Method 2:

Write the udpate Statement on the front side, first set the number to-1, and then select out of stock if>-1 is commit, otherwise rollback.

update products set quantity = quantity-1 WHERE id=3;
select quantity from products WHERE id=3 for update;

The above transaction executes update first, so the row id = 3 is locked and only the commit/rollback is released (the locks in the transaction are gradually obtained, but all are released when commit is used ). The concurrency problem is well solved.

Method 3:

The update statement is updated with a condition.

$quantity = select quantity from products WHERE id=3;
update products set quantity = ($quantity-1) WHERE id=3 and queantity = $quantity;

In this way, although the select statement is not locked, the transaction isolation level of mysql is repeatable, so the modification of other transactions does not affect the select result. When update is executed, if another transaction locks this record, update will wait and wait until other transactions release the lock. The update will be executed. However, if the quantity of quantity has been modified, the execution of update will return 0 affected rows.

Cause:

For example, if $ quantity = 3 is selected first, then the update condition is id = 3 and quantity = 3, and the function 0 is affected when an update is executed, however, when executing the select statement again, we found that the quantity record with id = 3 is indeed 3. Why can't this record be updated?

This is due to the transaction isolation level of mysql and MVCC. When the first select statement is $ quantity = 3, other transactions are executed before this update, as a result, the update condition does not find an appropriate record, because at the Repeatable read level, the update read is "current read" and the latest data is read. The value of $ quantity found during the select statement again is 3, because the select statement reads "snapshot read" and historical data, which is also a feature at the Repeatable read level.

Detailed Innodb in the transaction isolation level can be seen in the US Mission comment technical team of an article, there is an explanation for MVCC and gap lock: http://tech.meituan.com/innodb-lock.html