MySQL如何解决幻读和不可重复度？

文章参考：

• https://juejin.im/post/5b8577c26fb9a01a143fe04e
• https://joonwhee.blog.csdn.net/article/details/106893197
• http://blog.51cto.com/14344203/2402076

注意：探讨MySQL如何防止不可重复度和幻读问题之前，默认大家已经理解脏读、幻读、不可重复读的区别，以及数据库事务的3种隔离级别！

1. MySQL中的3种锁算法

首先了解下MySQL中的3种锁算法：

• Record lock：记录锁（行锁），单条索引记录上加锁，锁住的永远是索引，而非记录本身。

• Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

• Next-key lock：临键锁，Record lock 和 Gap lock 的结合，即除了锁住记录本身，也锁住索引之间的间隙。

下面来详细介绍下这三种锁：

1.1 记录锁Record Lock

顾名思义，记录锁就是为某行记录加锁，它封锁该行的索引记录：

-- id 列为主键列或唯一索引列
SELECT * FROM 表名称 WHERE id = 1 FOR UPDATE;

这时候 id 为 1 的记录行会被锁住。

需要注意的是：

• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。（行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁）

• 同时查询语句必须为精准匹配=，不能为>、<、like等，否则也会退化成临键锁。

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

1.2 间隙锁Gap Locks

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM 表名称 WHERE id BETWEN 1 AND 10 FOR UPDATE;

• 即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

• 除了手动加锁外，在执行完某些 SQL 后，InnoDB 也会自动加间隙锁，这个我们在下面会提到。

1.3 临键锁Next-Key Locks

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

假设有如下表：

引擎：InnoDB，隔离级别：Repeatable-Read：table(id PK, age KEY, name)

id	age	name
1	10	Lee
3	24	Soraka
5	32	Zed
7	45	Talon

该表中 age 列潜在的临键锁有：

(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

在事务 A 中执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 24;
-- 或根据非唯一索引列 锁住某条记录
SELECT * FROM table WHERE age = 24 FOR UPDATE;

不管执行了上述 SQL 中的哪一句，之后如果在事务 B 中执行以下命令，则该命令会被阻塞：

INSERT INTO table VALUES(100, 26, 'Ezreal');

很明显，事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (24, 32] 这个区间内的临键锁。

不仅如此，在执行以下 SQL 时，也会陷入阻塞等待：

INSERT INTO table VALUES(100, 30, 'Ezreal');

那最终我们就可以得知，在根据非唯一索引对记录行进行UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的临键锁 ，并同时获取该记录行下一个区间的间隙锁。

即事务 A在执行了上述的 SQL 后，最终被锁住的记录区间为 (10, 32)。

1.4 小结

• InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
• 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
• 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
• 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。即，除了锁住记录本身，也锁住索引之间的间隙。

注意：

InnoDB 行锁是通过索引上的索引项来实现的。意味者：只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则，InnoDB将使用表锁！

• 对于主键索引：直接锁住锁住主键索引即可。
• 对于普通索引：先锁住普通索引，接着锁住主键索引，这是因为一张表的索引可能存在多个，通过主键索引才能确保锁是唯一的，不然如果同时有2个事务对同1条数据的不同索引分别加锁，那就可能存在2个事务同时操作一条数据了。

扩展：MySQL 如何实现悲观锁和乐观锁？

• 乐观锁：更新时带上版本号（cas更新）
• 悲观锁：使用共享锁和排它锁，select...lock in share mode，select…for update。

---

2. MySQL如何解决不可重复读

• MySQL中，默认使用的事务隔离界别是可重复读，为了解决不可重复读问题，InnoDB采用了MVCC(多版本并发控制)【基于乐观锁】来解决！
• MVCC(多版本并发控制)是利用在每条数据后面加了隐藏的两列（创建版本号和删除版本号），每个事务在开始的时候都会有一个递增的当前事务版本号！

例如：

-- MVCC新增
begin; -- 假设获取的 当前事务版本号=1
insert into user (id,name,age) values (1,"张三",10); -- 新增，当前事务版本号是1
insert into user (id,name,age) values (2,"李四",12); -- 新增，当前事务版本号是1
commit; -- 提交事务

id	name	age	create_version	delete_version
1	张三	10	1	NULL
2	李四	12	1	NULL

-- 上表可以看到，插入的过程中会把当前事务版本号记录到列 create_version 中去！

-- MVCC删除：删除操作是直接将行数据的删除版本号更新为当前事务的版本号
begin； --假设获取的 当前事务版本号=3
delete from user where id = 2;
commit; -- 提交事务

id	name	age	create_version	delete_version
1	张三	10	1	NULL
2	李四	12	1	3

-- MVCC更新操作:采用 delete + add 的方式来实现，首先将当前数据标志为删除，然后再新增一条新的数据
begin;-- 假设获取的 当前事务版本号=10
update user set age = 11 where id = 1; -- 更新，当前事务版本号是10
commit; -- 提交事务

id	name	age	create_version	delete_version
1	张三	10	1	10
2	李四	12	1	3
1	张三	11	10	NULL

-- MVCC查询操作：
begin;-- 假设拿到的系统事务ID为 12
select * from user where id = 1;
commit; -- 提交事务

查询操作为了避免查询到旧数据或已经被其他事务更改过的数据，需要满足如下条件：

1、查询时当前事务的版本号需要大于或等于创建版本号create_version

2、查询时当前事务的版本号需要小于删除的版本号delete_version，或者当前删除版本号delete_version=NULL

即：(create_version <= current_version < delete_version) || (create_version <= current_version && delete_version-=NULL) ，这样就可以避免查询到其他事务修改的数据，同一个事务中，实现了可重复读！

执行结果应该是：

id	name	age	create_version	delete_version
1	张三	11	10	NULL

---

3. MySQL如何解决幻读

3.1 MySQL采用的MVCC可以解决幻读吗？

幻读：在一个事务中使用相同的 SQL 两次读取，第二次读取到了其他事务新插入的行，则称为发生了幻读。

例如：

• 1）事务1第一次查询：select * from user where id < 10 时查到了 id = 1 的数据

• 2）事务2插入了id = 2 的数据

• 3）事务1使用同样的语句第二次查询时，查到了 id = 1、id = 2 的数据，出现了幻读。

谈到幻读，首先我们要引入“当前读”和“快照读”的概念，通过名字就可以理解：

• 快照读：生成一个事务快照（ReadView），之后都从这个快照获取数据。普通 select 语句就是快照读。
• 当前读：读取数据的最新版本。常见的 update/insert/delete、还有 select ... for update、select ... lock in share mode 都是当前读。

对于快照读，MVCC 因为因为从 ReadView 读取，所以必然不会看到新插入的行，所以天然就解决了幻读的问题。

而对于当前读的幻读，MVCC 是无法解决的。需要使用 Gap Lock 或 Next-Key Lock（Gap Lock + Record Lock）来解决。

其实原理也很简单，用上面的例子稍微修改下以触发当前读：select * from user where id < 10 for update，当使用了 Gap Lock 时，Gap 锁会锁住 id < 10 的整个范围，因此其他事务无法插入 id < 10 的数据，从而防止了幻读。

3.2 有人说 RR 解决了幻读是什么情况？

SQL 标准中规定的 RR 并不能消除幻读，但是 MySQL 的 RR 可以，靠的就是 Gap 锁。在 RR 级别下，Gap 锁是默认开启的，而在 RC 级别下，Gap 锁是关闭的。

文章参考：[MySQL 到底是怎么解决幻读的？](https://blog.csdn.net/weixin_33795833/article/details/93036775]