前言
在商品秒杀活动中,比如商品库存只有100,但是在抢购活动中可能有200人同时抢购,这样就出现了并发,在100件商品下单完成库存为0了还有可能继续下单成功,就出现了超卖。
为了解决这个问题,今天我主要讲一下用redis队列的方式处理。redis有list类型,list类型其实就是一个双向链表。通过lpush,pop操作从链表的头部或者尾部添加删除元素。这使得list即可以用作栈,也可以用作队列。先进先出,一端进,一端出,这就是队列。在队列里前一个走完之后,后一个才会走,所以redis的队列能完美的解决超卖并发的问题。
解决秒杀超卖问题的方法还有比如:1.使用mysql的事务加排他锁来解决;2.使用文件锁实现。3.使用redis的setnx来实现锁机制等。
实现原理
将商品库存循环lpush到num里,然后在下单的时候通过rpop每次取出1件商品,当num的值为0时,停止下单。
第1步创建表
一共有三张表,分别是:订单表、商品表、日志表。
1.订单表
CREATE TABLE `ims_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_sn` char(32) NOT NULL, `user_id` int(11) NOT NULL, `status` int(11) NOT NULL DEFAULT ‘0‘, `goods_id` int(11) NOT NULL DEFAULT ‘0‘, `sku_id` int(11) NOT NULL DEFAULT ‘0‘, `number` int(11) NOT NULL, `price` int(10) NOT NULL COMMENT ‘价格:单位为分‘, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘订单表‘;
2.商品表
CREATE TABLE `ims_hotmallstore_goods` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘商品名称‘, `money` decimal(10, 2) NOT NULL COMMENT ‘售价‘, `sales` int(11) NOT NULL COMMENT ‘销量‘, `num` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of ims_hotmallstore_goods -- ---------------------------- INSERT INTO `ims_hotmallstore_goods` VALUES (1, ‘商品1‘, 1000.00, 10, 10);
第2步代码
<?php header("Content-type:text/html;charset=utf-8"); class MyPDO { protected static $_instance = null; protected $dbName = ‘‘; protected $dsn; protected $dbh; /** * 构造 * * @return MyPDO */ private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset) { try { $this->dsn = ‘mysql:host=‘.$dbHost.‘;dbname=‘.$dbName; $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd); $this->dbh->exec(‘SET character_set_connection=‘.$dbCharset.‘, character_set_results=‘.$dbCharset.‘, character_set_client=binary‘); } catch (PDOException $e) { exit($e->getMessage()); } } /** * 防止克隆 * */ private function __clone() { } /** * Singleton instance * * @return Object */ public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset) { if (self::$_instance === null) { self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset); } return self::$_instance; } /** * Query 查询 */ public function query($strSql, $queryMode = ‘All‘, $debug = false) { if ($debug === true) $this->debug($strSql); $recordset = $this->dbh->query($strSql); if ($recordset) { $recordset->setFetchMode(PDO::FETCH_ASSOC); if ($queryMode == ‘All‘) { $result = $recordset->fetchAll(); } elseif ($queryMode == ‘Row‘) { $result = $recordset->fetch(); } } else { $result = null; } return $result; } /** * Insert 插入 */ public function insert($table, $arrayDataValue, $debug = false) { $strSql = "INSERT INTO `$table` (`".implode(‘`,`‘, array_keys($arrayDataValue))."`) VALUES (‘".implode("‘,‘", $arrayDataValue)."‘)"; if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); return $result; } /** * 执行语句 */ public function execSql($strSql, $debug = false) { if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); return $result; } /** * debug * * @param mixed $debuginfo */ private function debug($debuginfo) { var_dump($debuginfo); exit(); } } class Test { private static $instance = null; // 用单列模式 实例化Redis public static function Redis() { if (self::$instance == null) { $redis=new Redis(); $redis->connect(‘127.0.0.1‘,6379); self::$instance = $redis; } return self::$instance; } public function getOne($sql) { $db = MyPDO::getInstance(‘localhost‘, ‘root‘, ‘168168‘, ‘test‘, ‘utf8‘); $data = $db->query($sql)[0]; return $data; } public function exec($sql) { $db = MyPDO::getInstance(‘localhost‘, ‘root‘, ‘168168‘, ‘test‘, ‘utf8‘); return $db->execSql($sql); } public function insert($table,$data) { $db = MyPDO::getInstance(‘localhost‘, ‘root‘, ‘168168‘, ‘test‘, ‘utf8‘); return $db->insert($table,$data); } // 将商品库存循环到lpush的num里 public function doPageSaveNum() { $redis=self::Redis(); $goods_id=1; $sql="select id, num,money from ims_hotmallstore_goods where id=".$goods_id; $goods = $this->getOne($sql); //print_r($goods);die; if(!empty($goods[‘num‘])) { for ($i=1; $i<=$goods[‘num‘]; $i++) { $redis->lpush(‘num‘,$i); } die(‘成功!库存数:‘.$goods[‘num‘]); } else { die(‘数据库已无库存‘); } } // 抢购下单 public function doPageGoodsStore() { $redis=self::Redis(); $goods_id=1; $user_id = mt_rand(1,100); if ($redis->sismember(‘user_list_‘.$goods_id,$user_id)) { echo ‘已下单‘; return false; ; } $count=$redis->rpop(‘num‘); //每次从num取出1,防止超卖 if($count==0) { $this->echoMsg(0,‘已无库存‘); } //加入已购买用户集合,防止重复购买 $redis->sAdd(‘user_list_‘.$goods_id,$user_id); $sql="select id, num, money from ims_hotmallstore_goods where id=".$goods_id; $goods = $this->getOne($sql); $this->doPageGoodsOrder($user_id,$goods,1); } public function orderNo() { return date(‘ymd‘).substr(implode(NULL, array_map(‘ord‘, str_split(substr(uniqid(), 7, 13), 1))), 0, 8); } // 下单更新库存 public function doPageGoodsOrder($user_id,$goods,$goods_number) { $orderNo=$this->orderNo(); $number=$goods[‘num‘]-$goods_number; if($number<0) { $this->echoMsg(0,‘已没有库存‘); } //mysql判断已购买用户 (自行处理) //... $order[‘user_id‘]=$user_id; $order[‘goods_id‘]=$goods[‘id‘]; $order[‘number‘]=$goods_number; $order[‘price‘]=$goods[‘money‘]; $order[‘status‘]=1; $order[‘sku_id‘]=2; $order[‘order_sn‘]=$orderNo; $order[‘create_time‘]=date(‘Y-m-d H:i:s‘); $this->insert(‘ims_order‘,$order); $sql="update ims_hotmallstore_goods set num=num-".$goods_number." where num>0 and id=".$goods[‘id‘]; $res=$this->exec($sql); // echo $sql;die; if(!empty($res)) { echo "库存扣减成功,库存剩下:$number".PHP_EOL; return false; } else { $redis=self::Redis(); $redis->lpush(‘num‘,$goods_number); //扣库存失败,把库存加回 $redis->SREM(‘user_list_‘.$goods_id,$user_id); //已购买用户集合移除 $this->echoMsg(0,‘库存扣减失败‘); } } // 保存日志 public function echoMsg($status,$msg,$exit = true) { if($exit == true) { die($msg); } else { echo $msg; } } } if(!isset($_GET[‘i‘])) { exit(‘缺失参数i‘); } // 调用--将商品库存循环到lpush的num里 if($_GET[‘i‘]==1) { $model = new Test; $model->doPageSaveNum(); } // 调用--高并发抢购下单 if($_GET[‘i‘]==2) { $model = new Test; $model->doPageGoodsStore(); } if($_GET[‘i‘]==3) { $model = new Test; for ($i=1; $i<=100; $i++) { $model->doPageGoodsStore(); } } //http://127.0.0.1/qianggou/test.php?i=1 // ab -n 2000 -c 500 http://127.0.0.1/qianggou/test.php?i=2 // (-n发出2000个请求,-c模拟500并发,请求数要大于或等于并发数。相当2000人同时访问,后面是测试url )
第3步并发测试
1.先手动执行: http://127.0.0.1/web/index.php?i=1
,将商品库存循环保存到lpush的num里。
2.这里我用Apache的ab测试,安装方法本文最后做补充。打开终端,然后执行: ab -n 1000 -c 200 http://127.0.0.1/web/index.php?i=2
(-n发出1000个请求,-c模拟200并发,请求数要大于或等于并发数。相当1000人同时访问,后面是测试url )
3.查看数据是否超发