MySQL(5):让你疑惑的锁案例

上文MySQL(四):锁的原理，讲述每个锁的原理，其中InnoDB独有的行锁，RR级别下，增删改的时候，执行语句时会加上行锁，本文，我们重点讲一下行锁，间隙锁，那为什么在RR级别下，无法解决幻读呢?

大纲内容

• 幻读
• 加锁规则
  

---

幻读

并发问题带来的问题

脏读：事务A读到了事务B未提交事务的最新值。

不可重复读：事务A读到了事务B已提交的最新值，侧重于数据内容的变化

幻读：事务A读到了事务B新增的记录，侧重于数据行数的变化。

在RR级别下，普通查询都是快照读，是不会看见其他事务新增的数据，幻读只有在当前读的场景下产生。

常见的当前读：InnoDB下加共享锁，排他锁，增删改都会触发当前读，当前读的意思是每次都读取最新的值。

排他锁 ：select * from… in share mode

共享锁：select * from… for update

在MySQL(二): 事务的隔离性原理中，我们举例子谈及到RR级别下可能产生幻读，再阐述一下RR级别下产生幻读的例子。

简述：在RR级别下，通过MVCC多并发版本控制来保证隔离性，但事务A读到了事务B已提交事务的新增数据, 造成了幻读。切记：幻读只有在当前读的场景下，读到了其他事务已提交事务的新增数据。

为了解决幻读问题：使用间隙锁，间隙锁之间是互不影响的，可以有多个事务拥有相同的间隙锁，只有新增才会有影响，比如一个事务A和事务B都获取间隙锁(5,10)，互不影响，但新增，即阻塞。

---

加锁规则:

幻读是什么？举例子-反证法，表结构如下

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

加锁规则：两个原则，两个优化，一个BUG。

• 原则一：加锁的基本单位是next-key-lock，next-key-lock是由行锁和间隙锁组成，申请锁是先申请间隙锁，再申请行锁的。遵循左开右闭原则。比如(1,100]，加锁对象都是索引值。

• 原则二：查找过的索引对象才加锁。

• 优化一：唯一索引的等值查询，当前值若存在，则next-key-lock退化成行锁，(个人理解:就是一条记录的行锁，简称记录锁)，若当前值不存在，则next-key-lock退化成间隙锁，向右遍历到第一个不为当前值的索引值，next-key-lock退化成间隙锁，即左开右开。

• 优化二：普通索引的等值查询，由于没有唯一性约束，向右遍历到第一个不为当前值的索引值，此next-key-lock退化成间隙锁。

• BUG：唯一索引和普通索引的范围查询，都会向右遍历到第一个不为当前值的索引值，遵循左开右闭原则。(唯一索引的范围查询在8.0版本之后已修复)

案例1: 主键索引的等值查询

根据原则一，加锁的单位都是next-key-lock，即（5，10】。根据唯一索引的等值查询找到优化一，当值不存在时，会向右遍历第一个不为当前值的索引值，即找到10，next-key退化为间隙锁，即(5，10)。

事务B新增id=8时，当前事务A已拥有间隙锁，固阻塞。

事务C更新id=10，当前事务A已拥有间隙锁，固阻塞。

个人理解：7在5~10之间。

案例2: 普通索引的等值查询

事务A根据原则一，加锁都是以next-key-lock为单位，（0，5】，根据优化二，普通索引的等值查询，无论值是否存在，都会向右遍历找到第一个不为当前值的索引值，根据原则二，扫描过的索引对象就要加锁，固扫到10时，需要加锁（5，10】，同时此next-key-lock退化为间隙锁，固当前锁住索引的值（0，10）。根据原则二：只有扫描到的索引对象才会加锁，当前SQL语句id的值能在二级索引上找到，固不需回表。

事务B操作成功，id压根就没有被锁住，事务A锁的是索引c的(0，10)。

事务C往间隙锁中插入c=7，被锁住了，固阻塞。

in share model是加共享锁，可以被覆盖索引优化，但for update 无法被覆盖索引优化，系统会自动给主键索引也加上锁。

---

案例3:主键索引的范围查询

事务A，由于是范围查询，并且是升序，则只关心大于号，根据id排序：0，5，10，15，20，25，则先处理id=10，根据原则一，加锁都是以next-key-lock为单位，固加了(5，10】。根据优化二，当前id=10存在并且是等值查询，固next-key-lock退化成id=10加行锁，根据BUG，主键索引的范围查询会向右找到第一个不为当前值的索引值，左开右闭，（10,15】，最终加锁范围：【10,15】。

事务B新增id=8，未被锁住，但新增id=13，位于锁内，阻塞

事务C id=15 位于锁内，阻塞。

---

案例4:普通索引的范围查询

事务A，由于是范围查询，并且是升序，则只关心大于号，先处理c=10，根据原则一，加锁都是以next-key-lock为单位，固（5，10】，根据优化二，普通索引的等值查询，会向后遍历第一个不为当前值的索引值，此next-key-lock退化成间隙锁，（10，15】–退化–>（10，15）。根据BUG，索引的范围查询都会向后遍历第一个不为当前值的索引值，固（10，15】，最终锁范围：（5，10】，（10，15】

插入c=8的被阻塞。

更新c=15的数据被阻塞。

案例5:

事务A由于用了in share mode和覆盖索引，固不会回表，根据原则1，加锁单位都是以next-key-lock为单位，固加锁：（5，10】，根据优化2，由于是普通索引的等值查询，会向后遍历第一个不为当前值的索引值，此next-key-lock退化成间隙锁，（10，15），结果：（5，10】，（10，15）

事务B操作c=10，阻塞了，但间隙锁之间是不互斥的，根据原则1：申请锁是先申请间隙锁，再申请行锁的，固申请（5，10），申请行锁时，阻塞了。

事务A插入c=8时，阻塞了，发生死锁了。

---

案例6:倒序的范围查询

这里用到了order by id desc，因为是倒序，则只关心小于号，根据id倒序：25，20，15，10，5，0。其中找到where条件中的最右边界值为12，虽然是<12，但要按照12去找。根据原则一，找到（10,15】, 再根据优化一，因为id=12不存在，固next-key-lock退化成间隙锁，（10，15）。

根据原则二，对扫描过的索引对象都加锁，固扫描到10时，发现符合id>9，则加锁（5，10】，固扫描到5时，发现不符合id>9，但扫描过的对象都会加锁，固锁了（0，5】，最终锁了（0，5】，（5，10】，（10，15）。

我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

使用in(10,20,15)如果走了索引，mysql是在执行过程中一个一个按照阿拉伯顺序加锁的，而不是一次性都加上锁。

在删除delete DML语句时，尽量加上limit，可以控制删除数据的条数，也可以减少锁的范围。