十四、mysql改一条记录却锁住多行数据的原因

目录

加锁规则的两个“原则”、两个“优化”和一个“bug”

案例一：等值查询间隙锁

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

案例三：主键索引范围锁

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

案例五：唯一索引范围锁bug

案例六、非唯一索引上 存在“等值”的例子

案例七：limit语句加锁

案例八：一个死锁的例子

总结

---

这里需要明确间隙锁和next-key lock的概念及加锁规则。

加锁规则有以下两条前提说明：

1、MySQL后面的版本可能会改变加锁策略，以下规则限于5.x系列<=5.7.24,8.0系列<=8.0.13.

2、间隙锁在可重复读隔离级别下才有效。

---

加锁规则的两个“原则”、两个“优化”和一个“bug”

1、原则1：加锁的基本单位是next-key lock（前开后闭区间）；

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

3、优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁；

4、优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁；

5、一个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);

 

1、根据原则1，加锁单位是next-key lock，session A加锁范围就是（5，10]；

2、同时根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是（5，10）。

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

---

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

 

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

1、根据原则1，加锁单位是next-key lock，因此会给（0，5]加上next-key lock；

2、c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给（5，10】加next-key lock。

3、同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁（5，10）.

4、根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么session B的update语句可以执行完成。

但session C要插入一个（7，7，7）的记录，就会被session A的间隙锁（5，10）锁住。

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

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

---

案例三：主键索引范围锁

关于范围查询

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

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

在逻辑上，这两条查询语句肯定是等价的，但是加锁规则不太一样 。

 

1、开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock（5,10]。根据优化1，主键id 上的等值条件，退化成行锁，只加了id=10这一行的行锁。

2、范围查找就往后继续找，找到id=15这一行停下来，因此 需要加next-key lock（10，15].

所以，session A 这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15].这样，session B和session C的结果就能理解了。

这里需要注意一点，首次session A定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。

---

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

 

这次session A 用字段c来判断，加锁规则和案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加了(5,10]这个next-key lock后，由于索引c 是非唯一索引，没有优化规则，也就是不会蜕变为行锁，因此最终session A加的锁死，索引上的(5,10]和(10,15]这两个next-key lock。

所以从结果上看，session B要插入(8,8,8)的这个insert 语句时就被堵住了。

这里需要扫描到c=15才停止扫描，是合理的，因为InnoDB要扫到c=15，才知道不需要继续往后找了。

---

案例五：唯一索引范围锁bug

 

---

案例六、非唯一索引上 存在“等值”的例子

 

 

 

 

---

案例七：limit语句加锁

 

 

---

案例八：一个死锁的例子

 

---

总结