Mysql事务控制语句

介绍

Mysql的Innodb存储引擎默认事务都是自动提交（隐式提交）；
查看当前的事务是否自动提交：

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

事务操作

1. 开始事务

start transaction | begin

2. 禁用当前会话自动提交

set autocommit=0

3. 提交事务
   commit提交事务，将数据库做的所有修改成为永久性。

commit

4. 回滚
   rollback回滚事务，结束当前事务，然后撤销之前未提交操作，回到初状态。

rollback

5. 设置保存点

savepoint [unique flag]

6. 释放保存点

release savepoint [unique flag]

7. 回滚保存点

rollback to savepoint [unique flag]

8. 设置事务隔离级别

set transaction

事务操作例子

1. 正常保存点回滚

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

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> savepoint t1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> rollback to savepoint t1;
Query OK, 0 rows affected (0.00 sec)
mysql> release savepoint t1;
Query OK, 0 rows affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

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

2. 保存点不存在将报错

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> release savepoint t1;
ERROR 1305 (42000): SAVEPOINT t1 does not exist

3. rollback to savepoint [unique flag]不是真正的回滚结束事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> savepoint t1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> savepoint t2;
Query OK, 0 rows affected (0.00 sec)
mysql> rollback to savepoint t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> release savepoint t2;
ERROR 1305 (42000): SAVEPOINT t2 does not exist
mysql> release savepoint t1;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

说明：
虽然回滚到保存点t1，但是前面插入的值4依然存在。可以发现回滚只是回滚到保存点的状态，而不是将当前整个事务回滚。而回滚到保存点t1，在去释放保存点t2，保存点t2将不会存在了。
结束当前事务还是应该采用rollback或者commit。而我们将事务rollback或者commit，那么保存点将全部被删除掉。

查看事务显示提交次数

mysql> show global status like '%com_commit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 47    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like '%com_commit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 48    |
+---------------+-------+
1 row in set (0.00 sec)

---

参考：
Mysql5.7官方手册
MySQL技术内幕：InnoDB存储引擎