MySQL事务隔离级别详解：脏读、不可重复读与幻读

数据库事务隔离级别主要作用是实现事务工作期间，数据库操作读的隔离特性，所谓读的操作就是将数据页可以调取到内存；

然后可以读取数据页中相应数据行的能力，并且不同事务之间的数据页读操作相互隔离；

可以简单理解为：一个事务在对数据页中数据行做更新操作时，在没有更新提交前，另一个事务此时是不能读取数据页中数据行内容的；

对于数据库存储事务隔离级别包括4种，可以通过操作命令查看获取当前使用的隔离级别:

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

常用的事务隔离级别类型：

类型一：RU（READ-UNCOMMITTED 表示读未提交）

可以读取到事务未提交的数据，隔离性差，会出现脏读（当前内存读），不可重复读，幻读问题;

类型二：RC（READ-COMMITTED 表示读已提交）可用

可以读取到事务已提交的数据，隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题；

类型三：RR（REPEATABLE-READ 表示可重复读）默认

可以防止脏读（当前内存读），防止不可重复读问题，防止会出现的幻读问题，但是并发能力较差；

会使用next lock锁进制，来防止幻读问题，但是引入锁进制后，锁的代价会比较高，比较耗费CPU资源，占用系统性能；

类型四：SR（SERIALIZABLE 可串行化）

隔离性比较高，可以实现串行化读取数据，但是事务的并发度就没有了；

这是事务的最高级别，在每条读的数据上，加上锁，使之不可能相互冲突

事务隔离级别官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

常用的事务隔离级别名词：

在解释分析说明相应的隔离级别名词前，需要对数据库事务隔离级别进行调整，以及关闭自动提交功能：

-- 隔离级别为RU，设置完成后需要执行\q重新进入新的会话才能生效
mysql> set global transaction_isolation='READ-UNCOMMITTED';
-- 退出当前会话并重新登录
mysql> \q
[root@master ~]# mysql
-- 查看事务隔离级别,修改为RU
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

-- 隔离级别为RC设置完成后需要执行\q重新进入新的会话才能生效
mysql> set global transaction_isolation='READ-COMMITTED';
-- 退出当前会话并重新登录
mysql> \q
[root@master ~]# mysql
-- 查看事务隔离级别,修改为RC
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

-- 隔离级别为RR置完成后需要执行\q重新进入新的会话才能生效
mysql> set global transaction_isolation='REPEATABLE-READ';
-- 退出当前会话并重新登录
mysql> \q
[root@master ~]# mysql
-- 查看事务隔离级别,修改为RR
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

# 临时关闭自动提交功能：
mysql> set global autocommit=0;
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

创建隔离级别测试数据表：

-- 临时关闭自动提交功能：
mysql> set global autocommit=0;

-- 创建测试库和表
mysql> create database db21;
mysql> create table db21.t1 (
    id int not null primary key auto_increment,
    a int not null,
    b varchar(20) not null,
    c varchar(20) not null
) charset=utf8mb4 engine=innodb;

-- 确认两个SQL会话窗口，即不同的事务查看的数据是否一致的；
mysql> begin;
mysql> insert into db21.t1(a,b,c)
values
(5,'a','aa'),
(7,'c','ab'),
(10,'d','ae'),
(13,'g','ag'),
(14,'h','at'),
(16,'i','au'),
(20,'j','av'),
(22,'k','aw'),
(25,'l','ax'),
(27,'o','ay'),
(31,'p','az'),
(50,'x','aze'),
(60,'y','azb');
mysql> commit;

-- 验证结果，观察到数据已正常插入完成
mysql> select * from db21.t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 |  5 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 | 14 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)

-- 关闭自动提交commit
mysql> set global autocommit=0;
-- 验证
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

名词解读分析一：脏读

脏读主要表示在一个事务窗口中，没有数据修改提交操作前，另一个事务就可以看到内存中数据页的修改；

简单理解：在一个事务窗口中，可以读取到别人没有提交的数据信息；

利用隔离级别RU解读：

-- 设置事务隔离级别
mysql> set global transaction_isolation='READ-UNCOMMITTED';
-- 结果验证
mysql> \q
[root@master ~]# mysql
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

-- 重新开启两个SQL会话窗口,这里定义为数据库A会话和B会话
-- 数据库A会话窗口开启事务
mysql> begin;
-- 数据库A会话窗口上只是在内存层面进行数据页中数据修改
mysql> update db21.t1 set a=10 where id=1;
-- 数据库B会话窗口上查询数据，观察到在A会话窗口没提交的事务修改，被B会话窗口查询到了
mysql> begin;
mysql> select * from db21.t1 where id=1;
+----+---+---+----+
| id | a | b | c  |
+----+---+---+----+
|  1 | 5 | a | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- 数据库A会话窗口进行事务回滚操作
mysql> rollback;
-- 数据库B会话窗口再次查询数据，观察到在A会话窗口进行回滚后，在B窗口查询的数据又恢复了
mysql> select * from db21.t1 where id=1;
+----+---+---+----+
| id | a | b | c  |
+----+---+---+----+
|  1 | 5 | a | aa |
+----+---+---+----+
1 row in set (0.00 sec)

名词解读分析二：不可重复读

不可重复读表示在一个事务中，利用相同的语句多次查询，获取的数据信息是不同的；

利用隔离级别RU解读：

-- 设置事务隔离级别
mysql> set global transaction_isolation='READ-UNCOMMITTED';
-- 结果验证
mysql> \q
[root@master ~]# mysql
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

-- 重新开启两个SQL会话窗口,这里定义为数据库A会话和B会话
-- 数据库A会话窗口开启事务
mysql> begin;
-- 数据库A会话窗口上第一次在内存层面进行数据页中数据修改
mysql> update db21.t1 set a=20 where id=1;
mysql> commit;
-- 在数据库B会话事务窗口进行数据第一次查询看到数据信息：a=20
mysql> select * from db21.t1 where id=1;
+----+----+---+----+
| id | a  | b | c  |
+----+----+---+----+
|  1 | 20 | a | aa |
+----+----+---+----+
1 row in set (0.00 sec)

-- 数据库A会话窗口上第二次在内存层面进行数据页中数据修改
mysql> update db21.t1 set a=30 where id=1;
mysql> commit;
-- 在数据库B会话事务窗口进行数据第二次查询看到数据信息：a=30
mysql> select * from db21.t1 where id=1;
+----+----+---+----+
| id | a  | b | c  |
+----+----+---+----+
|  1 | 30 | a | aa |
+----+----+---+----+
1 row in set (0.00 sec)

利用隔离级别RC解读：

# 设置事务隔离级别
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| READ-COMMITTED              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;

-- 准备工作

-- 重新开启两个SQL会话窗口

# 数据库A会话窗口操作

mysql> begin;
mysql> select * from db21.t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务查询信息 = B窗口事务查询信息
mysql> update t1 set a=10 where id=1;
-- A窗口事务进行修改
mysql> commit;
-- A窗口事务进行提交

# 数据库B会话窗口操作
mysql> use oldboy;
mysql> begin;
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务查询信息 = B窗口事务查询信息
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题；
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 10 | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务提交之后，B窗口事务查询信息和之前不同了

利用隔离级别RR解读：

# 设置事务隔离级别
mysql> set global transaction_isolation='REPEATABLE-READ';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| REPEATABLE-READ              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新开启两个SQL会话窗口

# 数据库A会话窗口操作
mysql> use oldboy;
mysql> begin;
mysql> select * from t1;
-- 确认初始数据信息
mysql> update t1 set a=10 where id=1;
-- A窗口事务进行修改
mysql> commit;
-- A窗口事务进行提交

# 数据库B会话窗口操作
mysql> use oldboy;
mysql> begin;
mysql> select * from t1;
-- 确认初始数据信息
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题；
mysql> select * from t1 where id=1;
+----+---+---+----+
| id   | a  | b  | c  |
+----+---+---+----+
|  1   | 5  | a  | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务提交之后，B窗口事务查询信息和之前是相同的；
-- 在RR级别状态下，同一窗口的事务生命周期下，每次读取相同数据信息是一样，避免了不可重复读问题
mysql> commit;
mysql> select * from t1 where id=1;
-- 在RR级别状态下，同一窗口的事务生命周期结束后，看到的数据信息就是修改的了

名词解读分析三：幻读

利用隔离级别RC解读：

-- 关闭自动commit功能
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 设置事务隔离级别
mysql> set global transaction_isolation='READ-COMMITTED';
-- 结果验证
mysql> \q
[root@master ~]# mysql
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

-- 数据库中A窗口获取表中数据
mysql> select * from db21.t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 |  5 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 | 14 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)

-- 在数据库A窗口上添加索引
mysql> alter table db21.t1 add index idx(a);

-- 在数据库A窗口和B窗口上开启事务操作
mysql> begin;

-- 在A窗口修改表数据,将a<20的信息调整为20
mysql> update db21.t1 set a=20 where a<20;

-- 在A窗口查看数据
mysql> select * from db21.t1;
mysql> select * from db21.t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 | 20 | a | aa  |
|  2 | 20 | c | ab  |
|  3 | 20 | d | ae  |
|  4 | 20 | g | ag  |
|  5 | 20 | h | at  |
|  6 | 20 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)

-- 在B窗口中，插入一条新的数据信息 a=10
mysql> begin;
mysql> insert into db21.t1 values(14,18,'a','c');

-- 在A窗口中，查看数据信息，希望看到的a是没有小于20的，但是结果看到了a存在等于10的（即出现了幻读）
mysql> select * from db21.t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 | 10 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 | 14 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
| 14 | 18 | a | c   |
+----+----+---+-----+
13 rows in set (0.00 sec)

利用隔离级别RR解读：

# 设置事务隔离级别
mysql> set global transaction_isolation='REPEATABLE-READ';
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| REPEATABLE-READ              |
+---------------------------------+
mysql> set global autocommit=0;
mysql> select @@autocommit;
-- 重新开启两个SQL会话窗口

# 数据库A会话窗口操作
mysql> use oldboy;
mysql> select * from t1;
-- 查看获取A窗口表中数据
mysql> alter table t1 add index idx(a);
-- 在A窗口中，添加t1表的a列为索引信息
mysql> begin;
mysql> update t1 set a=20 where a>20;
-- 在A窗口中，将a>20的信息均调整为20

# 数据库B会话窗口操作
mysql> use oldboy;
mysql> select * from t1;
-- 查看获取B窗口表中数据
mysql> begin;
mysql> insert into t1(a,b,c) values(30,'sss','bbb');
-- 在B窗口中，插入一条新的数据信息 a=30，但是语句执行时会被阻塞，没有反应；
mysql> show processlist;
-- 在C窗口中，查看数据库连接会话信息，insert语句在执行，等待语句超时（默认超时时间是50s）
-- 因为此时在RR机制下，创建了行级锁(阻塞修改)+间隙锁(阻塞区域间信息插入)=next lock
-- 区域间隙锁 < 左闭右开(可用临界值)  ;  区域间隙锁 > 左开右闭（不可用临界值）

事务隔离机制知识点补充：

提到事务肯定不陌生，和数据库打交道的时候，总是会用到事务。

最经典的例子就是转账，你要给朋友小王转 100 块钱，而此时你的银行卡只有 100 块钱。

转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作必须保证是一体的；

不然等程序查完之后，还没做减法之前，你这100块钱，完全可以借着这个时间差再查一次，然后再给另外一个朋友转账，

如果银行这么整，不就乱了么？这时就要用到“事务”这个概念了。

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。

比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

本知识点将会以 InnoDB 为例，剖析 MySQL 在事务支持方面的特定实现，并基于原理给出相应的实践建议，

希望这些案例能加深你对 MySQL 事务原理的理解。

隔离性与隔离级别

提到事务，肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），

我们就来说说其中 I，也就是“隔离性”。

当数据库上有多个事务同时执行的时候，就可能出现以下问题：

脏读（dirty read）
不可重复读（non-repeatable read）
幻读（phantom read）

为了解决这些问题，就有了“隔离级别”的概念。在谈隔离级别之前，首先要知道，隔离得越严实，效率就会越低。

因此很多时候，都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：

隔离级别	英文描述	解释说明
读未提交	RU-read uncommitted	一个事务还没提交时，它做的变更就能被别的事务看到。
读提交	RC-read committed	一个事务提交之后，它做的变更才会被其他事务看到。
可重复读	RR-repeatable read	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化	serializable	顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

其中“读提交”和“可重复读”比较难理解，所以我用一个例子说明这几种隔离级别。

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

mysql> create table T(c int) engine=InnoDB;
mysql> insert into T(c) values(1);

两个事务操作行为：

事务行为顺序	事务A	事务B
01	启动事务；查询得到值1	启动事务
02		查询得到值1
03		将1改为2
04	查询得到值v1
05		提交事务B
06	查询得到值v2
07	提交事务A
08	查询得到值v3

在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

若隔离级别是“读未提交”：

则 V1 的值就是 2，事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

若隔离级别是“读提交”：

则 V1 是 1，V2 的值是 2，事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

若隔离级别是“可重复读”：

则 V1、V2 是 1，V3 是 2，之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

若隔离级别是“串行化”：

则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。

所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

评论区