怎么给公司做微网站/营销策划36计

写在之前

关于mysql 的事务嵌套可以查看这个地址：
https://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

里面有这么一句话。

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

大体意思是db不支持事务嵌套，如果你嵌套执行START TRANSACTION时会隐式执行commit

我们做个测试：

mysql>  BEGIN;
Query OK, 0 rows affected (0.16 sec)mysql> INSERT INTO T2 VALUES(300);
Query OK, 1 row affected (0.29 sec)mysql>  BEGIN;
Query OK, 0 rows affected (0.04 sec)mysql> rollback;
Query OK, 0 rows affected (0.04 sec)mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  300 |
+------+
2 rows in set (0.04 sec)

果然，我们直接rollback上面的语句，但是还是执行了查询操作。

laravel之嵌套事务transactions实现

为啥官网不支持，但是 laravel 框架却优雅的实现了事务嵌套，我们来看看它的实现原理。

调用示例：

\DB::beginTransaction();    //主事务
try{\DB::beginTransaction(); //子事务\DB::insert('insert into T2 set ID=100');\DB::rollBack();         //子事务回滚\DB::insert('insert into T2 set ID=200');\DB::commit();
}catch (\Exception $e) {\DB::rollBack();echo $e->getMessage();exit;
}

查看执行结果：

mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  100 |
+------+
1 row in set (0.05 sec)

说明子事务成功回滚了，下面看下子事务的实现。

代码分析：

laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php 90行

public function beginTransaction(){$this->createTransaction();$this->transactions++;$this->fireConnectionEvent('beganTransaction');}

每调一次beginTransaction会使$this->transactions加1接着看一下$this->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();}}

if ($this->transactions == 0) 首先判断是否在事务中。

没有在事务中则执行 $this->getPdo()->beginTransaction()
相当于执行 BEGIN;

在事务中执行 $this->createSavepoint(); 下面是createSavepoint方法的实现。

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

这里相当于在mysql里执行 SAVEPOINT trans1;

下面看下rollback方法实现：

public function rollBack($toLevel = null)
{$toLevel = is_null($toLevel)? $this->transactions - 1: $toLevel;if ($toLevel < 0 || $toLevel >= $this->transactions) {return;}$this->performRollBack($toLevel);$this->transactions = $toLevel;$this->fireConnectionEvent('rollingBack');
}

首先rollback会使$this->transactions减一。然后调用$this->performRollBack

protected function performRollBack($toLevel)
{if ($toLevel == 0) {$this->getPdo()->rollBack();} elseif ($this->queryGrammar->supportsSavepoints()) {$this->getPdo()->exec($this->queryGrammar->compileSavepointRollBack('trans'.($toLevel + 1)));}
}

performRollBack方式实际就是在重新设定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方法，只有在最外层时才会真正的提交。

总结：

1. 基本实现原理是 savepoint
2. 通过$this->transactions对应的数值设定 不同的savepoint实现不同层次嵌套
3. 只有在最后一个commit时才会真正提交请求。

SAVEPOINT 使用demo如下：

mysql> CREATE TABLE T2(ID INT);
Query OK, 0 rows affected (0.05 sec)mysql> select * from T2;
Empty set (0.17 sec)mysql> BEGIN;
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO T2 VALUES(100);
Query OK, 1 row affected (0.04 sec)mysql> SAVEPOINT trans1;
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO T2 VALUES(200);
Query OK, 1 row affected (0.04 sec)mysql> ROLLBACK TO SAVEPOINT trans1;
Query OK, 0 rows affected (0.04 sec)mysql> RELEASE SAVEPOINT trans1;
Query OK, 0 rows affected (0.03 sec)mysql> commit;
Query OK, 0 rows affected (0.04 sec)mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  100 |
+------+
1 row in set (0.05 sec)