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

介绍

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

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

√

serializable （可串行化）

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

那么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');