每日MySQL之015：MySQL中的自动提交(autocommit)与savepoint

本文所述内容均在InnoDB存储引擎下。

1. 自动提交的关闭与打开

MySQL默认是打开了自动提交的。关闭自动提交有以下办法
1. Session级别：使用 START TRANSACTION 或者 BEGIN 来开始一个事务，使用ROLLBACK/COMMIT来结束一个事务。
2. Session级别：SET autocommit=0关闭当前session的自动提交。
3. 全局级别：SET GLOBAL autocommit=0关闭全局的自动提交。

示例：

shell> mysql test1
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));

mysql> START TRANSACTION;
mysql> INSERT INTO customer VALUES (10, 'Heikki');
mysql> COMMIT;

mysql> SET autocommit=0;
mysql> INSERT INTO customer VALUES (15, 'John');
mysql> INSERT INTO customer VALUES (20, 'Paul');
mysql> DELETE FROM customer WHERE b = 'Heikki';
mysql> ROLLBACK;

mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)

2. 无法被回滚的语句

DDL语句无法被回滚，比如 create database/drop database, create/drop/alter table/routine。

3. 隐式发出commit的语句

有些语句，在执行之前，会隐式地发出commit。这些语句包括：
3.1 DDL语句
3.2 隐式地使用或者修改 mysql数据库的语句，比如 ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD
3.3 事务控制和锁语句，比如BEGIN,START TRANSACTION,  LOCK/UNLOCK TABLES, SET autocommit = 1(原来不是1)
3.4 LOAD数据语句， LOAD DATA INFILE
3.5 管理语句，比如 ANALYZE TABLE, CACHE INDEX, CHECK TABLE等等
3.6 复制控制语句，比如 START/STOP/RESET SLAVE, CHANGE MASTER TO

示例：

mysql> set autocommit=1;

mysql> BEGIN;
mysql> CREATE TABLE T2(ID INT);
mysql> INSERT INTO T2 VALUES(100);
mysql> ROLLBACK;

mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

这里之所以已经ROLLBACK了，还是能看到插入了数据，是因为CREATE语句已经隐式地commit了。之后的语句都是自动提交的。

4. SAVEPOINT语法如下：

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

示例：

mysql> CREATE TABLE T2(ID INT);

mysql> BEGIN;
mysql> INSERT INTO T2 VALUES(100);
mysql> SAVEPOINT svp1;
mysql> INSERT INTO T2 VALUES(200);
mysql> ROLLBACK TO SAVEPOINT svp1;
mysql> RELEASE SAVEPOINT svp1;
mysql> COMMIT;

mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

可以看到，第二条insert语句被rollback了，但第一条是在savepoint之前，没有被rollback.