MySQL嵌套事务的讨论

在看MySQL Internals Manual 文档时有一段关于MySQL历史的讨论其中有一些nested transaction的讨论，有兴趣的读者可以通过下面的连接进行查看

点击打开链接

于是想实验一下现在MySQL嵌套事务怎么处理的。我的MySQL版本为5.6

首先在test中建一张及其简单的表create table t(c1 int);然后我们执行下面的sql序列

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

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

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

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

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

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

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

从上面的代码中可以看出，一开始表t中的数据只有一条1，然后我们弄了一个嵌套事务，外层插入数据2，内层插入数据3，然后内层的事务提交，外层的事务会滚

那么会得到什么结果呢，我一开始的想法时此时表t中的数据应该是1，2。但事实是

mysql> select * from t;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

这是为什么呢？？

原来start transaction 这条SQL会自动执行commit，即开启内层事务的时候，外层的插入2这条SQL事务被commit了。所以最后一个rollback没有起到任何的作用。

补充说明：

1 .除了start transaction会引起commit之外，下面的语句也会引起commit

ALTER FUNCTION  
ALTER PROCEDURE  
ALTER TABLE  
BEGIN  
CREATE DATABASE  
CREATE FUNCTION  
CREATE INDEX  
CREATE PROCEDURE  
CREATE TABLE  
DROP DATABASE  
DROP FUNCTION  
DROP INDEX  
DROP PROCEDURE  
DROP TABLE  
UNLOCK TABLES  
LOAD MASTER DATA  
LOCK TABLES  
RENAME TABLE  
TRUNCATE TABLE  
SET AUTOCOMMIT=1  
START TRANSACTION  

2. 实验的autocommit设置为1 表示每一条SQL都会自动提交，不希望这样可以set autocommit =0

3. 实验的存储引擎是InnoDB。