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 ;
}
