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:非唯一索引范围查询，该索引的临键锁不会有退化为间隙锁或记录锁。

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

为什么说是一个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，对普通索引c加临键锁(5,10]。案例二中讲明白了这个不能退化成为行锁。c>10部分，对索引c加临键锁(10,+∞]。即c>=10部分，索引c加锁范围是(5,+∞]。
c<11部分，索引c加临键锁(-∞,15]，按照常理来分析推理的话，15不符合条件，所以退化成间隙锁(-∞,15)，但是MySQL对此不是这样操作的，这个就是我说的一个bug，这里符合非唯一索引的范围查询，索引不能退化成间隙锁,所以是(-∞,15]。
所以综合(5,+∞]和(-∞,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，对普通索引c加临键锁(5,10]。
而这不是唯一索引，所以需要继续完后搜索，直到搜索到c=15才停止，这里就加临键锁(10,15]，因为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）,但还没到锁范围的右边界，但是因为已经找齐数据了，就不再需要后面的锁了。这是很符合常理的，让所需的锁范围尽量小，而又不破坏业务需求。