mysql-事务嵌套之savepoint

背景

最近业务开发中有发现公司自研框架会发生事务嵌套问题导致事务开启异常的一些问题，搜索发现laravel是通过savepoint 实现的嵌套处理，特此做记录。

mysql支持事务嵌套吗

不支持。官方文档有说明
因为每次开启事务都会隐式的执行commit

• 验证 START TRANSACTION 会隐式提交语句
  start transaction 后自动提交已开启的事务 ，如图。

	解析：（当前事务隔离级别 可重复读）
	1. 清空老表数据 t1
	2.  set autocommit=0; 关闭自动提交
	3. 开启事务 start transaction;
	4. 执行插入语句，因为未提交，执行完成后session2 未查询到
	5. 执行 start transaction;
	6. session2 查询发现已经查询到 插入的数据 ，说明事务已经隐式提交

框架如何实现嵌套的呢？

可以参见 事务嵌套
基本就是借助了savepoint 实现了回滚到指定的点

what’s savepoint

官方文档 savepoint

13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK.
The SAVEPOINT statement sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.
The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.
If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists:
ERROR 1305 (42000): SAVEPOINT identifier does not exist
The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.
All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.
A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.

InnoDB 支持 SQL 语句 SAVEPOINT、ROLLBACK TO SAVEPOINT、RELEASE SAVEPOINT 和 ROLLBACK 的可选 WORK 关键字。

SAVEPOINT 语句使用标识符名称设置命名事务保存点。 如果当前事务有一个同名的保存点，则删除旧的保存点并设置一个新的保存点。

ROLLBACK TO SAVEPOINT 语句将事务回滚到指定的保存点而不终止事务。 当前事务在设置保存点后对行所做的修改在回滚中被撤销，但 InnoDB 不会释放保存点后存储在内存中的行锁。 （对于新插入的行，锁信息由存储在行中的事务ID携带；锁不单独存储在内存中。在这种情况下，行锁在undo中释放。） 晚于指定的保存点被删除。

动手savepoint 实现效果

mysql> delete from t1;   -- 清空测试表
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;  -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (name) values(1); -- 插入一条 name=1
Query OK, 1 row affected (0.00 sec)

mysql> savepoint p1;-- 保存回滚点p1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (name) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint p2 ; -- 保存回滚点p2
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (name) values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (name) values(4);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint p3; -- 保存回滚点p3
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to p2; ; -- 回滚点p2
Query OK, 0 rows affected (0.00 sec)

mysql> commit; -- 提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 \G; -- 查询数据 发现只有p2之前的数据保存了，即实现了回滚至指定点。
*************************** 1. row ***************************
        id: 9
      name: 1
       age: 0
  varchar8:
varchar100:
*************************** 2. row ***************************
        id: 10
      name: 2
       age: 0
  varchar8:
varchar100:
2 rows in set (0.00 sec)

ERROR:
No query specified