1 概述

本实验测试MySQL事务隔离级别的可见性，即在各种事务隔离级别下，一个事务的修改操作（UPDATE, INSERT 和 DELETE 3种操作）对另一个事务是否可见。用于测试的2个事务可能处于相同事务隔离级别，也可能处于不同事务隔离级别。

2 实验环境

MySQL版本: 8.0.25 MySQL Community Server

MySQL存储引擎: InnoDB

操作系统: Windows 10 家庭中文版，版本号1803

3 实验方法

  1. 打开2个会话：打开2个CMD窗口，在2个窗口中分别用mysql程序连接MySQL服务器，开启2个会话。
  2. 启用 autocommit：在2个会话中都执行”set session autocommit = 1” 语句，启用autocommit。
  3. 设置事务隔离级别：用 “set session transaction_isolation = value” 语句设置各会话中事务的事务隔离级别，其中 value为下列之一：’READ-UNCOMMITTED’, ‘READ-COMMITTED’, ‘REPEATABLE-READ’, ‘SERIALIZABLE’。
  4. 测试修改操作的可见性：在会话1中执行修改操作，在会话2中执行查询操作，看是否能看到会话1中的修改操作结果。

测试表如下:

CREATE TABLE tmp (

id int unsigned NOT NULL AUTO_INCREMENT,

status int NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

4 测试结果

本节中”事务隔离级别1 对 事务隔离级别2”表示事务隔离级别1下的事务的修改操作对事务隔离级别2下的事务的可见性。

S1, S2, S3… 是步骤标识。T1, T2, T3…是事务标识。

开始每组实验前，都用以下语句重置表的数据：

truncate table tmp;

insert into tmp (id, status) values (1, 1), (2, 1);

4.1 READ UNCOMMITTED 对 READ UNCOMMITTED

步骤 会话1(READ UNCOMMITTED) 会话2(READ UNCOMMITTED) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1
5 select * from tmp where id between 1 and 3 (1,2), (2,1) S4中T1的UPDATE操作对T2可见
6 insert into tmp (id, status) values (3, 1)
7 select * from tmp where id between 1 and 3 (1,2), (2,1), (3,1) S6中T1的INSERT操作对T2可见
8 delete from tmp where id = 2
9 select * from tmp where id between 1 and 3 (1,2), (3,1) S8中T1的DELETE操作对T2可见
10 rollback
11 select * from tmp where id between 1 and 3 (1,1), (2,1)

4.2 READ COMMITTED 对 READ COMMITTED

步骤 会话1(READ COMMITTED) 会话2(READ COMMITTED) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1
5 select * from tmp where id between 1 and 3 (1,1), (2,1) S4中T1的UPDATE操作对T2不可见
6 insert into tmp (id, status) values (3, 1)
7 select * from tmp where id between 1 and 3 (1,1), (2,1) S6中T1的INSERT操作对T2不可见
8 delete from tmp where id = 2
9 select * from tmp where id between 1 and 3 (1,1), (2,1) S8中T1的DELETE操作对T2不可见
10 commit
11 select * from tmp where id between 1 and 3 (1,2), (3,1) S4,S6,S8中T1的UPDATE,INSERT,DELETE操作对T2可见

4.3 REPEATABLE READ 对 REPEATABLE READ

步骤 会话1(REPEATABLE READ) 会话2(REPEATABLE READ) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 34 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1
5 select * from tmp where id between 1 and 3 (1,1), (2,1) S4中T1的UPDATE操作对T2不可见
6 insert into tmp (id, status) values (3, 1)
7 select * from tmp where id between 1 and 3 (1,1), (2,1) S6中T1的INSERT操作对T2不可见
8 delete from tmp where id = 2
9 select * from tmp where id between 1 and 3 (1,1), (2,1) S8中T1的DELETE操作对T2不可见
10 commit
11 select * from tmp where id between 1 and 3 (1,1), (2,1) S4,S6,S8中T1的UPDATE,INSERT,DELETE操作对T2不可见

4.4 SERIALIZABLE 对 SERIALIZABLE

步骤 会话1(SERIALIZABLE) 会话务2(SERIALIZABLE) 可见性
操作 结果 操作 结果
1 truncate table tmp;
insert into tmp (id, status) values (1,1),(2,1),(4,1),(5,1);
2 begin T1开始
select * from tmp where id between 1 and 3 (1,1), (2,1)
3 begin T2开始
4 update tmp set status = status + 1 where id = 1 锁等待超时，T2回滚
5 begin T3开始
6 insert into tmp (id, status) values (3, 1) 锁等待超时，T3回滚
7 begin T4开始
8 delete from tmp where id = 2 锁等待超时，T4回滚
9 begin T5开始
10 update tmp set status = status + 1 where id = 4
11 insert into tmp (id, status) values (6, 1)
12 delete from tmp where id = 5
13 commit
14 select * from tmp where id between 1 and 6 (1,1),(2,1),(4,2),(6,1)
15 begin T6开始
16 insert into tmp (id, status) values (7, 1) 锁等待超时，T6回滚

4.5 READ UNCOMMITTED 对 READ COMMITTED

步骤 会话1(READ UNCOMMITTED) 会话2(READ COMMITTED) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1
5 select * from tmp where id between 1 and 3 (1,1), (2,1) S4中T1的UPDATE操作对T2不可见
6 insert into tmp (id, status) values (3, 1)
7 select * from tmp where id between 1 and 3 (1,1), (2,1) S6中T1的INSERT操作对T2不可见
8 delete from tmp where id = 2
9 select * from tmp where id between 1 and 3 (1,1), (2,1) S8中T1的DELETE操作对T2不可见
10 commit
11 select * from tmp where id between 1 and 3 (1,2), (3,1) S4,S6,S8中T1的UPDATE,INSERT,DELETE操作对T2可见

4.6 READ COMMITTED 对 READ UNCOMMITTED

步骤 会话1(READ COMMITTED) 会话2(READ UNCOMMITTED) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1
5 select * from tmp where id between 1 and 3 (1,2), (2,1) S4中T1的UPDATE操作对T2可见
6 insert into tmp (id, status) values (3, 1)
7 select * from tmp where id between 1 and 3 (1,2), (2,1), (3,1) S6中T1的INSERT操作对T2可见
8 delete from tmp where id = 2
9 select * from tmp where id between 1 and 3 (1,2), (3,1) S8中T1的DELETE操作对T2可见
10 rollback
11 select * from tmp where id between 1 and 3 (1,1), (2,1)

4.7 READ UNCOMMITTED 对 SERIALIZABLE

步骤 会话1(READ UNCOMMITTED) 会话2(SERIALIZABLE) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1 锁等待超时，T1回滚
5 begin T3开始
6 insert into tmp (id, status) values (3, 1) 锁等待超时，T3回滚
7 begin T4开始
8 delete from tmp where id = 2 锁等待超时，T4回滚

4.8 SERIALIZABLE 对 READ UNCOMMITTED

步骤 会话1(SERIALIZABLE) 会话2(READ UNCOMMITTED) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1 锁等待超时，T1回滚
5 begin T3开始
6 insert into tmp (id, status) values (3, 1) 锁等待超时，T3回滚
7 begin T4开始
8 delete from tmp where id = 2 锁等待超时，T4回滚

4.9 READ COMMITTED 对 REPEATABLE READ

4.10 REPEATABLE READ 对 READ COMMITTED

4.11 REPEATABLE READ 对 SERIALIZABLE

步骤 会话1(REPEATABLE READ) 会话2(SERIALIZABLE) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1 锁等待超时，T1回滚
5 begin T3开始
6 insert into tmp (id, status) values (3, 1) 锁等待超时，T3回滚
7 begin T4开始
8 delete from tmp where id = 2 锁等待超时，T4回滚

4.12 SERIALIZABLE 对 REPEATABLE READ

步骤 会话1(SERIALIZABLE) 会话2(REPEATABLE READ) 可见性
操作 结果 操作 结果
1 begin T1开始
2 begin T2开始
3 select * from tmp where id between 1 and 3 (1,1), (2,1)
4 update tmp set status = status + 1 where id = 1 锁等待超时，T1回滚
5 begin T3开始
6 insert into tmp (id, status) values (3, 1) 锁等待超时，T3回滚
7 begin T4开始
8 delete from tmp where id = 2 锁等待超时，T4回滚

5 结论

一个事务的事务隔离级别定义了其他并发事务（无论其事务隔离级别是什么）的修改对该事务的可见性，而没有定义该事务的修改对其他并发事务的可见性。

对于MySQL 8.0 和 InnoDB 存储引擎，假设有T1-T100 共100个并发事务，其中T1-T4的事务隔离级别分别是 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ 和 SERIALIZABLE，T5-T100的事务隔离级别任意，则T1-T4 4个事务的行为如下表所示：

事务 脏读 不可重复读 幻读
T1 可能 可能 可能
T2 不可能 可能 可能
T3 不可能 不可能 不可能
T4 不可能 不可能 不可能