面试官：MySQL是如何解决幻读的？

介绍

众所周知，在不同隔离级别下，会发生如下问题。

√ 为会发生，×为不会发生

幻读

√

不知道这些问题是如何产生的，可以看如下文章《面试官：脏读，不可重复读，幻读是如何发生的？》

那么mysql是如何避免脏读，不可重复度，幻读的？其实有两种方案

方案一：读操作使用多版本并发控制（MVCC），只对写操作加锁

mvcc之前已经介绍过，每次事务开启的时候，都会生成一个ReadView，然后找到版本链上对当前事务可见的版本。读记录的历史版本和改动记录的最新版本这两者并不冲突，所以采用MVCC时，读写并不会冲突

方案二：读写操作都采用加锁的方式

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

如果另一个事务在修改记录的时候对记录加锁，在事务提交后释放锁，那么当前事务在读取记录的时候获取不到锁，就不会出现脏读

不可重复读是指在事务1内，读取了一个数据，事务1还没有结束时，事务2也访问了这个数据，修改了这个数据，并提交。紧接着，事务1又读这个数据。由于事务2的修改，那么事务1两次读到的的数据可能是不一样的，因此称为是不可重复读。

如果当前事务在读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也就不会出现不可重复读的现象了

幻读指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会读到别的事务插入的记录，这些新记录就是幻影记录（InnoDB存储引擎通过多版本并发控制（MVCC）和间隙锁解决了这种情况的幻读问题）

MVCC和间隙锁我们后面接着聊。

这种情况下该怎么加锁呢？因为第一次读取记录的时候，幻影记录并不存在。我们后面见

MySQL中的锁

在MySQL中有三种级别的锁，表锁，行锁，页锁

表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 会发生在：MyISAM、memory、InnoDB、BDB 等存储引擎中

行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。会发生在：InnoDB 存储引擎

页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。会发生在：BDB 存储引擎

InnoDB中的锁

InnoDB存储引擎中有如下两种类型的行级锁

1. 共享锁（Shared Lock，简称S锁），在事务需要读取一条记录时，需要先获取改记录的S锁
2. 排他锁（Exclusive Lock，简称X锁），在事务要改动一条记录时，需要先获取该记录的X锁

如果事务T1获取了一条记录的S锁之后，事务T2也要访问这条记录。如果事务T2想再获取这个记录的S锁，可以成功，这种情况称为锁兼容，如果事务T2想再获取这个记录的X锁，那么此操作会被阻塞，直到事务T1提交之后将S锁释放掉

如果事务T1获取了一条记录的X锁之后，那么不管事务T2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务1提交，这种情况称为锁不兼容。

多个事务可以同时读取记录，即共享锁之间不互斥，但共享锁会阻塞排他锁。排他锁之间互斥

S锁和X锁之间的兼容关系如下

我们可以通过如下语句对读取的记录加锁。

对读取的记录加S锁

select .. lock in share mode;

对读取的记录加X锁

select ... for update

表锁

表级别的S锁，X锁

在对某个表执行select，insert，update，delete语句时，innodb存储引擎是不会为这个表添加表级别的S锁或者X锁。

在对表执行一些诸如ALTER TABLE，DROP TABLE这类的DDL语句时，会对这个表加X锁，因此其他事务对这个表执行诸如SELECT INSERT UPDATE DELETE的语句会发生阻塞

在系统变量autocommit=0，innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表t的S锁或者X锁，可以这么写

对表t加表级别的S锁

lock tables t read

对表t加表级别的X锁

lock tables t write

如果一个事务给表加了S锁，那么

• 别的事务可以继续获得该表的S锁
• 别的事务可以继续获得表中某些记录的S锁
• 别的事务不可以继续获得该表的X锁
• 别的事务不可以继续获得表中某些记录的X锁

如果一个事务给表加了X锁，那么

• 别的事务不可以继续获得该表的S锁
• 别的事务不可以继续获得表中某些记录的S锁
• 别的事务不可以继续获得该表的X锁
• 别的事务不可以继续获得表中某些记录的X锁

所以修改线上的表时一定要小心，因为会使大量事务阻塞，目前有很多成熟的修改线上表的方法，不再赘述

表级别的IS锁，IX锁

为什么要有表级别的IS锁，IX锁？
我们用教学楼和教室的例子类比一下

我们每个人都可以去教室学习，一个教室可以容纳多个人去学习，来一个人学习在门口挂一把S锁，教室可以挂多个S锁（相当于行级别的S锁）。而当教室进行维修的时候，别的工作都不能进行，在教室门口挂了一把X锁（相当于行级别的X锁）

有领导来教学楼视察，学生可以正常学习，但是不能有教室在维修，在教学楼门口挂一把S锁（相当于表级别的S锁）。学生看到教学楼的S锁，可以正常学习，修理工看到教学楼的X锁，就一直等着

学校要占用教学楼考试，不允许学生学习，也不允许维修，在教学楼门口挂一把X锁（相当于表级别的X锁），此时学生和修理工都得等着

这样做有两个问题

1. 想对教学楼上S锁，必须保证没有正在维修的教室
2. 想对教学楼上X锁，必须保证没有正在维修的教室以及教室都是空的

一个一个去查看教室，这样效率太低了。可以这样做

• 学生去教室学习，先在教学楼门口挂一把IS锁，然后在教室门口挂一把S锁
• 维修工去教室学习，先在教学楼门口挂一把IX锁，然后在教室门口挂一把X锁

这样想对教学楼上S锁，只需要看教学楼门口有没有IX锁即可
这样想对教学楼上X锁，只需要看教学楼门口有没有IX以及IS锁即可

使用InnoDB存储引擎，在对表的记录加S锁之前，需要先在表级别加一个IS锁。在对表的记录加X锁之前，需要先在表级别加一个IX锁。IS锁和IX锁只是为了在后续加表级别的S锁和X锁时判断表中是否有已经被加锁的记录，避免用遍历的方式来查看表中有没有上锁的记录

表级别的AUTO-INC锁

在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录的时候，可以不指定该列的值列，系统为他赋上递增的值

如下面这个表

CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT,
    c VARCHAR(100),
    PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO t(c) VALUES('aa'), ('bb');

插入2条记录后，结果如下

mysql> SELECT * FROM t;
+----+------+
| id | c    |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)

MySQL自动给AUTO_INCREMENT修饰的列递增赋值的原理主要有如下两种方式

1. 采用AUTO-INC锁，插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录中AUTO_INCREMENT修饰的列分配递增的值，插入语句执行结束后，再把AUTO-INC锁释放掉（插入语句执行完释放锁，不是事务结束时）。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证分配的递增值是连续的
2. 采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰列的值时获取一下这个轻量级锁，生成本次插入语句需要的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才插入锁

最后总结一下表级别锁的兼容性

不兼容

不兼容

行锁

CREATE TABLE `girl` (
  `id` int(11) NOT NULL,
  `name` varchar(255),
  `age` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into girl values
(1, '西施', 20),
(5, '王昭君', 23),
(8, '貂蝉', 25),
(10, '杨玉环', 26),
(12, '陈圆圆', 20);

InnoDB中有如下三种锁

1. Record Lock：对单个记录加锁
2. Gap Lock：间隙锁，锁住记录前面的间隙，不允许插入记录
3. Next-key Lock：同时锁住数据和数据前面的间隙，即数据和数据前面的间隙都不允许插入记录

Record Lock

对单个记录加锁

如把id值为8的数据加一个Record Lock，示意图如下

Record Lock也是有S锁和X锁之分的，兼容性和之前描述的一样。

SQL执行加什么样的锁受很多条件的制约，比如事务的隔离级别，执行时使用的索引（如，聚集索引，非聚集索引等），因此就不详细分析了，举几个简单的例子。

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ 利用主键进行等值查询
-- 对id=8的记录加S型Record Lock
select * from girl where id = 8 lock in share mode;

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ 利用主键进行等值查询
-- 对id=8的记录加X型Record Lock
select * from girl where id = 8 for update;

Gap Lock

锁住记录前面的间隙，不允许插入记录

MySQL在可重复读隔离级别下可以通过MVCC和加锁来解决幻读问题（后面还会详细介绍哈）

但是该如何加锁呢？因为第一次执行读取操作的时候，这些锁并不存在，我们没有办法加Record Lock，此时可以通过加Gap Lock解决，即对间隙加锁。

如一个事务对id=8的记录加间隙锁，则意味着不允许别的事务在id=8的记录前面的间隙插入新记录，即id值在(5, 8)这个区间内的记录是不允许立即插入的。直到加间隙锁的事务提交后，id值在(5, 8)这个区间中的记录才可以被提交

我们来看如下一个SQL的加锁过程

-- REPEATABLE READ 利用主键进行等值查询
-- 但是主键值并不存在
-- 对id=8的聚集索引记录加Gap Lock
SELECT * FROM girl WHERE id = 7 LOCK IN SHARE MODE;

由于id=7的记录不存在，为了禁止幻读现象（避免在同一事务下执行相同的语句得到的结果集中有id=7的记录），所以在当前事务提交前我们要预防别的事务插入id=7的记录，此时在id=8的记录上加一个Gap Lock即可，即不允许别的事务插入id值在(5, 8)这个区间的新记录

给大家提一个问题，Gap Lock只能锁定记录前面的间隙，那么最后一条记录后面的间隙该怎么锁定？

其实mysql数据是存在页中的，每个页有2个伪记录

1. Infimum记录，表示该页面中最小的记录
2. upremum记录，表示该页面中最大的记录

为了防止其实事务插入id值在(12, +∞)这个区间的记录，我们可以给id=12记录所在页面的Supremum记录加上一个gap锁，此时就可以阻止其他事务插入id值在(12, +∞)这个区间的新记录

Next-key Lock

同时锁住数据和数据前面的间隙，即数据和数据前面的间隙都不允许插入记录

所以你可以这样理解Next-key Lock=Record Lock+Gap Lock

-- REPEATABLE READ 利用主键进行范围查询
-- 对id=8的聚集索引记录加S型Record Lock
-- 对id>8的所有聚集索引记录加S型Next-key Lock（包括Supremum伪记录）
SELECT * FROM girl WHERE id >= 8 LOCK IN SHARE MODE;

因为要解决幻读的问题，所以需要禁别的事务插入id>=8的记录，所以

• 对id=8的聚集索引记录加S型Record Lock
• 对id>8的所有聚集索引记录加S型Next-key Lock（包括Supremum伪记录）

MySQL是如何在可重复读级别下解决幻读的？

众所周知，在不同隔离级别下，会发生如下问题。
√ 为会发生，×为不会发生

隔离级别

脏读

不可重复读

幻读

read uncommitted（未提交读）

√

√

√

read committed（提交读）

×

√

√

repeatable read（可重复读）

×

×

√

serializable （可串行化）

×

×

×

前面已经说过，我们需要将数据库的隔离级别设置为可串行化才能解决幻读，但是在MySQL可重复隔离级别下已经解决了幻读问题，那它是怎么解决的呢？

这就不得不提到MySQL中读取数据的两种方式了

快照读

利用多版本并发控制（MVCC）读取版本链上对当前事务可见的版本，MVCC的实现可以看如下文章，不再详细介绍《面试官：MVCC是如何实现的？》

MVCC通过读取版本链上可见记录的方式，来避免脏读，不可重复读，幻读的，毕竟读写不会冲突，可以极大的提高并发度。因为有可能读取到的数据是之前的数据，所以称为快照读。但是在某些场景下，用户需要读取数据库中的最新记录。这就要求数据库支持加锁语句，即使是对于select的只读操作，这就不提到我们下面要讲的当前读

当前读

对数据库最新记录进行操作，语句如下

select ... lock in share mode;
select ... for update;
insert; 
update; 
delete;

参考博客

《MySQL 是怎样运行的：从根儿上理解 MySQL》
[2]https://www.toutiao.com/a6838563153626792451/
mvcc和间隙锁
[3]https://www.huaweicloud.com/articles/f571bafcbe55475cd94d1f2f65e729a9.html
语句加锁分析
[4]https://mp.weixin.qq.com/s/Lavoo9sgulOzxQ22GRAamw