thinkphp5+mysql事务案例

  • 内容
  • 评论
  • 相关

mysql的表存储引擎必须是innodb,事务就是多条sql其中一个执行失败就回滚,都执行成功才一起提交。保证多条sql要么都执行成功,要么都不成功。但是事务并不能避免高并发带来的数据错乱问题。如何解决高并发带来的数据错乱问题会单独写一篇文章详细阐述。

如下是一个下单事务案例:1.一定要注意update语句返回受影响的行记录,如果受影响行为0,一定要手动抛出异常,在catch里面统一处理。2.商品库存字段一定要设置成无符号,一单更新成负数sql也会自动抛出异常。3.update语句的where条件一定要增加上库存大于0,等类似这样的条件,这样做的好处就是如果之前更新过,那么这次更新返回受影响行就是0,在结合手动抛出异常,程序也回滚。

 

  /**
    * 下单方法
    */
    public function add_order () {
  
        //sleep(20);die; //模拟超时
        //获取平台客户和活动信息
        $clientkeynum = $this->clientkeynum;
        $activity_info = $this->activity_info;
        $bianhao = $activity_info['activity_code'];

        $start_time=$activity_info["start_time"];
        $end_time=$activity_info["end_time"];
        //如果小于开始时间内
        if(time()<$start_time){
            $rst['sta'] = "0";
            $rst['msg'] = '活动还未到开始时间!请耐心等待!';
            echo json_encode$rst );die;
        }
        //如果大于结束时间
        if(time()>$end_time){
            $rst['sta'] = "0";
            $rst['msg'] = '活动已经结束,期待您下次参与!';
            echo json_encode$rst );die;
        }
        //如果活动不可用
        $status=$activity_info["status"];
        if($status!='1'){
            $rst['sta'] = "0";
            $rst['msg'] = '活动已被禁用!';
            echo json_encode$rst );die;
        }
        //活动是否归档
        $is_over=$activity_info["is_over"];
        if($is_over=='1'){
            $rst['sta'] = "0";
            $rst['msg'] = '活动已归档!';
            echo json_encode$rst);die;
        }  

        $request = Request::instance();
        $param = $request->param();
        $name = trim$param['name'] );
        $phone = trim$param['phone'] );
        $province = trim$param['province'] );
        $city = trim$param['city'] );
        $area = trim$param['area'] );
        $address = trim$_REQUEST['address'] );
        //常规业务逻辑
        if ( $name == '' ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,收货人姓名不能为空!';
            echo json_encode$rt );
            die;
        }
        if ( $phone == '' ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,收货人手机号不能为空!';
            echo json_encode$rt );
            die;
        }
        //php手机号正则校验
        if ( !preg_match'/^0?(1|1|1|1|1)[0-9]{10}$/'$phone ) ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,您输入的手机号格式不正确!';
            echo json_encode$rt );
            die;

        }
        if ( $province == '请选择' || $city == '请选择' || $area == '请选择' ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,请选择收货人地址!';
            echo json_encode$rt );
            die;
        }
        if ( $address == '' ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,收货人详细地址不能为空!';
            echo json_encode$rt );
            die;
        }

        //档次手机号 和选择商品信息
        $activity_grade_phone_info = session'icbc_activity_grade_phone_info' );
        $cart_good_info = session'icbc_cart_good_info' );
        if ( empty$activity_grade_phone_info ) ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,档次里面手机号信息丢失!';
            echo json_encode$rt );
            die;
        }
        if ( empty$cart_good_info ) ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起,请去重新选择商品!';
            echo json_encode$rt );
            die;
        }

        $daoru_phone = $activity_grade_phone_info['phone'];
        $table_name = 'client_activity_grade_phone_'.$bianhao;
        $goodid = $cart_good_info['id'];
        //校验是否可以兑换
        //名单表
        
        $activity_grade_phone_info = Db::table$table_name )->where'phone'$daoru_phone )->where'clientkeynum'$clientkeynum )->find();
        if ( $activity_grade_phone_info['is_order'] != '0' ) {
            $rt['sta'] = '0';
            $rt['msg'] = '您已经兑换过礼品了!';
            echo json_encode$rt );
            die;
        }
        
        //兑换的产品必须在当前档次里面
        $grade_id=$activity_grade_phone_info['grade_id'];
        $grade_good_arrDb::table'client_activity_grade_good' )->where'clientkeynum'$clientkeynum )->where'grade_id'$grade_id )->column("good_id");
        if(!in_array($goodid,$grade_good_arr)){
            $rt['sta'] = '0';
            $rt['msg'] = '对不起产品范围异常,请重新兑换!';
            echo json_encode$rt );
            die;
        }
       
        //同一个活动同一个人只能兑换一次
        $orderinfo_count = Db::table'client_order_info' )->where'clientkeynum'$clientkeynum )->where'daoru_phone'$daoru_phone )->where'activity_id'$activity_grade_phone_info['activity_id'] )->count();
        if ( $orderinfo_count>1 ) {
            $rt['sta'] = '0';
            $rt['msg'] = '同一个手机号同一个活动只能兑换一次!';
            echo json_encode$rt );
            die;
        }
        //库存
        $client_good_info = Db::table'client_good' )->where'id'$goodid )->where'clientkeynum'$clientkeynum )->find();

        if ( $client_good_info['stock']<1 ) {
            $rt['sta'] = '0';
            $rt['msg'] = '对不起该产品已经没有了库存!';
            echo json_encode$rt );
            die;
        }
        //获取活动详情
        $activity_id=$activity_grade_phone_info['activity_id'];
        $activity_info=Db::table'client_activity' )->where'id'$activity_id )->where'clientkeynum'$clientkeynum )->find();
        $activity_name=$activity_info['activity_name'];
        $project_id=$activity_info['project_id'];

        //订单唯一标识
        $order_keynum=create_guid();
        // 启动事务
        $trans_result = true;
        Db::startTrans();
        try {
            //订单表
            $order_sn = 'D' .create_order_sn();
            $order_info['order_sn'] = $order_sn;
            $order_info['name'] = $name;
            $order_info['phone'] = $phone;
            $order_info['province'] = $province;
            $order_info['city'] = $city;
            $order_info['area'] = $area;
            $order_info['address'] = $address;
            $order_info['add_time'] = time();
            $order_info['order_status'] = '0';
            $order_info['add_time'] = time();
            $order_info['goodid'] = $cart_good_info['id'];
            $order_info['goodimg'] = $cart_good_info['goods_thumb'];
            $order_info['goodsku'] = $cart_good_info['goodssku'];
            $order_info['goodname'] = $cart_good_info['goodsname'];
            $order_info['goodsintegral'] = $cart_good_info['goodsintegral'];
            $order_info['market_integral'] = $cart_good_info['market_integral'];
            $order_info['keynum'] = $order_keynum;
            $order_info['clientkeynum'] = $clientkeynum;
            $order_info['activity_id'] = $activity_grade_phone_info['activity_id'];
            $order_info['activity_name'] = $activity_name;
            $order_info['project_id'] = $project_id;
            $order_info['grade_id'] = $activity_grade_phone_info['grade_id'];
            $order_info['referer'] =  $_SERVER['HTTP_USER_AGENT'];
            $order_info['daoru_phone'] = $daoru_phone;
            //同一个活动同一个达标手机号只能有一个订单,数据库达标手机号daoru_phone和活动activity_id两个字段一起做unique索引,一旦重复了,错误也会自动到catch里面
            $order_id = Db::table'client_order_info' )->insertGetId$order_info );
            //手动抛出异常,如果insert的sql出错也会把异常抛出到catch里面
            if ( !$order_id ) {
                throw new \Exception'insert,client_order_info失败!' );
            }

            //修改档次下面名单表, Affected rows: 0 也会成功, 所以手动抛出异常,在catch里面记录异常信息日志
            $grade_phone['order_sn'] = $order_sn;
            $grade_phone['order_keynum'] = $order_keynum;
            $grade_phone['order_id'] =$order_id;
            $grade_phone['is_order'] = "1";
            $grade_phone['order_time'] = time();
            //update语句,where条件要千万注意,增加上is_order='0',这样如果这条记录更新过,那么update返回影响的记录行就是0,就能进入下面的手动抛出异常
            $flag = Db::table$table_name )->where('is_order','1')->where'phone'$daoru_phone )->where'clientkeynum'$clientkeynum )->update$grade_phone );
            if ( !$flag ) {
                logResDb::table$table_name )->getLastSql(), 'order' );
                throw new \Exception'update'.$table_name.'失败!' );
            }
            //订单日志
            $order_log['order_sn'] = $order_sn;
            $order_log['action_user'] = '前台客户';
            $order_log['action_note'] = '前台客户下单';
            $order_log['add_time'] = time();
            $order_log['clientkeynum'] = $clientkeynum;
            $log_id = Db::table'client_order_log' )->insertGetId$order_log );
            if ( !$order_id ) {
                throw new \Exception'insert,client_order_log失败!' );
            }

            //减去产品库存 where条件要注意增加stock>0,其次数据库也要把库存字段stock类型改为无符号UNSIGNED,一旦更新成负数,也能在catch中自动捕获异常,从而回滚,保证库存别卖超
            $sql = "update client_good set stock=stock-1 where  stock>0 and  clientkeynum='$clientkeynum'  and id='$goodid'";
            $stock_flag = Db::execute$sql );
            if ( !$stock_flag ) {
                throw new \Exception'update,client_good的库存失败!' );
            }

            Db::commit();
        } catch ( \Exception $e ) {
            // 回滚事务
            Db::rollback();
            $trans_result = false;
            $msg = $e->getMessage();
            logRes'订单提交失败!--》'.$msg'order' );

        }

        //如果失败
        if ( !$trans_result ) {
            $rt['sta'] = '0';
            $rt['msg'] = '兑换失败!'.$msg;
            echo json_encode$rt );
            die;
        }
        //清除session信息,保存订单信息
        $order_info = Db::table'client_order_info' )->where"order_id='$order_id'" )->find();
        session'icbc_order_info'$order_info );
        //存入session
        Session::delete'icbc_activity_grade_phone_info' );
        Session::delete'icbc_cart_good_info' );
        $rt['sta'] = '1';
        $rt['msg'] = '兑换成功';
        echo json_encode$rt );
        die;

    }

 

本文标签:

版权声明:若无特殊注明,本文皆为《菜鸟站长》原创,转载请保留文章出处。

本文链接:thinkphp5+mysql事务案例 - https://wlphp.com/?post=247

发表评论

电子邮件地址不会被公开。 必填项已用*标注