In the mall, there is a product has 100 pieces of inventory, then the user every buy one, inventory to do minus one. At first I thought of handling this:
$model = new Model ();//Read Inventory $quantity = $model->where ("id = 1")->count ();//inventory minus one $quantity-; $result = Mysql_que Ry ("UPDATE goods SET quantity = $quantity WHERE id = 1");
In this way, it is not a problem without considering concurrency, but the situation is different if multiple users are working at the same time.
Assuming that two users buy at the same time, a user reads inventory for 10,B users to read inventory is also 10,a after the purchase is completed, the inventory will be reduced to 9, and at this time, B will also reduce the inventory by one, instead of 9. Actually sold two pieces, but the stock did not reduce correctly, it will cause "oversold".
A, B user's purchase process is cross-executed.
The key to the problem is that the code above uses two SQL statements, so requests from different users may cross-execute. This problem can be avoided if the operation can be combined into an SQL statement.
Take a look at the revised code:
$model = new Model (), $result = mysql_query ("UPDATE goods SET quantity = quantity-1 WHERE id = 1");
Because the relational database is stored in a file, modifications to the same record cannot be made concurrently. This means that even if A and B users buy at the same time, the inventory changes are in order, so the inventory can be correctly reduced.
There is also a problem, if the inventory is already 0, the above code is problematic, because there is no check inventory.
There are two ways to solve this problem:
1, the Quantity field is set to unsigned, when the inventory is 0 o'clock, because the field cannot be negative, will return false.
2. Modify the SQL statement and implementation code:
$model = new Model ();//Read Inventory $quantity = $model->where ("id = 1")->count ();//inventory minus one $quantity-; $result = Mysql_que Ry ("UPDATE goods SET quantity = quantity-1 where id = 1 where quantity > 0"); $num = Mysql_affected_rows (); if ($num = = 1) {//success}
If the inventory is 0, the affected line is 0, which means that the purchase failed.
How relational databases Handle "inventory" issues