MySQL的加锁规则

学习了MySQL的锁后，知道其有这么多锁，那应该会有些疑惑，这么多锁，究竟我在写sql语句时候用到哪个锁的，什么情况是用什么锁的？在哪里查看该sql语句是用了哪些锁的呢？加锁的规则是什么呢？这篇文章就来解决这些疑惑。

MySQL的默认存储引擎是innodb，而我们常用的也是innodb，所以我们主要研究的是行级锁。行级锁有行锁、间隙锁、临键锁。

间隙锁和临键锁的唯一目是为了解决幻读现象。在可重复读（RR）隔离级别解决了幻读问题。那很明显在RR隔离级别下是使用了间隙锁和临键锁。

间隙锁和临键锁只有在可重复读隔离级别中才会存在，如果是在RC读已提交隔离级别下，是没有间隙锁的存在，只有行锁。

所以，我们只讨论在RR隔离级别的innodb引擎表的锁。

这里测试环境mysql版本：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

--测试使用的表和表数据
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);

下图中的规则是《MySQL45讲》专栏作者总结的。

我的理解：

从我使用的MySQL版本(MySQL8.0.36)测试来看，一个"bug" 是已经修复的了。

首先要清楚：加锁是对索引加锁的，不是对该行记录加锁的。

比如select id from t where c=10 lock in share mode;是对字段c的索引加临键锁(5,10]，不是对主键索引加锁。

而比如select * from t where c=10 lock in share mode;对字段c的索引加临键锁(5,10]，查询的是所有字段，就需要回表，那就会用到主键查询，那就是会访问到主键嘛， 也对主键索引加锁，这就是原则2所讲的。

优化1中所讲的，因为是唯一索引，那就只需要查询到该索引等值，就可以直接返回这个数据，因为是唯一的，不需要再往后查找了。只需要锁住那条数据即可，那next-key lock就可以退化为行锁。

所以是要区分唯一索引和非唯一索引。

优化2中，等值查询的向后遍历是什么意思？因为可能这个等值查询的数据是不存在的。

比如 select id from t where id=9。而id=9这个数据是不存在的 ，那主键加锁就加在9的前后两个数据之间:id=5和id=10，所以是加临键锁(5,10]。而这个是等值查询，就继续在(5,10]从9往后遍历，到最后一个数据id=10，10不满足条件，那就不锁10,就可以退化成间隙锁(5,10)。

我个人认为：只需记住2点：

一个原则：加锁的基本单位是 next-key lock。加锁是对索引加锁的。

一个bug:非唯一索引在范围查询时，向右扫描到的第一个不符合条件的记录不会发生next-key lock退化为间隙锁的操作。

其他就通过常理来分析推理即可。

为什么说是一个bug,是因为非唯一索引的范围查询不符合常理分析推理。

那接下来结合我总结的，用具体例子来讲解。

案例一：唯一索引(主键)等值查询

红圈是sql语句输入的时间顺序。

分析过程：

• 根据一个原则，加锁的基本单位是 next-key lock，查询条件是id=7, 表中是没有id=7的数据，对主键加临键锁(5,10]。
• 因为id=10是不符合条件的，所以可以退化成间隙锁(5,10)。

所以插入数据(8,8,8)，即是id=8的数据被阻塞，因为id在（5,10）之间被锁住了，而更新id=10的数据是成功的。

上面的是我们的分析结果，那我们如何可以通过数据查看是否是加了这些表的呢？可以通过该语句

select * from performance_schema.data_locks\G;

来查看。

在执行了上图的第二步后，就执行该语句，可得

说明在主键索引加的是间隙锁(5,10），这是符合我们分析得到的结果的。 

案例二：非唯一索引等值锁

大家可能想到会话2应该是被阻塞的，而会话3是成功的，但结果却是相反的。

注意：普通的select...是不加锁的，select ... lock in share mode 和 select ... for update才是加锁的。lock in share mode是共享读锁，for update是独占写锁。

分析过程:

• 根据一个原则，加锁的基本单位是 next-key lock，查询条件是c=5,给普通索引c加临键锁(0,5]。
• 因为索引c不是唯一索引，那该值可能有多个，就需要继续往右查询，直到找到第一个不符合该值的值，那就是到了c=10,所以还需要给索引c加临键锁(5,10]，而10是不符合条件的，所以退化成间隙锁(5,10)。这里非唯一索引的锁可以退化成间隙锁是因为这个查询不是范围查询(该查询条件是where c=5）。
• 这里是select id,不需要回表，就不用给主键索引加锁。

 为什么索引c的临键锁(0,5]不退化成行锁？

因为 c字段是非唯一索引，不具有唯一性，所以非唯一索引的索引结构都是和主键连结在一起的，而performance_schema.data_locks表中LOCK_DATA中就需要加上主键值。 所以如果只加记录锁（记录锁无法防止插入，只能防止删除或者修改），就会导致其他事务插入一条 (c=5,id=4) 的记录，这样前后两次查询的结果集就不相同了，出现幻读现象。

主键索引上没加锁，所以 会话2的 update .... where id=5语句不被阻塞。

但是在会话3中insert values(7,7,7)，即是c=7的数据，而索引c的锁范围是(0,10),所以c=7会被锁住。

查看加锁情况

在上图的步骤2后执行select * from performance_schema.data_locks\G。

 通过得知是对索引c加了临键锁(0,5]和间隙锁（5,10），总的即是锁住(0,10），符合我们分析得到的结果。

需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

另一种情况：

假如会话1中的select语句改成select * from t where c=5 lock in share mode,这个时候是查询所有字段，就会回表，那就需要主键索引，给主键索引加锁。

根据一个原则，加锁单位是临键锁，给主键加临键锁(0,5],而这里是等值查询，而且主键是唯一的，所以就只锁住id=5这行即可，即是退化成行锁。

案例三：主键索引范围锁

举例之前，你可以先思考一下这个问题：对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

id是int类型的，那这两条语句的逻辑是一样的。但是它们并不是等价的。

先来分析下第一条语句。

• 根据原则1，加锁的单位是临键锁。查询条件是id=10，那给主键索引加临键锁(5,10]。
• 主键是唯一的，那找到第一条id=10就会找到结果，不用再继续往右查找的。而表是有id=10这条数据的，所以不需要锁住(5,10]，只需锁住10,这时就退化成行锁。

接着来看第二条语句

 分析过程：

• 根据原则1，加锁的单位是临键锁。
• 查询条件id>=10部分。id=10部分，主键加临键锁(5,10]。主键索引也是唯一索引，表也有id=10这条数据，所以退化成行锁，锁住id=10。>10部分，主键加临键锁(10,+∞]。
• 而id<11部分，(id=11的下行数据是id=15),主键加临键锁(-∞,15],因为最后一个是15，不符合id<11，所以就退化成间隙锁(-∞,15)。这里我们要清楚，临键锁退化成间隙锁，是只能去掉临键锁的右区间，不是直接变成（-∞，11）。
• 所以结合(10,+∞]和(-∞,15)和行锁id10，变成锁住[10,15)。即主键加的锁：行锁id=10,间隙锁(10,15)。

备注:在《MySQL45讲中》，该作者实验的步骤5的更新语句是被阻塞，而我使用的MySQL版本显示是更新成功，没有被锁的。作者的分析 是主键加锁是行锁 id=10 和 next-key lock(10,15]。这个和现在我的测试结果不符合的。可能是MySQL版本不同导致的，请大家注意。

查看加锁情况

在上图的步骤2后执行select * from performance_schema.data_locks\G。

 主键加锁：行锁id=10 和 间隙锁(10,15)符合我们分析得到的结果。

案例四：非唯一索引范围锁

该案例可以对照着案例三来看。与案例三不同的是，当前例子中查询语句的 where 条件中用的是字段 c(普通索引)。

这也是个困惑点，来验证我说的一个“bug”：非唯一索引范围查询，该索引的临键锁不会有退化为间隙锁或记录锁。这个bug主要是针对 查询条件是 非唯一索引<查询值 时候出现的，例如select ... where c<11。

步骤4和5更新的是同一行数据的，但是通过索引c更新的被堵住，说明加锁的是对索引加锁的，不是对行数据加锁。

分析过程：

• 根据一个原则，加锁的基本单位是临键锁。查询条件是c>=10 and c<11。对普通索引c加临键锁。
• c>=10部分。由于c=10是不唯一的，为了防止幻读，必须在这个前后都加上next-key lock锁,所以给索引c加临键锁(5,10],(10,15],(15,20],（20,25],(25,+∞]。
• c<11部分，索引c加临键锁(-∞,15]，按照常理来分析推理的话，15不符合条件，所以退化成间隙锁(-∞,15)，但是MySQL对此不是这样操作的，这个就是我说的一个bug，这里符合非唯一索引的范围查询，索引不能退化成间隙锁,所以是(-∞,15]。
• 所以综合索引c的锁，其加锁是：临键锁(5,10],临键锁(10,15]。
• 因为是select *,所以需要回表。表有c=10这条数据，所以要锁住c=10对应的主键索引，即是锁住主键10，对主键加行锁。

insert语句中字段c=8,在索引c的锁范围(5,15]内，会被阻塞。而通过字段c=15来update也会被阻塞。通过id=15来update就不会阻塞，因为主键索引只加了行锁(id=10）。

查看加锁情况

在上图的步骤2后执行select * from performance_schema.data_locks\G。

 可以看到第二个锁的范围是(10,15]，这是包含15的，但是按照 select * from t where c>=10 and c<11 for update;的逻辑那肯定是不锁住c=15的，但是该实现的锁就是锁住了c=15，这就不符合常理。

 更简单的，我们可以查看where c<11的加锁情况。

select * from t where c<11 for update;

--for update和lock in share mode 需要在事务中才能起效的，只是单单执行上面的语句是不起效的

begin;
select * from t where c<11 for update;

 select * from performance_schema.data_locks\G;查看锁情况。

按照常理分析，c=15是不符合条件的，所以就不应该锁住c=15的，但是结果却是临键锁，锁住了15。所以在对非唯一索引范围查询时有特别的做法， 不能退化成间隙锁。

案例五：非唯一索引上存在”等值”的例子

该例子，是为了更好地说明“间隙”这个概念。这里，我给表 t 插入一条新记录。

mysql> insert into t values(30,10,30);

新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。那么，这时候索引 c 上的间隙是什么状态了呢？而由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

可以看到字段c是有两个10的，但是他们的主键是不一样的。所以这两个 c=10 的记录之间，也是有间隙的。

这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，是加排他锁，其实跟 select … for update 是类似的。

分析过程：

• 根据一个原则，加锁的基本单位是临键锁。条件是c=10，这不是唯一索引，为了防止幻读，对此前后加间隙锁，(5,10],(10,15]。因为这是等值查询，15不符合条件，临键锁(10,15]可以退化成间隙锁(10,15)。所以对普通索引c加的锁：临键锁(5,10]，间隙锁(10,15)。
• 表中是有c=10这条数据的，那就需要回表，找到id=10和id=30符合条件，所以主键需要加两个行锁，锁id=10和id=30。

更新语句条件是c=15,没在普通索引c的加锁范围内，所以成功。而第三步的插入语句中插入了c=12的语句，所以被阻塞。

接着来看看步骤5,6是怎情况。步骤5插入数据（4,5,100）不阻塞，步骤6插入数据（6,5,100）被阻塞。100对应的字段是没有索引的，可以不用关注。

而明明普通索引c的加锁范围是(5,15),是不包括5的，为什么数据（6,5,100）会被阻塞的呢？

那明显是在主键上不同导致的插入阻塞的。

这时我们来看看普通索引c的加锁范围吧，中蓝色区域覆盖的部分就是加锁的范围。

为什么索引c的加锁范围变成这样呢？就是因为索引c不是唯一索引，可以有多个c=10，而c=10对应的多条数据的主键却是是不能相同的，所以加锁才会变成这样的。

从上图就可以看出来，在插入数据时候，id>5(比如6)就会被阻塞；id<5(比如4)就不被阻塞。

所以在插入数据时，能否插入成功，可能需要结合普通索引和主键索引来具体分析的。

查看加锁情况

在上面的步骤2后执行select * from performance_schema.data_locks\G;。

从这就可以看出，需要通过主键才能确定普通索引对应的数据。

案例六：limit 语句加锁

该例子对照案例五，在案例五的基础上limit 2。

这个例子里，左边会话 的 delete 语句加了 limit 2。而表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，右边会话 的 insert 语句执行通过了(案例5中也是插入（12,12,12）,但是插入阻塞)。

分析过程：

• 案例5中分析出来索引c的锁范围是(5,15),但是现在案例6中是limit2,其只需要两条数据。
• 在锁(5,15）范围内找到了符合条件的2条数据，这里到了（c=10,id=30）,但还没到锁范围的右边界，但是因为已经找齐数据了，就不再需要后面的锁了。这是很符合常理的，让所需的锁范围尽量小，而又不破坏业务需求。

可以看到，(c=10,id=30）之后的右边间隙并没有在加锁范围里，因此 insert 语句插入 c=12 是可以执行成功的。

这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例七：order by查询语句(倒序)

没有order by语句的查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select id from t where c>=15 and c<=20 lock in share mode; 
+----+
| id |
+----+
| 15 |
| 20 |
+----+
2 rows in set (0.00 sec)

该语句加的锁有哪些呢？

分析过程：

非唯一索引在范围查询时，向右扫描到的第一个不符合条件的记录的加锁不会退化为间隙锁。

根据一个原则，加的锁是临键锁。where中是字段c,就是对索引c加锁。

• 先说c>=15部分。c不是唯一索引，为防止幻读，需要对c=15前后都临键锁，那即是(10,15],(15,20],(20,25],(25,+∞]。
• c<=20部分。c不是唯一索引，为防止幻读，需要对c=20前后都加临键锁。而且是范围查询，向右扫描到的第一个不符合条件的记录的加锁不会退化为间隙锁。所以加锁(-∞,0],(0,5],(5,10],(10,15],(15,20],(20,25]。（25是第一个不符合条件的记录）
• 因为是select id....，不需要回表，并且是lock in share mode,所以主键索引没有加锁。
• c>=15和c<=20加的锁范围就是：(10,15],(15,20],(20,25]。

查看加锁情况

有order by语句的查询(倒序）

倒序就是使用order by .... desc。

而正序查询的效果就和不使用order by c查询一样的，select id from t where c>=15 and c<=20 lock in share mode就是正序排序查出来的(前提是where中的条件和order by中的是同一字段)。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from t where c>=15 and c<=20 order by c desc lock in share mode;
+----+
| id |
+----+
| 20 |
| 15 |
+----+
2 rows in set (0.00 sec)

分析过程：

• 由于是order by c desc，那就是从右往左找了。
• 第一个要定位的是索引c上“最右边的”c=20的行，索引c不是唯一索引，为了防止幻读，对c=20前后加临键锁(15,20],(20,25]。这个是等值查询，因为25不符合条件的，所以临键锁(20,25]退化成间隙锁(20,25)。
• 在索引c上向左遍历，要扫描到c=10才停下来，所以next-key lock会加到(5,10]。非唯一索引在范围查询时，向右扫描到的第一个不符合条件的记录的加锁不会退化为间隙锁。这里是从右往左查找了 ，所以是临键锁，10也被锁住，而非唯一索引的锁不会退化成行锁，所以还需要锁住(5,10]。(感觉这个是代码层面需求吧,，因为从常理来分析不锁住(5,10]也不会出现幻读现象嘛)
• 因为是select id....，不需要回表，并且是lock in share mode,所以主键索引没有加锁。
• 即是对索引c加锁:（5,10],(10,15],(15,20],（20,25）。

查看加锁情况

案例八:非索引的查询

字段d是没有创建索引的

通过无索引字段d来进行查询，右边会话被阻塞。

查看加锁情况

 每一条记录的索引上都会加 next-key 锁，都是锁住主键，这样就相当于锁住的全表。

注意的是：不是加上表锁的。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

案例九：死锁例子

该案例目的是说明：next-key lock实际上是间隙锁和行锁加起来的结果。

分析过程：

• 步骤2中查询c=10,即是对索引c加锁:临键锁(5,10],间隙锁(10,15)。
• 右边会话 的update语句也要在索引c上加临键锁(5,10] 和间隙锁(10,15)，进入锁等待；
• 然后左边会话要再插入(8,8,8)这一行，被右边会话的间隙锁锁住。由于出现了死锁，InnoDB让session B回滚。

读者应该会疑惑，右边会话的临键锁不是还没申请成功吗？

其实是这样的，右边会话的“加next-key lock(5,10] ”操作，实际上分成了两步，先是加(5,10)的间隙锁，加锁成功(间隙锁是不冲突的）；然后加c=10的行锁，这时候才被锁住的。

右边会话也对索引c加了间隙锁(5,10)，所以左边会话的插入(8,8,8)会被右边会话的间隙锁锁住。

这时，左边和右边会话拥有索引c的间隙锁(5,10),这两会话涉及的线程都在等待别的线程释放资源时，就会导致这2个线程都进入无限等待的状态，这就出现死锁。

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

注意：这里解开死锁的策略是主动死锁检测，由innodb_deadlock_detect控制，其的默认值本身就是on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

总结

加锁的规则：

一个原则：加锁的基本单位是 next-key lock。加锁是对索引加锁的。

一个bug:非唯一索引在范围查询时，向右扫描到的第一个不符合条件的记录不会发生next-key lock退化为间隙锁的操作。

其他就通过常理来分析推理即可。

其实感觉这规律也是很难总结，也是复杂，还有些规则是不符合常理的，可能还没有测试到其他的特例。MySQL版本不一样也有出入,但规则大致是一致的，望仔细思考。

这里的常理是要是能用行锁或者间隙锁替代临键锁而不出现幻读现象，就使用行锁或间隙锁。尽量减少锁的范围嘛。

查询的情况就那几种：

唯一索引等值查询

唯一索引范围查询

非唯一索引等值查询

非唯一索引范围查询

非索引查询