mysql嵌套事务问题

保护私人版权，尊重他人版权。转载请注明出处并附带页面链接

本文主要介绍mysql中的嵌套事务问题以及php中的具体应用。

背景

Mysql官方文档对于事务处理的解释

In a nutshell: transactions could be arbitrarily nested, but when the parent transaction was committed or aborted, all its child (nested) transactions were committed or aborted as well. Commit of a nested transaction, in turn, made its changes visible, but not durable: it destroyed the nested transaction, so all the nested transaction’s changes would become visible to the parent and to other currently active nested transactions of the same parent.

事务可以是任意嵌套的，但是当父事务被提交或中止时，它的所有子事务（嵌套）事务也都被提交或中止。所有嵌套事务的更改将对父项和同一父项的其他当前活动嵌套事务可见。

####举个例子

start transaction ;
insert into tb_test values (1);
start transaction ;
insert into tb_test values (2);
commit;
rollback;

期望的结果本来是只有子事务提交，外层事务回滚，但事实是1,2都插入成功了即子事务和父事务都提交成功了。

php中的实际问题

实际项目中，很常见的一种实现场景是：方法A中使用了事务，A中调用了B方法，B内部也使用了事务。不同框架对嵌套事务的处理机制不一样。

1. Eloquent

   来看beginTransaction的实现：

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

   transactions计数加1，接着是内部createTransaction的实现

   /**
        * Create a transaction within the database.
        *
        * @return void
        */
       protected function createTransaction()
       {
           if ($this->transactions == 0) {
               try {
                   $this->getPdo()->beginTransaction();
               } catch (Exception $e) {
                   $this->handleBeginTransactionException($e);
               }
           } elseif ($this->transactions >= 1 && $this->queryGrammar->supportsSavepoints()) {
               $this->createSavepoint();
           }
       }

   首先判断是否在最外层，是的话则执行数据库层的begin命令，否则创建保存点，设置保存点的实现如下，执行数据库层的SAVEPOINT命令

   /**
    * Create a save point within the database.
    *
    * @return void
    */
   protected function createSavepoint()
   {
       $this->getPdo()->exec(
           $this->queryGrammar->compileSavepoint('trans'.($this->transactions + 1))
       );
   }

   接着看rollback方法的实现

   /**
    * Rollback the active database transaction.
    *
    * @param  int|null  $toLevel
    * @return void
    */
   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.
       $this->performRollBack($toLevel);
   
       $this->transactions = $toLevel;
   
       $this->fireConnectionEvent('rollingBack');
   }

   和beginTransaction对应的逆向操作是transactions计数-1并且重新设置执行数据库层的SAVEPOINT命令

   最后看commit方法的实现

   /**
    * Commit the active database transaction.
    *
    * @return void
    */
   public function commit()
   {
       if ($this->transactions == 1) {
           $this->getPdo()->commit();
       }
   
       $this->transactions = max(0, $this->transactions - 1);
   
       $this->fireConnectionEvent('committed');
   }

   只有在最外层执行才会真正提交，也就是说只有最后一个commit才会是真commit，其余的都是伪commit

   回到开头的例子来说

   public function handleA()
   {
       \DB::beginTransaction();
       $this->handleA1();
       $this->handleB();
       \DB::commit();
   }
   protected function handleA1()
   {
       //A内部的处理逻辑
   }
   public function handleB()
   {
       \DB::beginTransaction();
       //B内部的处理逻辑
       \DB::rollBack();
   }

   数据库层面的操作就会是

   begin;
   insert into tb_test values (3);
   savepoint p1;
   insert into tb_test values (4);
   rollback  to savepoint  p1;
   release savepoint p1;
   commit;

   最后的结果是只有3保存了4回滚没提交成功。

2. paris

   比较轻量级的框架，内部没有像Eloquent一样对嵌套事务的处理，需要手动改造

   ​

Sub

扫码关注微信公众号，随时随地，掌握最新技术资讯

微信