MySQL handles high concurrency, preventing inventory oversold

First of all, the issue of inventory oversold description: General e-commerce sites will encounter such as group purchase, second Kill, specials and other activities, and such activities have a common feature is the surge in traffic, thousands or even tens of thousands of people snapping up a commodity. However, as an active commodity, inventory is certainly very limited, how to control inventory does not allow overbought, in order to prevent unnecessary loss is a lot of e-commerce website Programmers Headache problem, this is also the most basic problem.

From the technical perspective, many people will certainly think of business, but the transaction is to control the inventory oversold requirements, but not sufficient necessary conditions.

Example:

Total Stock: 4 items

Requester: A, a commodity B, 2 commodities C, 3 items

The procedure is as follows:

Begintranse (Open transaction)

try{

$result = $DBCA->query (' Select amount from s_store where PostID = 12345 ');

if (Result->amount > 0) {

Quantity inventory quantity that is lost for the request

$DBCA->query (' update s_store set amount = amount-quantity where PostID = 12345 ');

}

}catch ($e Exception) {

RollBack (rollback)

}

Commit (COMMIT TRANSACTION)

The above code is our usual control inventory write code, most people will write this, seemingly the problem is not big, in fact, hidden a huge loophole. access to the database is actually access to the disk files, the table in the database is actually saved on disk files, even a file contains multiple tables. For example, due to high concurrency, there are currently three users A, B, c three users into the transaction, this time will generate a shared lock, so at the time of the Select, the three users found the inventory amount is 4, but also note that MySQL InnoDB found that the results are version-controlled, and then other users update no commit before (that is, no new version before the creation), the current user found the results are still the version;

Then update, if the three users at the same time to update here, this time the UPDATE statement will be the concurrent serialization, that is, to arrive at the same time there are three users order, one to execute, and generate an exclusive lock, before the current UPDATE statement commit, Other users wait to execute, commit, generate a new version, so after execution, the inventory must be negative. But according to the above description, we modify the code will not appear overbought phenomenon, the code is as follows:

Begintranse (Open transaction)

try{

Quantity inventory quantity that is lost for the request
$DBCA->query (' update s_store set amount = amount-quantity where PostID = 12345 ');

$result = $DBCA->query (' Select amount from s_store where PostID = 12345 ');

if (Result->amount < 0) {

throw new Exception (' insufficient inventory ');

}

}catch ($e Exception) {

RollBack (rollback)

}

Commit (COMMIT TRANSACTION)

In addition, a more concise approach:

Begintranse (Open transaction)

try{

Quantity inventory quantity that is lost for the request
$DBCA->query (' update s_store set amount = amount-quantity where amount>=quantity and PostID = 12345 ');

}catch ($e Exception) {

RollBack (rollback)

}

Commit (COMMIT TRANSACTION)

MySQL handles high concurrency, preventing inventory oversold