商城系统中,抢购和秒杀是很常见的营销场景,在一定时间内有大量的用户访问商场下单,主要需要解决的问题有两个:
高并发对数据库产生的压力
对于第一个问题,使用缓存来处理,避免直接操作数据库,例如使用 Redis。
竞争状态下如何解决商品库存超卖
对于第二个问题,需要重点说明。
常规写法:查询出对应商品的库存,判断库存数量否大于 0,然后执行生成订单等操作,但是在判断库存是否大于 0 处,如果在高并发下就会有问题,导致库存量出现负数。
测试表 sql
把如下表数据导入到数据库中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | /*
Navicat MySQL Data Transfer
Source Server : 01 本地localhost
Source Server Version : 50553
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50553
File Encoding : 65001
Date: 2020-11-06 14:31:35
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS |
---|
下单处理代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | <?php<br><br>db();<br>global $con;<br><br>//step1 接收下单参数<br>$product\_id = 1;// 商品ID<br>$buy\_num = 1;// 购买数量<br><br>//step2 查询商品信息<br>$sql = "select \* from products where id={$product\_id}";<br>$result = mysqli\_query($con, $sql);<br>$row = mysqli\_fetch\_assoc($result);<br><br>//step3 判断商品下单数量是否大于商品库存数量<br>//此处在高并发下,可能出现上一个下单后还没来得及更新库存,下一个下单判断库存数不是最新的库存<br>if ($row['store'] > 0) {<br><br> sleep(1);<br> //step4 更新商品库存数量(减去下单数量)<br> $sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br> if (mysqli\_query($con, $sql)) {<br> echo "更新成功";<br> //step5 生成订单号创建订单<br> $oid = build\_order\_no();<br> create\_order($oid, $product\_id, $buy\_num);<br> insertLog('库存减少成功,下单成功');<br> } else {<br> echo "更新失败";<br> insertLog('库存减少失败');<br> }<br><br>} else {<br> echo "没有库存";<br> insertLog('库存不够');<br>}<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>}<br><br>/\*\*<br> \* 生成唯一订单号<br> \*/<br>function build\_order\_no()<br>{<br> return date('Ymd') . str\_pad(mt\_rand(1, 99999), 5, '0', STR\_PAD\_LEFT);<br>}<br><br>function create\_order($oid, $product\_id, $number)<br>{<br> global $con;<br> $sql = "INSERT INTO `order` (oid, product\_id, number) values('$oid', '$product\_id', '$number')";<br> mysqli\_query($con, $sql);<br>}<br><br>/\*\*<br> \* 记录日志<br> \*/<br>function insertLog($content)<br>{<br> global $con;<br> $sql = "INSERT INTO `order\_log` (content) values('$content')";<br> mysqli\_query($con, $sql);<br>} |
---|
将库存字段字段设为 unsigned
因为库存字段不能为负数,在下单后更新商品库存时,如果出现负数将返回 false
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | <?php<br>db();<br>global $con;<br><br>//step1 接收下单参数<br>$product\_id = 1;// 商品ID<br>$buy\_num = 1;// 购买数量<br><br>//step2 查询商品信息<br>$sql = "select \* from products where id={$product\_id} for UPDATE";//利用for update 开启行锁<br>$result = mysqli\_query($con, $sql);<br>$row = mysqli\_fetch\_assoc($result);<br><br>//step3 判断商品下单数量是否大于商品库存数量<br>if ($row['store'] > 0) {<br><br> sleep(1);<br> //step4 更新商品库存数量(减去下单数量)<br> $sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br> if (mysqli\_query($con, $sql)) {<br> echo "更新成功";<br> //step5 生成订单号创建订单<br> $oid = build\_order\_no();<br> create\_order($oid, $product\_id, $buy\_num);<br> insertLog('库存减少成功,下单成功');<br> } else {<br> // 如果出现负数将返回false<br> echo "更新失败";<br> insertLog('库存减少失败');<br> }<br>} else {<br> //商品已经抢购完<br> echo "没有库存";<br> insertLog('库存不够');<br>}<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>}<br><br>/\*\*<br> \* 生成唯一订单号<br> \*/<br>function build\_order\_no()<br>{<br> return date('Ymd') . str\_pad(mt\_rand(1, 99999), 5, '0', STR\_PAD\_LEFT);<br>}<br><br>function create\_order($oid, $product\_id, $number)<br>{<br> global $con;<br> $sql = "INSERT INTO `order` (oid, product\_id, number) values('$oid', '$product\_id', '$number')";<br> mysqli\_query($con, $sql);<br>}<br><br>/\*\*<br> \* 记录日志<br> \*/<br>function insertLog($content)<br>{<br> global $con;<br> $sql = "INSERT INTO `order\_log` (content) values('$content')";<br> mysqli\_query($con, $sql);<br>} |
---|
使用 mysql 的事务,锁住操作的行
在下单处理过程中,使用 mysql 的事务将正在下单商品行数据锁定
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | <?php<br>db();<br>global $con;<br><br>//step1 接收下单参数<br>$product\_id = 1;// 商品ID<br>$buy\_num = 1;// 购买数量<br><br>mysqli\_query($con, "BEGIN"); //开始事务<br><br>//step2 查询商品信息<br>$sql = "select \* from products where id={$product\_id} for UPDATE";//利用for update 开启行锁<br>$result = mysqli\_query($con, $sql);<br>$row = mysqli\_fetch\_assoc($result);<br><br>//step3 判断商品下单数量是否大于商品库存数量<br>if ($row['store'] > 0) {<br><br> sleep(1);<br> //step4 更新商品库存数量(减去下单数量)<br> $sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br> if (mysqli\_query($con, $sql)) {<br> echo "更新成功";<br> //step5 生成订单号创建订单<br> $oid = build\_order\_no();<br> create\_order($oid, $product\_id, $buy\_num);<br> insertLog('库存减少成功,下单成功');<br> mysqli\_query($con, "COMMIT");//事务提交即解锁<br> } else {<br> echo "更新失败";<br> insertLog('库存减少失败');<br> mysqli\_query($con, "ROLLBACK");//事务回滚即解锁<br> }<br>} else {<br> //商品已经抢购完<br> echo "没有库存";<br> insertLog('库存不够');<br> mysqli\_query($con, "ROLLBACK");//事务回滚即解锁<br>}<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>}<br><br>/\*\*<br> \* 生成唯一订单号<br> \*/<br>function build\_order\_no()<br>{<br> return date('Ymd') . str\_pad(mt\_rand(1, 99999), 5, '0', STR\_PAD\_LEFT);<br>}<br><br>function create\_order($oid, $product\_id, $number)<br>{<br> global $con;<br> $sql = "INSERT INTO `order` (oid, product\_id, number) values('$oid', '$product\_id', '$number')";<br> mysqli\_query($con, $sql);<br>}<br><br>/\*\*<br> \* 记录日志<br> \*/<br>function insertLog($content)<br>{<br> global $con;<br> $sql = "INSERT INTO `order\_log` (content) values('$content')";<br> mysqli\_query($con, $sql);<br>} |
---|
使用非阻塞的文件排他锁
在处理下单请求的时候,用 flock 锁定一个文件,如果锁定失败说明有其他订单正在处理,此时要么等待要么直接提示用户” 服务器繁忙”,计数器存储抢购的商品数量,避免查询数据库。
阻塞 (等待) 模式:并发时,当有第二个用户请求时,会等待第一个用户请求完成、释放锁,获得文件锁之后,程序才会继续运行下去。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | <?php<br>db();<br>global $con;<br><br>//step1 接收下单参数<br>$product\_id = 1;// 商品ID<br>$buy\_num = 1;// 购买数量<br><br>$fp = fopen('lock.txt', 'w');<br>if (flock($fp, LOCK\_EX)) { //文件独占锁,阻塞<br> //step2 查询商品信息<br> $sql = "select \* from products where id={$product\_id}";<br> $result = mysqli\_query($con, $sql);<br> $row = mysqli\_fetch\_assoc($result);<br><br> //step3 判断商品下单数量是否大于商品库存数量<br> if ($row['store'] > 0) {<br> //处理订单<br> sleep(1);<br> //step4 更新商品库存数量(减去下单数量)<br> $sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br> if (mysqli\_query($con, $sql)) {<br> echo "更新成功";<br> //step5 生成订单号创建订单<br> $oid = build\_order\_no();<br> create\_order($oid, $product\_id, $buy\_num);<br> insertLog('库存减少成功,下单成功');<br> } else {<br> echo "更新失败";<br> insertLog('库存减少失败');<br> }<br> } else {<br> //商品已经抢购完<br> echo "没有库存";<br> insertLog('库存不够');<br> }<br> flock($fp, LOCK\_UN); //释放锁<br><br>}<br>fclose($fp);<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>}<br><br>/\*\*<br> \* 生成唯一订单号<br> \*/<br>function build\_order\_no()<br>{<br> return date('Ymd') . str\_pad(mt\_rand(1, 99999), 5, '0', STR\_PAD\_LEFT);<br>}<br><br>function create\_order($oid, $product\_id, $number)<br>{<br> global $con;<br> $sql = "INSERT INTO `order` (oid, product\_id, number) values('$oid', '$product\_id', '$number')";<br> mysqli\_query($con, $sql);<br>}<br><br>/\*\*<br> \* 记录日志<br> \*/<br>function insertLog($content)<br>{<br> global $con;<br> $sql = "INSERT INTO `order\_log` (content) values('$content')";<br> mysqli\_query($con, $sql);<br>} |
---|
非阻塞模式:并发时,第一个用户请求,拿得文件锁之后。后面请求的用户直接返回系统繁忙,请稍后再试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | <?php<br>db();<br>global $con;<br><br>//step1 接收下单参数<br>$product\_id = 1;// 商品ID<br>$buy\_num = 1;// 购买数量<br><br>$fp = fopen('lock.txt', 'w');<br>if (flock($fp, LOCK\_EX|LOCK\_NB)) { //文件独占锁,非阻塞<br> //step2 查询商品信息<br> $sql = "select \* from products where id={$product\_id}";<br> $result = mysqli\_query($con, $sql);<br> $row = mysqli\_fetch\_assoc($result);<br><br> //step3 判断商品下单数量是否大于商品库存数量<br> if ($row['store'] > 0) {<br> //处理订单<br> sleep(1);<br> //step4 更新商品库存数量(减去下单数量)<br> $sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br> if (mysqli\_query($con, $sql)) {<br> echo "更新成功";<br> //step5 生成订单号创建订单<br> $oid = build\_order\_no();<br> create\_order($oid, $product\_id, $buy\_num);<br> insertLog('库存减少成功,下单成功');<br> } else {<br> echo "更新失败";<br> insertLog('库存减少失败');<br> }<br> } else {<br> //商品已经抢购完<br> echo "没有库存";<br> insertLog('库存不够');<br> }<br> flock($fp, LOCK\_UN); //释放锁<br><br>} else {<br> //系统繁忙,请稍后再试<br> echo "系统繁忙,请稍后再试";<br> insertLog('系统繁忙,请稍后再试');<br>}<br>fclose($fp);<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>}<br><br>/\*\*<br> \* 生成唯一订单号<br> \*/<br>function build\_order\_no()<br>{<br> return date('Ymd') . str\_pad(mt\_rand(1, 99999), 5, '0', STR\_PAD\_LEFT);<br>}<br><br>function create\_order($oid, $product\_id, $number)<br>{<br> global $con;<br> $sql = "INSERT INTO `order` (oid, product\_id, number) values('$oid', '$product\_id', '$number')";<br> mysqli\_query($con, $sql);<br>}<br><br>/\*\*<br> \* 记录日志<br> \*/<br>function insertLog($content)<br>{<br> global $con;<br> $sql = "INSERT INTO `order\_log` (content) values('$content')";<br> mysqli\_query($con, $sql);<br>} |
---|
使用 redis 队列
1.先将商品库存到 redis 队列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | <?php<br><br>db();<br>global $con;<br><br>// 查询商品信息<br>$product\_id = 1;<br>$sql = "select \* from products where id={$product\_id}";<br>$result = mysqli\_query($con, $sql);<br>$row = mysqli\_fetch\_assoc($result);<br>$store = $row['store'];<br><br>// 获取商品在redis缓存的库存<br>$redis = new Redis();<br>$result = $redis->connect('127.0.0.1', 6379);<br>$key = 'goods\_store\_' . $product\_id;<br>$res = $redis->llen($key);<br>$count = $store - $res;<br><br>for ($i=0; $i<$count; $i++) {<br> $redis->lpush($key, 1);<br>}<br>echo $redis->llen($key);<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>} |
---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <?php<br><br>db();<br>global $con;<br><br>//step1 接收下单参数<br>$product\_id = 1;// 商品ID<br>$buy\_num = 1;// 购买数量<br><br>//step2 下单前判断redis队列库存量<br>$redis = new Redis();<br>$result = $redis->connect('127.0.0.1',6379);<br>$count = $redis->lpop('goods\_store\_' . $product\_id);<br>if (!$count) {<br> insertLog('error:no store redis');<br> return '秒杀结束,没有商品库存了';<br>}<br><br>sleep(1);<br>//step3 更新商品库存数量(减去下单数量)<br>$sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br>if (mysqli\_query($con, $sql)) {<br> echo "更新成功";<br> //step4 生成订单号创建订单<br> $oid = build\_order\_no();<br> create\_order($oid, $product\_id, $buy\_num);<br> insertLog('库存减少成功,下单成功');<br>} else {<br> echo "更新失败";<br> insertLog('库存减少失败');<br>}<br><br>function db()<br>{<br> global $con;<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br>}<br><br>/\*\*<br> \* 生成唯一订单号<br> \*/<br>function build\_order\_no()<br>{<br> return date('Ymd') . str\_pad(mt\_rand(1, 99999), 5, '0', STR\_PAD\_LEFT);<br>}<br><br>function create\_order($oid, $product\_id, $number)<br>{<br> global $con;<br> $sql = "INSERT INTO `order` (oid, product\_id, number) values('$oid', '$product\_id', '$number')";<br> mysqli\_query($con, $sql);<br>}<br><br>/\*\*<br> \* 记录日志<br> \*/<br>function insertLog($content)<br>{<br> global $con;<br> $sql = "INSERT INTO `order\_log` (content) values('$content')";<br> mysqli\_query($con, $sql);<br>} |
---|
redis 乐观锁防止超卖
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <?php<br><br>$redis =new Redis();<br>$redis->connect("127.0.0.1", 6379);<br>$redis->watch('sales');//乐观锁 监视作用 set() 初始值0<br>$sales = $redis->get('sales');<br><br>$n = 20;// 库存<br>if ($sales >= $n) {<br> exit('秒杀结束');<br>}<br><br>//redis开启事务<br>$redis->multi();<br>$redis->incr('sales'); //将 key 中储存的数字值增一 ,如果 key 不存在,那么 key 的值会先被初始化为 0 ,然后再执行 INCR 操作。<br>$res = $redis->exec(); //成功1 失败0<br><br>if ($res) {<br> //秒杀成功<br> $con = new mysqli('localhost','root','root','test');<br> if (!$con) {<br> echo "数据库连接失败";<br> }<br><br> $product\_id = 1;// 商品ID<br> $buy\_num = 1;// 购买数量<br> sleep(1);<br><br> $sql = "update products set store=store-{$buy\_num} where id={$product\_id}";<br><br> if (mysqli\_query($con, $sql)) {<br> echo "秒杀完成";<br> }<br><br>} else {<br> exit('抢购失败');<br>} |
---|
未经允许不得转载:肥猫博客 » PHP高并发情形下怎么防止商品库存超卖