mysql事务隔离级别

引用：https://blog.csdn.net/xinyuan_java/article/details/128493205

1.隔离级别

1. SERIALIZABLE(序列化)

如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

2. REPEATABLE READ

在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。

3. READ COMMITTED

READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。

4. READ UNCOMMITTED

READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。

在 MySQL 数据库中，默认的事务隔离级别是 REPEATABLE READ。

2.实践

2.1查看事务隔离级别

MySQL8 之前使用如下命令查看 MySQL 隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

MySQL8 开始，通过如下命令查看 MySQL 默认隔离级别：

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

2.2 设置隔离级别

通过如下命令可以修改隔离级别（建议开发者在修改时修改当前 session 隔离级别即可，不用修改全局的隔离级别）：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

2.3 不可重复读

不可重复读是指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。具体操作步骤如下（操作之前先将两个账户的钱都恢复为1000）：

首先打开两个查询会话 A 和 B ，并且将 A 的数据库事务隔离级别设置为 READ UNCOMMITTED。具体 SQL 参考上文，这里不赘述。

接下来执行如下 SQL：

SQL1 执行结果如下：

SQL2 执行结果如下：

SQL3 执行结果如下：

可以看到，在 SessionA 中查询同一条记录，多次查询最终的结果可能不一样，这就是不可重复读。

和脏读的区别在于，脏读是看到了其他事务未提交的数据，而不可重复读是看到了其他事务已经提交的数据（由于当前 SQL 也是在事务中，因此有可能并不想看到其他事务已经提交的数据）。

2.4 幻读

幻读和不可重复读非常像，看名字就是产生幻觉了。

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`age` int(11) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

id 是主键，age 是唯一非空索引。
表中数据如下：

现在我们有两个会话 Session A 和 Session B，Session A 隔离级别是 READ UNCOMMITTED，Session B 是默认的隔离级别，执行的 SQL 如下：

注意，在 SQL1 中用了一个当前读，按理说它会锁住 age 大于 80 的记录，其实也确实锁住了 89 和 99 这样的值，但是对于一开始就不存在的 100 就没能锁住了，这就导致在 SQL3 执行的时候，看到了 SQL2 插入的语句。

幻读，幻读专指看到了新插入的行。。

READ COMMITTED 主要解决了脏读的问题，对于不可重复读和幻象读则未解决。

REPEATABLE READ 进一步解决了不可重复读的问题，对于幻读问题，REPEATABLE READ 也有一个自己的方案。

SERIALIZABLE 提供了事务之间最大限度的隔离，在这种隔离级别中，事务一个接一个顺序的执行，不会发生脏读、不可重复读以及幻象读问题，最安全。

3.幻读怎么解决

脏读、不可重复读这两个问题通过修改事务的隔离级别就可以解决，那么幻读该如何解决呢？MySQL 中提出了 Next-Key Lock 来解决幻读问题，当然这个方案也只在 REPEATABLE READ 这个隔离级别下生效。要把这个问题理解透，你得搞明白三把锁：Record Lock、Gap Lock 以及 Next-Key Lock。

3.1 Record Lock

Record Lock 也就是我们所说的记录锁，记录锁是对索引记录的锁，注意，它是针对索引记录，即它只锁定记录这一行数据。

例如如下一条 SQL：

select * from user where id=1 for update;

注意，id 是索引，id 如果不是索引，上面这条 SQL 所加的排他锁就不是一个 Record Lock。

我们来看如下一个例子：
首先我们将系统变量 innodb_status_output_locks 设置为 ON，如下：

接下来我们执行如下 SQL，锁定一行数据，此时会自动为表加上 IX 锁：

接下来我们在一个新的会话中执行如下指令来查看 InnoDB 存储引擎的情况：

show engine innodb status\G

输出的信息很多，我们重点关注 TRANSACTIONS，如下：

可以看到：

• TABLE LOCK table test08.user trx id 3564804 lock mode IX：这句就是说事务 id 为 3564804 的事务，为 user 表添加了意向排他锁（IX）。
• RECORD LOCKS space id 851 page no 3 n bits 80 index PRIMARY of table test08.user trx id 3564804 lock_mode X locks rec but not gap：这个就是一个锁结构的记录，这里的索引是 PRIMARY，加的锁也是正儿八经的记录锁（not gap）。

看到了 LOCKS REC BUT NOT GAP，就说明这是一个记录锁。

那么这个 Record Lock 和我们之前所讲的 S 锁以及 X 锁有什么区别呢？S 锁是共享锁，X 锁是排他锁，当我们加 S 锁或者 X 锁的时候，如果用到了索引，锁加在了某一条具体的记录上，那么这个锁也是一个记录锁（其实，记录锁，S 锁，X 锁，概念有一些重复的地方，但是描述的重点不一样）。

或者也可以理解为记录锁又细分为 S 锁和 X 锁，它们之间的兼容性如下图：

3.2 Gap Lock

Gap Lock 也叫做间隙锁，它的存在可以解决幻读问题，另外需要注意，Gap Lock 也只在 REPEATABLE READ 隔离级别下有效。先来看看什么是幻读，我们来看如下一个表格：

有两个会话，A 和 B，先在会话 A 中开启事务，然后查询 age 为 99 的用户总数，注意使用当前读，因为在默认的隔离级别下，默认的快照读并不能读到其他事务提交的数据，至于快照读和当前读的区别，大家参考：S 锁与 X 锁，当前读与快照读！。当会话 A 中第一次查询过后，会话 B 中向数据库添加了一行记录，等到会话 A 中第二次查询的时候，就查到了和第一次查询不一样的结果，这就是幻读（注意幻读专指数据插入引起的不一致）。

在 MySQL 默认的隔离级别 REPEATABLE READ 下，上图所描述的情况无法复现。无法复现的原因在于，在 MySQL 的 REPEATABLE READ 隔离级别中，它已经帮我们解决了幻读问题，解决的方案就是 Gap Lock。

大家想想，之所以出现幻读的问题，是因为记录之间存在缝隙，用户可以往这些缝隙中插入数据，这就导致了幻读问题，如下图：

现在 Gap Lock 间隙锁，就是要把这些记录之间的间隙也给锁住，间隙锁住了，就不用担心幻读问题了，这也是 Gap Lock 存在的意义。

给一条记录加 Gap Lock，是锁住了这条记录前面的空隙，例如给 id 为 1 的记录加 Gap Lock，锁住的范围是 (-∞,1)，给 id 为 3 的记录加 Gap Lock，锁住的范围是 (1,3)，那么 id 为 10 后面的空隙怎么锁定呢？MySQL 提供了一个 Supremum 表示当前页面中的最大记录，所以最后针对 Supremum 锁住的范围就是 (10,+∞)，这样，所有的间隙都被覆盖到了，由于锁定的是间隙，所以都是开区间。

那么我们怎么样能看到 Gap Lock 呢？我给大家举一个简单的例子，假设我有如下一张表：

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

一个简单的表，id 是主键，age 是普通索引，表中有如下几条记录：

接下来我们执行如下 SQL，锁定一行数据，此时也会产生间隙锁：

接下来我们在一个新的会话中执行如下指令来查看 InnoDB 存储引擎的情况：

show engine innodb status\G

红色框选中的，就是一个间隙锁的加锁记录，可以看到，在某一个记录之前加了间隙锁。

这就是间隙锁。非常重要的一点需要大家牢记：Gap Lock 只在 REPEATABLE READ 隔离级别下有效。

3.3 Next-Key Lock

以下内容都是基于 MySQL 默认的隔离级别 REPEATABLE READ。

如果我们既想锁定一行，又想锁定行之间的记录，那么就是 Next-Key Lock 了，换言之，Next-Key Lock 是 Record Lock 和 Gap Lock 的结合体。

正常来说，我们加行锁的基本单位就是 Next-Key Lock，即既有记录锁又有间隙锁，但是有时候 Next-Key Lock 会退化，我们通过几个简单的例子来分析一下。

首先我们来看看 Next-Key Lock 的加锁规则：

1. 锁的范围是左开右闭。
2. 如果是唯一非空索引的等值查询，Next-Key Lock 会退化成 Record Lock。
3. 普通索引上的等值查询，向后遍历时，最后一个不满足等值条件的时候，Next-Key Lock 会退化成 Gap Lock。

我们通过几个简单的例子来分析下。

2.3.1 唯一非空索引

假设我有一个学生表，学生表中有学生的姓名和成绩，如下：

CREATE TABLE `student` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`score` double NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

id 是主键，score 是成绩，其中 score 是唯一非空索引。

现在表中有如下数据：

假设我们执行如下 SQL：

在这个例子中，由于 score 是唯一非空索引，所以 Next-Key Lock 会退化成 Record Lock，换句话说，这行 SQL 只给 score 为 90 的记录加锁，不存在 Gap Lock，即我们新开一个会话，插入一条 score 为 88 的记录也是 OK 的。

不过这里有一个特例，如果锁定的是一个不存在的记录，那么也会产生间隙锁，例如下面这个：

由于并不存在 score 为 91 的记录，所以这里会产生一个范围为 (90,95) 的间隙锁，我们执行如下 SQL 可以验证：

可以看到，90.1、94.9 都会被阻塞（我按了 Ctrl C，所以大家看到查询终止）。

90、95 则不符合唯一非空索引的条件。

95.1 则可以插入成功。

没问题。

2.3.2 非空索引

现在我们重新开始，将 score 索引改为普通索引，如下：

CREATE TABLE `student` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`score` double NOT NULL,PRIMARY KEY (`id`),KEY `score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

数据还是跟前面一样，此时我们来执行如下 SQL：

我们来分析下。

此时要锁定的是 id 为 90 的记录，那么首先加间隙锁，上一个 score 为 89，所以这次加的间隙锁范围是 (89,90)，同时要锁定 id 为 90 的记录，所以进一步优化为 (89,90]。

同时，这里还有一条规则，就是满足条件的上一条记录，也需要被锁住，所以最终的锁范围就是 [89,90]。

由于 score 不是唯一性索引，所以还需要继续向后查找，找到的下一条记录是 95，由于此时 Next-Key Lock 会退化成 Gap Lock，所以锁定的范围是 (90,95)。综上，最终锁定的范围是 [89,95)。

接下来我们可以新开一个会话，我们分别尝试添加如下数据看看是否能够添加成功：

可以看到，score 为 88 是可以的，但是为 89.1 就不行。

score 为 95 也是可以的，但是为 94.9 就不行。

再试一下 89 是否可以：

说明我们上面分析的加锁范围是正确的。

再来看如下一条 SQL：

跟前面的案例相比，这次多了 limit 1，limit 1 表示只要一条记录，所以这次查找到 90 之后就不会再往后查找了，那么最终的锁就是间隙锁+一个记录锁，最终的范围就是 [89,90]。

此时新开一个会话，分别插入 score 为 88.9、89、90、91 的 记录，验证我们上面所分析的加锁范围：

88.9 和 89 的插入结果跟我们预想的一致。

可以看到，这里 90 也能插入，能插入的原因是因为缺乏 90 往后的间隙锁。


参考：https://blog.csdn.net/xinyuan_java/article/details/128493205