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

HigginCui
125 阅读7分钟

间隙锁在可重复读隔离级别下才有效，所以本篇文章默认都是可重复读隔离级别。

间隙锁加锁规则里面，包含 2个"原则"+ 2个“优化”+ 1个“bug”：

  • 原则1：加锁的基本单位是next-key lock(间隙锁+行锁)，是前开后闭的。
  • 原则2：查找过程中访问到的对象都会加锁。
  • 优化1：索引上的等值查询，给唯一索引（如主键索引）加锁的时候，next-key lock退化为行锁。
  • 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  • 1个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

创建一张表，并插入6个数据。

-- 除了主键索引外，还有索引c
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;

插入6条数据

insert into t values
(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);

案例一：等值查询间隙锁

image.png

  1. 根据原则1，加锁的单位是next-key lock，session A 加锁的范围是(5,10]。
  2. 根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，那么next-key lock会退化为间隙锁，最终加锁范围为(5,10)。
  3. 所以Session B要往间隙里面插入(8,8,8)会被阻塞，而session C修改id=10是可以的。

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

这个例子是关于覆盖索引上的锁： image.png

  1. sessionA要给索引c上c=5这一行加读锁，根据原则1，加锁的单位是next-key lock，因此会给(0,5]加上next-lock key。
  2. 而c是普通索引，因此仅访问c=5这一行是不能停下来的，需要向右遍历，直到c=10才会放弃。根据原则2，访问到的都要加锁，因此也要给(5,10]也加上next-lock key。
  3. 同时根据优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成(5,10)。
  4. 同样根据原则2，只有访问到的对象才会加锁，这个查询用的覆盖索引，不需要访问主键索引，所以此时主键上并没有加任何锁，所以基于主键查询的sessionB可以正常执行。
  5. session C要插入一个(7,7,7)会被sessionA的间隙锁(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。

案例三：主键索引范围锁

下面两个查询语句，加锁范围是否相同？

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

image.png

  1. sessionA执行时，要找到第一个id=10的行，因此本该是next-key lock加锁范围(5,10]，而id是主键索引，根据优化1，主键id上的等值条件，会退化成行锁，因此只加了id=10这一行的行锁。
  2. 范围查询会继续往后找，找到id=15这一行停下来，因此要加next-lock(10,15]。
  3. session A最终锁的范围是行锁id=10和next-key lock(10,15]。
  4. 所以sessionB部分被阻塞，sessoinC被阻塞。

sessionA定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。

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

来看下两个非唯一索引的范围查询的案例： image.png

  1. sessionA在第一次用c=10定位记录的时候，索引c上加了next-key lock锁(5,10]，因为索引c是非唯一索引，所以没有优化规则。
  2. 最终sessionA加的锁时 (5,10]和(10,15]这两个next-key lock。

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

image.png

  • sessonA是一个范围查询，按照原则1的话，应该是索引id是只加(10,15]这个next-key lock，而id是唯一键，循环应该判断到id=15这一行就应该停止了。
  • 实际上，InnoDB会往前扫描到第一个不满足条件的行才停止，也就是id=20，由于是范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上。
  • sessionB要更新id=20这一行，是会被锁住的，同样的，sessionC要插入id=16这一行，也会被锁住。

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

给表t再插入一条新纪录

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

新插入的这一行c=10,也就是说现在表里有2个c=10的行(10,10,10)和(30,10,30)。

image.png 虽然有2个c=10，但是它们的主键值id是不同的(分别是10和30)，因此这两个c=10的记录之间，也是有间隙的。

image.png

  • sessionA在遍历的时候，先访问第一个c=10的记录。根据原则1，这里简单是(5,10]这个next-key lock。
  • sessionA向右查找，直到碰到(c=15,id=15)这一行，循环才结束，根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(10,15)的间隙锁

所以delete语句在索引c的加锁范围，如下所示：

image.png 蓝色区域两边都是虚线，表示开区间，即c的加锁范围是(5,15)。

案例七：limit语句加锁

image.png 接上面，表t里面c=10的记录实际有2条，因此加不加limit 2，删除效果是一样的，但是加锁的效果是不同的。

  • sessionA的delete语句明确加了 limit 2限制，此时满足c=10的语句也只有2条，因此在遍历到(c=10,d=30)这一行后，满足条件的语句已经有2条，循环就结束了。
  • 索引c上的加锁范围变成了从(c=5,id=10)到(c=10,id=30)这个前开后闭区间，如下所示： image.png (c=10,id=30)之后的这个间隙并没有在加锁范围里，因此insert语句插入c=12是可以执行成功的。
  • 在删除数据的回收尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

image.png

  1. session A启动事务后执行查询语句加 lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15);
  2. sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待
  3. session A要再插入一条(8,8,8)，被sessionB的间隙锁锁住，出现了死锁，InnoDB让session B回滚。

可能会疑惑sessionB的next-key lock不是还没有申请成功吗？

  • sessionB的"加 next-key lock(5,10]"操作，实际上分成了2步，先是加(5,10)的间隙锁，加锁成功，然后加c=10的行锁，这时候才被锁住的。
  • 分析加锁规则的时候可以用next-key lock来分析，具体执行的时候，是要分成间隙锁和行锁2段来执行的。

小结

  • 可重复读隔离级别下，遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放。
  • next-key lock实际上是间隙锁+行锁实现的。
  • 如果切换到读已提交(rc)隔离级别下，即去掉间隙锁，只剩下行锁。读已提交隔离级别下，锁的范围更小，锁的时间更短，不少业务都默认使用读已提交隔离级别的原因。
avatar
文章
阅读
粉丝