乐观锁和悲观锁代码示例 [ 2.0 版本 ]

数据库表结构

//商品表
CREATE TABLE `qg_goods` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `good_name` varchar(64) NOT NULL DEFAULT '' COMMENT '商品名称',
  `count` tinyint(3) NOT NULL COMMENT '库存',
  `version` tinyint(3) NOT NULL COMMENT '乐观锁标识',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

//订单表
CREATE TABLE `qg_orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `order_id` varchar(20) NOT NULL DEFAULT '' COMMENT '订单号',
  `goods_id` int(11) NOT NULL COMMENT '商品id',
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

两张表必须是innodb类型的 不然事务不支持的

use frontend\models\QgGoods;
use frontend\models\QgOrders;

  //悲观锁实例操作
        $tb = Yii::$app->db->beginTransaction();
        try{
            //查询id为1的这条数据并且锁定
            $sql = "select * from qg_goods where id = 1 for update";
            $goods = Yii::$app->db->createCommand($sql)->queryOne();
            //判断库存是否大于0
            if($goods['count'] > 0){
                //打印数据且睡5秒 这期间你可以多开几个页面访问 看看页面是否等待中  测试用
                var_dump($goods);
                sleep(5);
                //将库存数量减1
               $goods1 = QgGoods::findOne(['id' => $goods['id']]);
               $goods1->count -= 1;
                //库存减1成功 就插入一条订单数据 表示抢购成功 写入抢购数据
                if($goods1->save()){
                    $order = new QgOrders();
                    $order->order_id =  date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
                    $order->goods_id =  $goods1['id'];
                    $order->user_id  =  1;
                    if($order->save()){
                        $tb->commit();
                    }
                }
            }
            else{
                var_dump('抢购结束了');
            }
        }catch(Exception $e){
            $tb->rollBack();
        }


  //乐观锁实例操作
       $tb =  Yii::$app->db->beginTransaction();
        try{
            $goods = QgGoods::findOne(['id' => 1]);
            if($goods->count > 0){
                sleep(5);
                $id = $goods['id'];
                $version = $goods['version'];
                $sql = "update qg_goods set count = count -1 ,version = version -1 where id = $id and version =  $version ";
                if(Yii::$app->db->createCommand($sql)->execute()){
                    $order = new QgOrders();
                    $order->order_id =  date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
                    $order->goods_id =  $id;
                    $order->user_id  =  1;
                    if($order->save()){
                        $tb->commit();
                    }
                }
            }else{
                var_dump('抢购结束了');
            }

        }catch(Exception $e){
            var_dump('结束了');
            $tb->rollBack();
        }
     var_dump(11);exit;