MySQL 21 为什么我只改一行的语句，锁这么多？

技术分享 8个月前 (07-25) 0 999+

关注

上篇文章中，介绍了间隙锁和临键锁，但并未说明加锁规则。本文首先介绍加锁规则，由于间隙锁在可重复读隔离级别下才有效，因此接下来的内容默认在可重复读隔离级别下。

加锁规则（限5.x系列<=5.7.24, 8.0系列<=8.0.13）：

• 原则1：加锁的基本单位是临键锁，是一个前开后闭区间；

• 原则2：查找过程中访问到的对象才会加锁；

• 优化1：索引上的等值查询，给唯一索引加锁的时候，临键锁退化为行锁；

• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，临键锁退化为间隙锁；

• 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

后续例子用到的表：

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); 

案例一：等值查询间隙锁

由于表中没有id=7的记录，用加锁规则判断：

• 根据原则1，加锁单位是临键锁，session A加锁范围是(5,10]；

• 根据优化2，这是一个等值查询id=7，而id=10不满足查询条件，临键锁退化成间隙锁，因此最终加锁范围是(5,10)。

所以session B要插入id=8的记录会被锁住，但是session C修改id=10这行是可以的。

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

这里session A要给索引c上c=5这一行加读锁：

• 根据原则1，加锁单位是临键锁，因此会给(0,5]加临键锁；

• 由于c是普通索引，因此仅访问c=5这一条记录不能马上停下来，需要向右遍历查到c=10才放弃。根据原则2，访问到的都要加锁，因此给(5,10]加临键锁；

• 这个遍历符合优化2，由于最后一个值不满足c=5这个等值条件，临键锁退化成间隙锁；

• 根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，不需要访问主键索引，所以主键索引上不加任何锁，因此session B的update语句可以成功。

而session C的插入操作，会被session A的间隙锁(5,10)锁住。

在该案例中，lock in share mode只锁覆盖索引，但如果是for update就不同了，因为系统会认为接下来更新数据，会顺便给主键索引上满足条件的行加上行锁。

该案例说明，锁是加在索引上的，同时如果要用lock in share mode来给行加读锁避免数据被更新，就必须绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段，比如将session A的查询语句改成select d from t where c=5 lock in share mode。

案例三：主键索引范围锁

考虑下面这两条查询语句，加锁范围是否相同：

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

在逻辑上，这两条查询语句等价，但加锁规则不太一样。看看第二个语句的加锁效果：