Notes

Exploring note

0%

加锁规则

发表于 更新于 分类于
本文字数： 5k 阅读时长 ≈ 8 分钟
next-key lock加锁规则

MySQL新的版本可能会改变加锁策略，当前规则适用于5.x系列<=5.7.24,8.0系列<=8.0.13。

执行update和delete的时候，要“先读后写”，这个读就开始加锁了。
insert的时候要有插入意向锁(跟gap lock冲突)。
查询过程中，在 InnoDB 要去找“第一个值”的时候，是按照等值去找的，用的是等值判断的规则；找到第一个值以后，要在索引内找“下一个值”，对应于规则中说的范围查找。

加锁规则

包含两个“原则”、两个“优化”和一个“bug”：

  1. 原则1：加锁的基本单位是next-key lock(前开后闭区间)。
  2. 原则2：查找过程中访问到的对象才会加锁(查询过程中访问到的行和间隙，不是查询结果)。
  3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁(若存在等于该值的行则退化为行锁，若不存在则不退化，参考优化2形成间隙锁)。
  4. 优化2：索引上的等值查询，(无论是唯一索引还是不唯一的普通索引)向右遍历到最后一个不满足等值条件的值，next-key lock退化为间隙锁。
  5. 一个bug：唯一索引上的范围查询也会访问到不满足条件的第一个值为止。 
1
2
3
4
5
6
7
8
9
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);

1. 唯一索引等值查询

等值条件操作间隙：

sessionA sessionB sessionC
begin;
update t set d=d+1 where id=7;
insert into t values(8,8,8);
(block)
update t set d=d+1 where id=10;
(Query OK)

表t中没有id=7的记录：

  1. 根据原则1，加锁单位是next-key lock，sessionA加锁范围是(5,10]；
  2. 根据优化2，这是一个等值查询id=7，而id=10不满足查询条件，next-key lock退化成间隙锁，最终加锁范围是(5,10)。

sessionB往间隙中插入id=8的记录会被锁住，sessionC修改id=10这行可以。

2. 非唯一索引等值查询

覆盖索引上的锁：

sessionA sessionB sessionC
begin;
select id from t where c=5 lock in share mode;
update t set d=d+1 where id=5;
(Query OK)
insert into t values(7,7,7);
(blocked)

sessionA给非唯一索引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，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，sessionB的update语句可以执行。

sessionC插入c=7的记录，会被sessionA的间隙锁(5,10)锁住。

锁是加在索引上的。由于覆盖索引的优化，本例中lock in share mode只锁了覆盖索引。若为了加读锁避免数据被更新，需要再查询字段中加入索引中不存在的字段，绕过覆盖索引优化；或者使用for update，系统认为接下来要更新数据，会顺便给主键索引上满足条件的行加上行锁。

3.唯一索引范围锁

范围查询：

sessionA sessionB sessionC
begin;
select * from t where id>=10 and id<11 for update;
insert into t values(8,8,8);
(QueryOK)
insert into t values(13,13,13);
(blocked)
update t set d=d+1 where id=15;
(blocked)
  1. 开始执行时，要找到第一个id=10的行(等值查询)，加上next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。
  2. 范围查找往后继续找(范围查询)，找到id=15这一行停下来，需要加next-key lock(10,15]。

sessionA锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]，综合为[10,15]。sessionB后半部和sessionC被锁住。

4.非唯一索引范围锁

对照案例三：

sessionA sessionB sessionC
begin;
select * from t where c>=10 and c<11 for update;
insert into t values(8,8,8);
(blocked)
update t set d=d+1 where c=15;
(blocked)

sessionA用字段c来判断，加锁规则跟案例三不同处：在第一次用c=10定位记录的时候，索引c加上了(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，不会蜕变为行锁。之后范围查询，扫描到c=15才知道不需要继续往后找了，才停止扫描。

sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock，综合为(5,15]。sessionB被(5,10]堵住，sessionC被(10,15]堵住。

如果sessionA中语句为select * from t where c>=10 and c<=15 for update;加锁范围为(5,20]。

5.唯一索引范围锁bug

sessionA sessionB sessionC
begin;
select * from t where id>10 and id<=15 for update;
update t set d=d+1 where id=20;
(blocked)
insert into t values(16,16,16);
(blocked)

sessionA是一个范围查询，按照原则1，索引id上加(10,15]这个next-key lock，(因为id是唯一键，循环判断到id=15这一行就可以停止了)，而InnoDB会继续往前扫描到第一个不满足条件的行id=20为止。由于是范围扫描，索引id上的(15,20]这个next-key lock会被锁上且没有优化。

sessionB和sessionC被(15,20]锁住。

6.非唯一索引上存在“等值”的例子

insert into t(id,c,d) values(30,10,30);

sessionA sessionB sessionC
begin;
delete from t where c=10;
insert into t values(12,12,12);
(blocked)
update t set d=d+1 where c=15;
(Query OK)

sessionA在遍历的时候，先访问第一个c=10的记录，根据原则1，加的是(c=5,id=5)-(c=10,id=10)这个前开后闭的next-key lock。
sessionA继续向右查找，直到碰到(c=15,id=15)这一行循环才结束。根据优化2，这是一个等值查询，向右查询到了不满足条件的行，(c=10,id=10)-(c=15,id=15)这个前开后闭的next-key lock会退化成(c=5,id=5)-(c=15,id=15)前后都开的间隙锁。总体索引c上的锁范围是(c=5,id=5)-(c=15,id=15)前后都开的区间。

所以sessionB会被堵住，sessionC正常更新。

7.limit语句加锁

对照案例6：

sessionA sessionB
begin;
delete from t where c=10 limit 2;
insert into t values(12,12,12);
(Query OK)

sessionA的delete语句加了limit2，表t中c=10的记录只有两条，加不加limit2，删除效果相同，但加锁效果不同。

delete语句明确加了limit2的限制，在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条了，循环就结束了。索引c上的加锁范围变成了从(c=5,id=5)-(c=10,id=30)这个前开后闭的区间。

(c=10,id=30)-(c=15,id=15)之间是没有锁的。sessionB没有被堵住。在删除数据的时候尽量加limit。不仅可以控制删除数据的条数，让操作更安全，还可以减少加锁的范围。

8.一个死锁的例子

next-key lock实际上是间隙锁和行锁加起来的结果：

sessionA sessionB
begin;
select id from t where c=10 lock in share mode;
update t set d=d+1 where c=10;
(blocked)
insert into t values(8,8,8);
ERROR 1213(40001): Deadlock found when trying to get lock;try restarting transaction;
  1. sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]（非唯一索引不会退化成行锁）和间隙锁(10,15)（优化2，等值查询优化后成为间隙锁）。
  2. sessionB的update语句先要在索引c上加next-key lock(5,10]，进入锁等待。
  3. sessionA要在插入(8,8,8)这一行，被sessionB的间隙锁锁住。出现了死锁，InnoDB让sessionB回滚。

sessionB的next-key lock(5,10]操作，实际上分成了两步：先是加(5,10)的间隙锁，加锁成功(间隙锁之间不互斥，只保护间隙，不让insert)；然后加c=10的行锁，加锁失败(行锁之间互斥)等待。若成功后面还要依次加(10,15]的锁。

9.倒序

sessionA sessionB
begin;
select * from t where c>=15 and c<=20 order by c desc lock in share mode;
insert into t values(6,6,6);
(blocked)
  1. 由于是order by desc，第一个要定位的是索引c上“最右边的”c=20的行，所以会加上间隙锁(20,25)和next-key lock(15,20]。
  2. 在索引c上向左遍历，要扫描到c=10才停下来(扫描到10，才知道已经不等于15了，找到第一个不等于15的值)，所以next-key lock会加到(5,10]，这是阻塞sessionB的insert语句的原因。
  3. 在扫描过程中，c=20,c=15,c=10这三行都存在值，由于是select *，所以会在主键id上加三个行锁。

sessionA的select语句锁的范围是：

  1. 索引c上(5,25)；
  2. 主键索引上id=15、20两个行锁。

其他

可重复读隔离级别。遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放。next-key lock实际上是由间隙锁加行锁实现的。如果切换到读提交隔离级别，相当于去掉间隙锁的部分，只保留行锁部分。

读提交隔离级别下的优化：语句执行过程中加上的行锁，在语句执行完成后，就把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。读提交隔离级别下，锁的范围更小，锁的时间更短。

读提交隔离级别下，update语句有一个“semi-consistent” read优化(delete语句无效)：如果update语句碰到一个已经被锁了的行，会读入最新的版本，然后判断一下是不是满足查询条件：

  • 不满足，直接跳过；
  • 满足，进入锁等待。