实际开发中由于多个业务逻辑可能存在嵌套关系，因此存在事务之间嵌套的处理。在Mysql中可通过SAVEPOINT来达到嵌套的目的。

语法

SAVEPOINT identifier 创建节点，在事务中可直接回退到此节点
RELEASE SAVEPOINT identifier 释放节点，不会触发COMMIT或ROLLBACK
ROLLBACK [WORK] TO [SAVEPOINT] identifier 回滚到某个节点，不影响回滚节点之前的变更，此节点之后的变更会被回滚

业务逻辑

开启事务 如果存在已经开启的事务则创建 SAVEPOINT，否则执行BEGIN

回滚 如果存在SAVEPOINT则 ROLLABCK TO SAVEPOINT,否则执行 ROLLBACK

提交 如果还存在有SAVEPOINT则 RELEASE SAVEPOINT，否则执行COMMIT

应用场景

mysql> create table t1 (c1 int primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)

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

mysql> savepoint b;
Query OK, 0 rows affected (0.00 sec)

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

mysql> rollback to savepoint a;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

引用

1. SAVEPOINT官方文档
2. SAVEPOINT原理