MySQL 事务的核心概念与 ACID 属性详解

*************************** 1. row ***************************
       Engine: InnoDB
      Support: DEFAULT
      Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
Savepoints: YES
*************************** 5. row ***************************
       Engine: MyISAM
      Support: YES
      Comment: MyISAM storage engine
Transactions: NO
          XA: NO
Savepoints: NO

六、事务的提交方式

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

查看事务提交方式：

show variables like 'autocommit'; 

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

SET AUTOCOMMIT=0;  # 禁止自动提交
SET AUTOCOMMIT=1;  # 开启自动提交

七、事务常见操作方式

简单银行用户表

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;

7.1 正常演示 - 证明事务的开始与（定向）回滚

mysql> show variables like 'autocommit';  -- 查看事务是否自动提交。我们故意设置成自动提交，看看该选项是否影响 begin
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> start transaction;                 -- 开始一个事务 begin 也可以，推荐 begin
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint save1;                   -- 创建一个保存点 save1（根据需求设置保存点）
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, '张三', 100);  -- 插入一条记录
Query OK, 1 row affected (0.05 sec)

mysql> savepoint save2;                   -- 创建一个保存点 save2
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account values (2, '李四', 10000); -- 在插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;             -- 两条记录都在了
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> rollback to save2;                 -- 回滚到保存点 save2（定向回滚）
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;             -- 一条记录没有了
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> rollback;                          -- 直接 rollback，回滚在最开始，哪怕你一个回滚点都没设置也可以
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;             -- 所有刚刚的记录没有了
Empty set (0.00 sec)

commit;                                   -- 就是把该事务给提交了，无法回滚

但是一般我们很少手动 rollback，事务大多数都是为了非正常情况。

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

-- 终端 A
mysql> select * from account;             -- 当前表内无数据
Empty set (0.00 sec)

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

mysql> begin;                             -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, '张三', 100);  -- 插入记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;             -- 数据已经存在，但没有 commit，此时同时查看终端 B
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

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

-- 终端 B
mysql> select * from account;             -- 终端 A 崩溃前
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> select * from account;             -- 数据自动回滚
Empty set (0.00 sec)

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

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

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

mysql> begin;                             -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, '张三', 100);  -- 插入记录
Query OK, 1 row affected (0.00 sec)

mysql> commit;                            -- 提交事务
Query OK, 0 rows affected (0.04 sec)

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

-- 终端 B
mysql> select * from account;             -- 数据存在了，所以 commit 的作用是将数据持久化到 MySQL 中
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

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

手动 begin 就必须手动 commit，跟是否是自动提交毫无关系

-- 终端 A
mysql> select *from account;               -- 查看历史数据
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> show variables like 'autocommit';  -- 查看事务提交方式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
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> begin;                             -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (2, '李四', 10000); -- 插入记录
Query OK, 1 row affected (0.00 sec)

mysql> select *from account;              -- 查看插入记录，同时查看终端 B
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> Aborted                             -- 再次异常终止

-- 终端 B
mysql> select * from account;             -- 终端 A 崩溃前
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from account;             -- 终端 A 崩溃后，自动回滚
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

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

-- 实验一
-- 终端 A
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

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

mysql> set autocommit=0;                  -- 关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (2, '李四', 10000); -- 插入记录
Query OK, 1 row affected (0.00 sec)

mysql> select *from account;              -- 查看结果，已经插入。此时可以在查看终端 B
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> ^D                                -- ctrl + \ or ctrl + d，终止终端
Bye

-- 终端 B
mysql> select * from account;             -- 终端 A 崩溃前
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from account;             -- 终端 A 崩溃后
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   |  100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 实验二
-- 终端 A
mysql> show variables like 'autocommit';  -- 开启默认提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

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

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

mysql> select *from account;              -- 数据已经插入
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> Aborted                             -- 异常终止

-- 终端 B
mysql> select * from account;             -- 终端 A 崩溃前
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from account;             -- 终端 A 崩溃后，并不影响，已经持久化。autocommit 起作用
+----+--------+----------+
| id | name   | blance |
+----+--------+----------+
|  1 | 张三   |  100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

7.6 结论

1. 只要输入 begin 或者 start transaction，事务便必须要通过 commit 提交，才会持久化，与是否设置 set autocommit 无关。
2. 事务可以手动回滚，同时，当操作异常，MySQL 会自动回滚。
3. 对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select 有特殊情况，因为 MySQL 有 MVCC）

从上面的例子，我们能看到事务本身的原子性 (回滚)，持久性 (commit)。

7.7 事务操作注意事项

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