1 概述
本实验测试MySQL事务隔离级别的可见性，即在各种事务隔离级别下，一个事务的修改操作（UPDATE, INSERT 和 DELETE 3种操作）对另一个事务是否可见。用于测试的2个事务可能处于相同事务隔离级别，也可能处于不同事务隔离级别。
2 实验环境
MySQL版本: 8.0.25 MySQL Community Server
MySQL存储引擎: InnoDB
操作系统: Windows 10 家庭中文版，版本号1803
3 实验方法
- 打开2个会话：打开2个CMD窗口，在2个窗口中分别用mysql程序连接MySQL服务器，开启2个会话。
- 启用 autocommit：在2个会话中都执行”set session autocommit = 1” 语句，启用autocommit。
- 设置事务隔离级别：用 “set session transaction_isolation = value” 语句设置各会话中事务的事务隔离级别，其中 value为下列之一：’READ-UNCOMMITTED’, ‘READ-COMMITTED’, ‘REPEATABLE-READ’, ‘SERIALIZABLE’。
- 测试修改操作的可见性：在会话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 |
不可能 |
不可能 |
不可能 |
