并发条件下,update 语句中直接赋值和计算的区别是什么?
学习redis
时的一个demo
表结构
- 商品信息表
CREATE TABLE `ims_hotmallstore_goods` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL COMMENT '商品名称', `type_id` int(11) NOT NULL COMMENT '商品分类', `img` text COMMENT '商品图片', `money` decimal(10,2) NOT NULL COMMENT '售价', `money2` decimal(10,2) NOT NULL COMMENT '原价', `is_show` int(11) NOT NULL DEFAULT '1' COMMENT '1.上架2.下架', `uniacid` int(11) DEFAULT NULL COMMENT '小程序id', `inventory` int(11) NOT NULL COMMENT '库存', `details` text NOT NULL COMMENT '详情', `store_id` int(11) NOT NULL COMMENT '商家id', `sales` int(11) NOT NULL COMMENT '销量', `logo` varchar(100) DEFAULT NULL, `num` int(11) NOT NULL, `is_gg` int(11) NOT NULL DEFAULT '2' COMMENT '是否开启规格', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
- 订单表
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 AUTO_INCREMENT=7521 DEFAULT CHARSET=utf8 COMMENT='订单表';
-
订单日志表
CREATE TABLE `ims_order_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` int(11) NOT NULL DEFAULT '0', `msg` text CHARACTER SET utf8, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=10466 DEFAULT CHARSET=gb2312 COMMENT='订单日志表';
demo源码
<?php include "../vendor/autoload.php"; class Test { private static $instance = null; private static $pdo = null; public static function Redis() { if (self::$instance == null ) { $redis = new Predis\Client([ 'scheme' => 'tcp', 'host' => '127.0.0.1', 'port' => 6379, ]); self::$instance = $redis; } return self::$instance; } public static function Pdo(){ if(self::$pdo==null){ $config = array(); $config['db']['host'] = 'localhost'; $config['db']['username'] = 'homestead'; $config['db']['password'] = 'secret'; $config['db']['port'] = '3306'; $config['db']['database'] = 'redisdemo'; $config['temp'] =str_replace("\\", '/',dirname(__FILE__))."/cache/";//临时目录 $config['db']['charset'] = 'utf8'; $config['db']['pconnect'] = 0; $config['db']['tablepre'] = 'ims_'; $config['domain'] = "http://".$_SERVER['HTTP_HOST']."/";//域名 $pdo = new \Crh\DB($config); // $pdo = new \Slim\PDO\Database($dsn, $usr, $pwd); self::$pdo = $pdo; } return self::$pdo; } // 将商品库存循环到lpush的num里 public function doPageSaveNum() { $redis=self::Redis(); $pdo = self::Pdo(); $goods_id=1; $sql="select id, num, money from ims_hotmallstore_goods where id=".$goods_id; $goods = $pdo->fetch($sql); if(!empty($goods)){ for($i=1; $i<=$goods['num']; $i++){ $redis->lpush('num',$i); } die('成功!'); }else{ $this->echoMsg(0,'商品不存在。'); } } // 抢购下单 public function doPageGoodsStore() { $pdo = self::Pdo(); $goods_id=1; $sql="select id, num, money from ims_hotmallstore_goods where id=".$goods_id; $goods = $pdo->fetch($sql); $redis=self::Redis(); $count=$redis->rpop('num');//每次从num取出1 if($count==0){ $this->echoMsg(0,'no num redis'); } $this->doPageGoodsOrder($goods,1); } // 保存日志 public function echoMsg($status,$msg,$_data="") { $pdo = self::Pdo(); $data=json_encode(array('status'=>$status,'msg'=>$msg,'data'=>$_data),JSON_UNESCAPED_UNICODE); $order_log['status']=$status; $order_log['msg']=$msg; $order_log['create_time']=date('Y-m-d H:i:s'); $goods = $pdo->insert('order_log', $order_log); die($data); } public function orderNo() { return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8); } // 下单更新库存 public function doPageGoodsOrder($goods,$goods_number) { $pdo = self::Pdo(); $orderNo=$this->orderNo(); $number=$goods['num']-$goods_number; if($number<0){ $this->echoMsg(0,'已没有库存'); } $user_id=rand(1,500); $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'); $pdo->insert('order', $order); // 问题所在 // $sql="update ims_hotmallstore_goods set num=num-".$goods_number." where num>0 and id=".$goods['id']; $sql="update ims_hotmallstore_goods set num=$number where num>0 and id=".$goods['id']; $res = $pdo->query($sql); if(!empty($res)){ $this->echoMsg(1,'库存扣减成功'.$number); } $redis=self::Redis(); $redis->lpush('num',$goods_number); $this->echoMsg(0,'库存扣减失败'.$number); } } // 调用--将商品库存循环到lpush的num里 if($_GET['i']==1){ $model = new Test; $model->doPageSaveNum(); } // 调用--高并发抢购下单 if($_GET['i']==2){ $model = new Test; $model->doPageGoodsStore(); }
使用
-
初始化数据
http://redis.test/demo/test.php?i=1
-
使用
http://redis.test/demo/test.php?i=2
结果
以抢购100件商品为例,商品表num=100
在函数doPageGoodsOrder
中,如果使用如下sql
$sql="update ims_hotmallstore_goods set num=$number where num>0 and id=".$goods['id'];
即直接更新数据,不在sql中运算,在使用apache ab测试时
ab -n 1000 -c 100 http://redis.test/demo/test.php?i=2
发现:
- 商品表,num字段不为0
- 订单表,生成456份订单
- 日志表,1000条日志,其中抢购成功的即
status=1
的是100条
如果在sql中计算,即:
$sql="update ims_hotmallstore_goods set num=num-".$goods_number." where num>0 and id=".$goods['id'];
结果正常,num=0
,订单 100 条,日志1000条
问题
- 为什会出现这种现象(猜测是锁,但不了解具体的机制,正在学习)
- 这个demo,是否能算一个解决超卖的基本方法
谢谢!
推荐文章: