高并发模拟实验1-情景模拟

实验目的
查看高并发情况下数据逻辑上出现的错误问题（注：这里不考虑服务器负载等性能问题）

实验描述
假设存在商品表goods，表包含两个字段：ID，quantity，其中ID为主键，代表商品编号，quantity代表商品库存数量，服务端设计程序模拟购物，每购买一件（客户端每请求一次），商品库存就减一。在测试会话中使用ab压力测试工具模拟以下两种情况，分别查看库存剩余量。假设库存总量为100，请求完毕，正确的库存剩余量应该是80。

（1）有20个人依次排队进行购物，每人购买一件。

（2）同时有10个人进行购物操作，总的购买量为20（即总的请求个数为20，每次发送10个请求）

实验环境：fedora29

所需软件：apache，php，mysql，ab

实验步骤：

建立初始数据

MariaDB [test]> CREATE DATABASE `test`;
MariaDB [test]> CREATE TABLE `goods` (
    -> `ID` int(10) DEFAULT NULL,
    -> `quantity` int(10) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.693 sec)
MariaDB [test]> insert into goods values(1,100);
Query OK, 1 row affected (0.068 sec)
MariaDB [test]> select * from goods;
+------+----------+
| ID   | quantity |
+------+----------+
|    1 |      100 |
+------+----------+
1 row in set (0.000 sec)

购物处理程序buy.php

<?php
$db = new MySQLi("localhost","root","yptest","test");
$query = "select quantity from a where ID =1";
$res =$db->query($query);
while($row=mysqli_fetch_array($res)){
   $quantity = $row['quantity'];
}
$sql = "update goods set quantity = $quantity+1 where ID =1";
$reslut =$db->query($sql);
//记录操作语句，查看执行细节
$file = fopen("log.txt", "a+") or die("Unable to open file!");
fwrite($file, $sql."\r\n");
fclose($file);
if($reslut){
    echo "success";
}else{
    echo "error";
}
?>

模拟请求

（1）有20个人依次排队进行购物，每人购买一件。

ab -c 1 -n 20 "http://localhost:9101/buy.php"

结果（正确）：

MariaDB [test]> select * from goods;
+------+----------+
| ID   | quantity |
+------+----------+
|    1 |       80 |
+------+----------+
1 row in set (0.000 sec)

日志（log.txt）明细：

update goods set quantity = 100-1 where ID =1
update goods set quantity = 99-1 where ID =1
update goods set quantity = 98-1 where ID =1
update goods set quantity = 97-1 where ID =1
update goods set quantity = 96-1 where ID =1
update goods set quantity = 95-1 where ID =1
update goods set quantity = 94-1 where ID =1
update goods set quantity = 93-1 where ID =1
update goods set quantity = 92-1 where ID =1
update goods set quantity = 91-1 where ID =1
update goods set quantity = 90-1 where ID =1
update goods set quantity = 89-1 where ID =1
update goods set quantity = 88-1 where ID =1
update goods set quantity = 87-1 where ID =1
update goods set quantity = 86-1 where ID =1
update goods set quantity = 85-1 where ID =1
update goods set quantity = 84-1 where ID =1
update goods set quantity = 83-1 where ID =1
update goods set quantity = 82-1 where ID =1
update goods set quantity = 81-1 where ID =1

（2）同时有10个人进行购物操作，总的购买量为20（即总的请求个数为20，每次发送10个请求）

ab -c 10 -n 20 "http://localhost:9101/concurrency.php"

结果（错误）：

MariaDB [test]> select * from goods;
+------+----------+
| ID   | quantity |
+------+----------+
|    1 |       87 |
+------+----------+
1 row in set (0.000 sec)

日志（log.txt）明细：

update goods set quantity = 100-1 where ID =1
update goods set quantity = 99-1 where ID =1
update goods set quantity = 98-1 where ID =1
update goods set quantity = 98-1 where ID =1
update goods set quantity = 99-1 where ID =1
update goods set quantity = 99-1 where ID =1
update goods set quantity = 98-1 where ID =1
update goods set quantity = 97-1 where ID =1
update goods set quantity = 97-1 where ID =1
update goods set quantity = 96-1 where ID =1
update goods set quantity = 95-1 where ID =1
update goods set quantity = 94-1 where ID =1
update goods set quantity = 91-1 where ID =1
update goods set quantity = 93-1 where ID =1
update goods set quantity = 91-1 where ID =1
update goods set quantity = 92-1 where ID =1
update goods set quantity = 91-1 where ID =1
update goods set quantity = 90-1 where ID =1
update goods set quantity = 88-1 where ID =1
update goods set quantity = 89-1 where ID =1

结果分析：

从结果可以看出，当有10个人同时进行请求购物时，库存剩余量出错，分析日志文件可以看出，在并发请求的情况下，由于无法准确的获取当前库存剩余量，导致在更新库存剩余量出错。

问题:
如何保障在10个人同时进行请求购物时，每次请求都能获取到准确的库存剩余量，进而进行库存的更新操作？

解决办法
《 高并发模拟实验2-解决高并发问题》