MySQL 事务默认隔离级别？能否解决幻读？

向右看齐 2023-01-23 06:57 28阅读 0赞

### 事务隔离级别 ###

MySQL 中事务隔离级别有 read uncommited、read commited、repeatable read、serializable 四种，其中默认为 repeatable read（可重复读）。

> 为什么默认隔离级别是 RR，其实是个历史问题，参考 [**为什么mysql选可重复读作为默认的隔离级别？**][mysql]
> 
> 简单来说就是 MySQL 主从复制是基于 binlog（二进制文件），binlog 模式有 statement、row、mixed 三种，分别代表文件记录修改的 SQL 语句、记录行数据实际的变更、前 2 种的混合。MySQL 5.0 版本之前，binlog 只有 statement 这种模式，如果隔离级别设置为 read commited，主机和从机就会出现数据不一致的情况。解决这个问题的办法就是隔离级别设置为 repeatable read，高版本可设置 binlog 模式为 row。

### 如何解决幻读 ###

一个事务先后 2 次使用相同的条件读取数据，另一个事务如果在俩次读取间隔中插入条件相同的数据，导致第一个事务后一次读到了更多的行，这种现象称为幻读。

> 脏读：一个事务读到了另一个事务回滚前的数据，这些数据是无效的，称为脏数据。
> 
> 不可重复读：一个事务先后读取数据，在中间过程中另一个事务对数据做了修改，导致先后读取到数据不一致。
> 
> 不可重复读与幻读的区别就是，前者是数据不一致，因为对数据做了修改，后者是新插入了数据。

所以 MySQL 中 RR 隔离级别能否防止幻读，关键就是能否阻止数据的插入。

#### 行锁 3 种算法 ####

MySQL 的 InnoDB 引擎是支持行级别锁的，有三种算法：

*  Record Lock：记录锁，锁定单行记录
 *  Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
 *  Next-Key Lock：锁定一个范围，并锁定记录本身，简单理解为前 2 种的组合

#### Next-Key Lock ####

RR 级别下 InnoDB 引擎使用 Next-Key Lock 机制，这里要分 2 种情况：

*  唯一索引
    
    Next-Key Lock 降级为 Record Lock 锁定单行记录。
 *  辅助索引
    
    锁定记录本身和相邻的范围。举个例子，如 test 表有 a、b 俩个字段，a 是主键，并且 b 字段有辅助索引：
    
        | a | b |
        |---|---|
        | 1 | 1 |
        | 3 | 1 |
        | 5 | 3 |
        | 7 | 6 |
        |10 | 8 |
    
    执行以下 SQL
    
        select * from test where b = 3 for update;
    
    此时的加锁范围是（3,1）~ （7,6），如果 insert 的数据在此范围内则，会阻塞，例如执行以下 6 条 SQL 的结果是：
    
        insert into test (a,b) values (2,1); // 1 ok
        insert into test (a,b) values (4,1); // 2 block
        insert into test (a,b) values (4,3); // 3 block
        insert into test (a,b) values (6,3); // 4 block
        insert into test (a,b) values (6,6); // 5 block
        insert into test (a,b) values (8,6); // 6 ok
    
    其中第 3、4 条 insert 语句不能被插入，所以同一事物中再次执行 where b = 3 的查询与之前结果相同，不会出现幻读。

### 彪神指点 ###

#### Next-Key Lock 范围是左开右闭 ####

例如一个索引有 10，11，20 这四个值，那么该索引可能被锁定的区间有可能是：(-∞,10\]、(10,11\]、(11,20\]、(20,+∞) ，再看 test 表中数据，for update 之后除了锁定 b 字段索引 (1,3\] 还需要加 Gap Lock 锁定 (3,6) 这段范围。而因为 Gap Lock 解决了幻读。

彪神说因为挨个去比较索引 key 的值是否满足条件，至少要与一个不相等的 key ，那么 Gap Lock 边界就是这个 key 的位置，这个解释让我豁然开朗。

#### 插入意向锁 ####

**彪神：因为 Gap Lock 和插入意向锁冲突，所以能防止幻读。**

我：Gap 锁和任意锁都不兼容，和是不是意向锁没关系。

我认为如果已经加了 gap 锁，是 X 锁，X 锁与任意锁都是不兼容的。这里说的 X 锁属于行级别锁，InnoDB 实现了 X 锁（排他锁）和 S 锁（共享锁） 2 种行级别的锁，前者容许事务删除或更新一行数据，后者允许读取一行数据。

意向锁是什么意思呢？是指 InnoDB 引擎支持多粒度锁定，将加锁对象分为多个层级，分别加不同类型的锁，比如要对表 A 中记录加 X 锁，那么先对表 A 加 IX 意向锁，再对记录加 X 锁。

InnoDB 设计意向锁为表级别的锁，支持 2 种意向锁：IS 和 IX 前者表示事务想要获取一张表中某几行共享锁，后者表示事务想要获取一张表中某几行的排他锁。

而彪神说的这个插入意向锁是什么意思呢？并不是 IS 和 IX ，而是 Gap Lock 的一种，指的是 **Insert Intention** Lock，在 insert 操作时产生。在多事务同时写入不同数据到同一个索引范围时，并不需要等待其他事务完成，不会发生锁等待。假设一个记录包含索引 4 和 7，当事务分别插入 5 和 6，每个事务都会产生一个插入意向锁，获取在插入行上的排他锁，但是不会被互相锁住，因为数据行不冲突。

**彪神：Gap Lock 互相兼容，拿 test 表数据来说如果事务更新 b = 3，另一事务更新 b = 6 不会阻塞。**

我：会阻塞。

我认为范围会重叠，会阻塞。这个明显是我想错了，Gap Lock 锁的是范围，不是值，也就是说 update b = 3 时， Gap Lock 的右侧范围是到 (7,6) 但是并不会锁 (7,6) 这条记录。

### MySQL 8 测试 ###

准备数据

CREATE TABLE `test` (
      `a` int NOT NULL,
      `b` int NOT NULL,
      PRIMARY KEY (`a`),
      KEY `idx_b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into test (a,b) values (1,1); 
    insert into test (a,b) values (3,1); 
    insert into test (a,b) values (5,3);
    insert into test (a,b) values (7,6); 
    insert into test (a,b) values (10,8);

#### Update，Gap Lock 范围重叠 ####

事务一修改 b=3，不提交

begin;
    update mxc.test set a = 6 where b = 3;

查看 `performance_schema.data_locks` 表中记录，加锁情况如下：

![WX20210713-143639][]

因为 set a=6，所以 Gap 锁有一条是 （3，6），另外没有显示左侧范围（1，3），实际上如果插入（4，3）这条记录，会阻塞，加锁情况如下，插入意向锁等待：

insert into mxc.test (a,b) values (4,3);

![WX20210713-144204][]

事务二也去修改 b=3，加锁情况如下，由于排他锁等待：

update mxc.test set a = 6 where b = 3;

![WX20210713-144502][]

事务二修改 b=6，执行成功

update mxc.test set a = 11 where b = 6;

如下图，b 字段的索引结构的叶子节点，Gap 锁范围内不能插入，X 锁范围内不能修改。

![WX20210713-160018][]

所以我觉得 **Gap 锁就是防止插入的，对于修改则是具体某条记录上的 X 锁**。

#### 范围扫描，Lock ####

select * from mxc.test where b > 3 for update;

此时的加锁情况，supremum pseudo-record 相当于比索引中所有值都大，但却不存在索引中，相当于最后一行之后的间隙锁：

![WX20210713-152718][]

[mysql]: https://www.cnblogs.com/shoshana-kong/p/10516404.html
[WX20210713-143639]: /images/20221004/ffd5cb17fdda4d00839f4ec148ef6406.png
[WX20210713-144204]: /images/20221004/5674f365f23349e2a376e002b62ddb1d.png
[WX20210713-144502]: /images/20221004/8db21d5101a244afaea11abdcec01e53.png
[WX20210713-160018]: /images/20221004/7dfba0d9428f493cbea78865d5b90422.png
[WX20210713-152718]: /images/20221004/7954689c212644aa944f1053252c6f34.png