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_arr= Db::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 ) {
logRes( Db::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://wziyi.com.cn/?post=247
