MySQL 事务详解：ACID 属性、引擎支持与提交方式

4. 事务提交方式

事务的提交方式常见的有两种：自动提交、手动提交。

查看事务提交方式

show variables like 'autocommit';

用 SET 来改变 MySQL 的自动提交模式

set autocommit = 0; -- 禁止自动提交

set autocommit = 1; -- 开启自动提交

事务常见操作方式

为了便于演示，我们将 MySQL 的默认隔离级别设置成读未提交。

set global transaction isolation level read uncommitted;

创建测试表

create table if not exists account( 
    id int primary key, 
    name varchar(50) not null default ' ', 
    balance decimal(10,2) not null default 0.0 
) engine=InnoDB default charset=utf8;

正常演示 - 证明事务的开始与回滚

show variables like 'autocommit';   -- 查看事务是否自动提交。我们故意设置成自动提交，看看该选项是否影响 begin start transaction; -- 开始一个事务 begin 也可以，推荐 begin savepoint s1; -- 创建一个保存点 s1 insert into account values(1,'张三',1263.4); -- 插入一条记录 savepoint s2; -- 创建一个保存点 s2 insert into account values(2,'李四',6521.9); -- 再插入一条记录 select * from account; -- 两条记录都在了 +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 1263.40 | | 2 | 李四 | 6521.90 | +----+--------+---------+ 2 rows in set (0.00 sec) rollback to s2; -- 回滚到保存点 save2 select * from account; -- id 为 2 的一条记录没有了 +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 1263.40 | +----+--------+---------+ 1 row in set (0.00 sec) rollback; -- 直接 rollback，回滚在最开始 select * from account; -- 所有刚刚的记录没有了 Empty set (0.00 sec)

非正常演示 1 - 证明未 commit，客户端崩溃，MySQL 自动会回滚（隔离级别设置为读未提交）

-- 终端 A
select * from account; -- 当前表内无数据
show variables like 'autocommit'; -- 依旧自动提交
begin; -- 开启事务
insert into account values(1,'张三',1566.7); -- 插入记录

-- 终端 B
select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 1566.70 |
+----+--------+---------+
1 row in set (0.00 sec)

-- 终端 A
mysql> Aborted -- ctrl + \ 异常终止 MySQL

-- 终端 B
select * from account;
Empty set (0.00 sec)

非正常演示 2 - 证明 commit 了，客户端崩溃，MySQL 数据不会在受影响，已经持久化

show variables like 'autocommit'; -- 依旧自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

select * from account;   -- 当前表内无数据
Empty set (0.00 sec)

begin;                   -- 开启事务
insert into account values (1, '张三', 1234.4); -- 插入记录
commit;                  -- 提交事务
Aborted                  -- ctrl + \ 异常终止 MySQL

-- 终端 B
select * from account;   -- 数据存在了，所以 commit 的作用是将数据持久化到 MySQL 中

非正常演示 3 - 对比试验。证明 begin 操作会自动更改提交方式，不会受 MySQL 是否自动提交影响

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 1234.40 |
+----+--------+---------+
1 row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)

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

mysql> insert into account values(2,'李四',6543.2);
Query OK, 1 row affected (0.00 sec)

mysql> Aborted

非正常演示 4 - 证明单条 SQL 与事务的关系

实验一

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 1234.40 |
+----+--------+---------+
1 row in set (0.00 sec)

mysql> system clear

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 1234.40 |
+----+--------+---------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)

mysql> insert into account values(2,'李四',6532.4);
Query OK, 1 row affected (0.00 sec)

mysql> Aborted

实验二

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 1234.40 |
+----+--------+---------+
1 row in set (0.00 sec)

mysql> insert into account values(2,'李四',3526.4);
Query OK, 1 row affected (0.01 sec)

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 1234.40 |
| 2 | 李四 | 3526.40 |
+----+--------+---------+
2 rows in set (0.01 sec)

mysql> Aborted

结论

• 只要输入 begin 或者 start transaction，事务便必须要通过 commit 提交，才会持久化，与是否设置 set autocommit 无关。
• 事务可以手动回滚，同时，当操作异常，MySQL 会自动回滚。
• 对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select 有特殊情况，因为 MySQL 有 MVCC）。
• 从上面的例子，我们能看到事务本身的原子性（回滚），持久性（commit）。

事务操作注意事项

• 如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback（前提是事务还没有提交）。
• 如果一个事务被提交了（commit），则不可以回退（rollback）。
• 可以选择回退到哪个保存点。
• InnoDB 支持事务，MyISAM 不支持事务。
• 开始事务可以使 start transaction 或者 begin。