mysql
官方并不支持事务嵌套,当第二个事务开始时候,会隐式的先调用commit
提交之前的事务,而后在开启第二次的事务。
在正常开发中难免会出现失误嵌套的情况。如何解决呢?
laravel/lumen
框架使用了mysql
的SAVEPOINT
方法来解决这一问题。
mysql savepoint
首先来看下mysql
原生案例。
1 2 3 4 5 6 7 8 9 |
# 表结构 +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(32) | NO | | NULL | | | password | varchar(32) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ |
2次开启事务案例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# 操作 mysql> begin; # 开启事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into user(username, password) values('ff', 'ff'); Query OK, 1 row affected (0.00 sec) mysql> begin; # 此处mysql会先提交前一个事务,再开启后一个事务。 Query OK, 0 rows affected (0.01 sec) mysql> insert into user(username, password) values('ee', 'ee'); Query OK, 1 row affected (0.00 sec) mysql> rollback; # 事务回滚,把username=ee回滚掉了,而ff已插入数据库。 Query OK, 0 rows affected (0.00 sec) |
savepoint
案例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> begin; # 开启事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into user(username, password) values('ff', 'ff'); Query OK, 1 row affected (0.00 sec) mysql> SAVEPOINT trans_1; # 创建回滚点trans_1 Query OK, 0 rows affected (0.00 sec) mysql> insert into user(username, password) values('ee', 'ee'); Query OK, 1 row affected (0.00 sec) mysql> SAVEPOINT trans_2; # 创建回滚点trans_2 Query OK, 0 rows affected (0.00 sec) mysql> insert into user(username, password) values('gg', 'gg'); Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK TO SAVEPOINT trans_2; # 回滚到trans_2,将username=gg回滚掉了 Query OK, 0 rows affected (0.00 sec) mysql> insert into user(username, password) values('hh', 'hh'); Query OK, 1 row affected (0.00 sec) mysql> commit; # 提交数据 Query OK, 0 rows affected (0.00 sec) |
上面例子中username=gg
被回滚掉了,最终数据库中插入了username=ee,ff,hh
。如果最后commit
换成rollback
所有数据将全部回滚。
laravel/lumen 实现
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/** * Start a new database transaction. * * @return void */ public function beginTransaction() { ++$this->transactions; if ($this->transactions == 1) { $this->getPdo()->beginTransaction(); } elseif ($this->transactions > 1 && $this->queryGrammar->supportsSavepoints()) { $this->getPdo()->exec( $this->queryGrammar->compileSavepoint('trans'.$this->transactions) ); } $this->fireConnectionEvent('beganTransaction'); } |
开启事务时检测当前transactions
等级,如果等于1
开启事务,如果大于1
则创建相应level
的savepoint
。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/** * Commit the active database transaction. * * @return void */ public function commit() { if ($this->transactions == 1) { $this->getPdo()->commit(); } --$this->transactions; $this->fireConnectionEvent('committed'); } |
commit
操作时候,检测当前transactions
等级,如果不等于1
跳过操作,并且transactions
等级减1
。如果等于1
,事务提交。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/** * Rollback the active database transaction. * * @return void */ public function rollBack() { if ($this->transactions == 1) { $this->getPdo()->rollBack(); } elseif ($this->transactions > 1 && $this->queryGrammar->supportsSavepoints()) { $this->getPdo()->exec( $this->queryGrammar->compileSavepointRollBack('trans'.$this->transactions) ); } $this->transactions = max(0, $this->transactions - 1); $this->fireConnectionEvent('rollingBack'); } |
而rollback
操作,检测当前transactions
等级,如果不等于1
回滚到相应等级的savepoint
,等于1
这全部回滚。