mysql 事务嵌套

MySQL版本：5.7.30

简介：

        有个同事在一个事务中调用了一个存储过程，然后在回滚的时候发现，事务中的部分数据已经提交了，帮同事分析过程可以使用一个简单例子来进行表述。

创建一张表：

CREATE TABLE `test1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `col` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

查看数据库自动提交是否已开启：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.02 sec)

自动提交已开启。

写入两条数据：

mysql> insert into test1(col) values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

测试事务：

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 1 |

| 2 | 2 |

+----+-----+

2 rows in set (0.03 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update test1 set col = 3 where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 3 |

| 2 | 2 |

+----+-----+

2 rows in set (0.04 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update test1 set col = 4 where id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 3 |

| 2 | 4 |

+----+-----+

2 rows in set (0.04 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 3 |

| 2 | 2 |

+----+-----+

2 rows in set (0.05 sec)

以上过程可以发现，在事务内部嵌套的事务开始之前的update生效了，导致外层事务后面的rollback没有实现完全回滚到事务初始状态。

参考官方文档：MySQL :: MySQL 5.7 Reference Manual :: 13.3.3 Statements That Cause an Implicit Commit

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.

事务不能嵌套，当嵌套的事务发出start transaction指令时，当前事务已执行的部分会进行一次隐式提交，所以以下语句已经commit了：

update test1 set col = 3 where id = 1;

后续的rollback操作当然不会回滚该已经提交的部分。

突然想到，是否可以利用这个特性来干预事务，将事务内部分成几个部分，通过不同的条件让事务实现不同程度的commit？

其实，还有一个现成的方法可以使用，通过savepoint来对事务进行干预。

以下为测试过程：

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 1 |

| 2 | 2 |

+----+-----+

2 rows in set (0.06 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update test1 set col = 3 where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> savepoint sp1;

Query OK, 0 rows affected (0.00 sec)

mysql> update test1 set col = 4 where id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 3 |

| 2 | 4 |

+----+-----+

2 rows in set (0.05 sec)

mysql> rollback to savepoint sp1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+-----+

| id | col |

+----+-----+

| 1 | 3 |

| 2 | 2 |

+----+-----+

2 rows in set (0.06 sec)

以上测试的效果同前面的事务嵌套的效果相同，可见，的确可以通过savepoint来对事务进行控制，可以让事务实现部分提交的效果。

小结：

1. 事务不能嵌套；

2. 可以通过savepoint对事务实现部分提交的效果。