Laravel如何优雅的解决MySQL的事务嵌套

CREATE TABLE `transcation_test` ( `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `sex` char(2) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;INSERT INTO `transcation_test` VALUES('Jasper','男');ROLLBACK;

SELECT * FROM `transcation_test`;//结果符合我们的预期：Empty set (0.00 sec)

BEGIN;INSERT INTO `transcation_test` VALUES('Jasper','男');BEGIN;ROLLBACK;

+--------+-----+| name   | sex |+--------+-----+| Jasper | 男  |+--------+-----+1 row in set (0.00 sec)

/*扣除用户积分发放礼品*/public function giveOutGifts(){  try{      DB::beginTransaction();  //开启事务      FlowRecord::save();     //保存流水      User::decPoint();       //用户减少积分      Gift::Receive();        //领取礼品      DB::commit();  }catch (\Exception $e) {      DB::rollBack();      return $e->getMessage()  }}
/*领取积分@param bool $directRelease 是否直接发放礼品*/public function collectPoints($directRelease=false){  try{      DB::beginTransaction();  //开启事务      FlowRecord::save();     //保存流水      User::incPoint();       //用户添加积分      if($directRelease){        $this->giveOutGifts();  //发放礼品      }      DB::commit();  }catch (\Exception $e) {      DB::rollBack();      return $e->getMessage()  }}

public function receiveAndGiveOutGifts(Request $request){    $this->checkParam();  //校验参数    //调用服务层完成逻辑    $this->service->collectPoints(true);}

SAVEPOINT identifierROLLBACK [WORK] TO [SAVEPOINT] identifierRELEASE SAVEPOINT identifier

BEGIN;SAVEPOINT for_kiki;INSERT INTO `transcation_test` VALUES('KiKi','女');SAVEPOINT for_kevin;INSERT INTO `transcation_test` VALUES('Kevin Drunt','男');ROLLBACK TO for_kevin;COMMIT;

mysql> select * from transcation_test ;+--------+-----+| name   | sex |+--------+-----+| Jasper | 男  || KiKi   | 女  |+--------+-----+2 rows in set (0.00 sec)

DB::beginTransaction();    //主事务try{    DB::beginTransaction(); //子事务    DB::insert("insert into base_transcation_test values('Kevin Durant','男')");    DB::rollBack();         //子事务回滚    DB::insert("insert into base_transcation_test values('Taylor Swift','女')");    DB::commit();}catch (\Exception $e) {    DB::rollBack();    return $e->getMessage();}

+--------------+-----+| name         | sex |+--------------+-----+| Jasper       | 男  || Taylor Swift | 女  |+--------------+-----+

/**
  * Start a new database transaction.
  *
  * @return void
  *
  * @throws \Throwable
 */
public function beginTransaction(){
  $this->createTransaction();
  $this->transactions++;
  optional($this->transactionsManager)->begin(
    $this->getName(), $this->transactions
  );
  $this->fireConnectionEvent('beganTransaction');
}

protected function createTransaction(){
  //判断是否在事务中
  if ($this->transactions == 0) {
      $this->reconnectIfMissingConnection();
      try {
        $this->getPdo()->beginTransaction();
      } catch (Throwable $e) {
        $this->handleBeginTransactionException($e);
      }
  } elseif ($this->transactions >= 1 && $this->queryGrammar->supportsSavepoints()) {
    $this->createSavepoint();
  }
}

protected function createSavepoint(){
    $this->getPdo()->exec(
        $this->queryGrammar->compileSavepoint('trans'.($this->transactions + 1))
    );
}

public function rollBack($toLevel = null){
  // We allow developers to rollback to a certain transaction level. We will verify
  // that this given transaction level is valid before attempting to rollback to
  // that level. If it's not we will just return out and not attempt anything.
  $toLevel = is_null($toLevel)
    ? $this->transactions - 1
    : $toLevel;
  if ($toLevel < 0 || $toLevel >= $this->transactions) {
    return;
  }
  // Next, we will actually perform this rollback within this database and fire the
  // rollback event. We will also set the current transaction level to the given
  // level that was passed into this method so it will be right from here out.
  try {
    $this->performRollBack($toLevel);
  } catch (Throwable $e) {
    $this->handleRollBackException($e);
  }
  $this->transactions = $toLevel;
  optional($this->transactionsManager)->rollback(
    $this->getName(), $this->transactions
  );
  $this->fireConnectionEvent('rollingBack');
}

//执行数据库回滚
protected function performRollBack($toLevel){
  if ($toLevel == 0) {
    $this->getPdo()->rollBack();
  } elseif ($this->queryGrammar->supportsSavepoints()) {
    $this->getPdo()->exec(
      //回滚指定级别的回滚点
      $this->queryGrammar->compileSavepointRollBack('trans'.($toLevel + 1))
    );
  }
}

public function commit(){
  if ($this->transactions == 1) {
    $this->getPdo()->commit();
  }
  $this->transactions = max(0, $this->transactions - 1);
  if ($this->transactions == 0) {
    optional($this->transactionsManager)->commit($this->getName());
  }
  $this->fireConnectionEvent('committed');
}

//主事务 $this->transcation = 1，此时还没有savepoint
DB::beginTransaction();   
try{
  //子事务 $this->transcation = 2，此时savepoint = 2
    DB::beginTransaction();
    DB::insert("insert into base_transcation_test values('Kevin Durant','男')");
    //这时子事务回滚 $this->transcation = 2-1 = 1，调用执行回滚点的操作，回滚savepoint=2的操作
    DB::rollBack();        
    DB::insert("insert into base_transcation_test values('Taylor Swift','女')");
    //$this->transcation=1,在最外层，可以提交
    DB::commit();
}catch (\Exception $e) {
    DB::rollBack();
    return $e->getMessage();
}