数据库事务

事务

数据库事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。它的特性有原子性、一致性、隔离性(read uncommitted, read committed, repeatable read and serializable)和持久性。这些特性在网上或者书本上有很详细的介绍，这里就不一一介绍了。

嵌套事务

本文主要想探索数据库中嵌套事务相关的问题，以mysql和InnoDB为载体。

实验

set autocommit=0

-- 场景1
start transaction;
    insert into test(id,name) values(1,'dog');
    start transaction;
        insert into test(id,name) values(2,'cat');
    commit;
rollback;
select * from test;
+----+------+
| id | name |
+----+------+
|  1 | dog  |
|  2 | cat  |
+----+------+


-- 场景2
delete from test;
start transaction;
    insert into test(id,name) values(1,'dog');
    start transaction;
        insert into test(id,name) values(2,'cat');
    rollback;
commit;
select * from test;
+----+------+
| id | name |
+----+------+
|  1 | dog  |
+----+------+

场景2似乎是正确的，外层的事务被提交了，而内层的事务被回滚了。但是在场景1中，并没有回滚外层事务。

结论

在mysql的“Historical Note”:

transactions could be arbitrarily nested, but when the parent transaction was committed or aborted, all its child (nested) transactions were committed or aborted as well. Commit of a nested transaction, in turn, made its changes visible, but not durable: it destroyed the nested transaction, so all the nested transaction's changes would become visible to the parent and to other currently active nested transactions of the same parent.

mysql是支持嵌套事务的，而且嵌套事务是会根据parent transaction的状态迁移的。

那为什么在实验中并没有出现上文描述的情况呢？那是因为“Current Situation”:

the DDL statement always commits the current transaction (if any) before proceeding

所以当遇到DDL的时候，会自动提交。网上有人总结了一些会自动提交的语句，如下：

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   

Savepoint

尽量不要用嵌套事务，一般来说如果需要实现局部回滚，会使用savepoint。

-- 场景1 回滚第一条语句
delete from test;
start transaction;
    savepoint savepoint1;
    insert into test(id,name) values(1,'dog');
    rollback to savepoint savepoint1;
    insert into test(id,name) values(2,'cat');
commit;
select * from test;
+----+------+
| id | name |
+----+------+
|  2 | cat  |
+----+------+


-- 场景2 回滚第二条语句
delete from test;
start transaction;
    insert into test(id,name) values(1,'dog');
    savepoint savepoint1;
    insert into test(id,name) values(2,'cat');
    rollback to savepoint savepoint1;
commit;
select * from test;
+----+------+
| id | name |
+----+------+
|  1 | dog  |
+----+------+

可以看出，通过savepoint，是可以实现局部回滚的。