How relational databases Handle "inventory" issues

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