mysql锁机制及原理

1.隔离级别
2.实践
- 2.1查看事务隔离级别
- 2.2 设置隔离级别
- 2.3 不可重复读
- 2.4 幻读
3.幻读怎么解决
- 3.1 Record Lock
- 3.2 Gap Lock
- 3.3 Next-Key Lock

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

1.隔离级别

SERIALIZABLE(序列化)

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

REPEATABLE READ

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

READ COMMITTED

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

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 的加锁规则：

锁的范围是左开右闭。
如果是唯一非空索引的等值查询，Next-Key Lock 会退化成 Record Lock。
普通索引上的等值查询，向后遍历时，最后一个不满足等值条件的时候，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;