mysql加锁是加到哪里

转载

编程艺术之光 2024-11-01 13:13:10

文章标签 mysql加锁是加到哪里加锁主键数据 文章分类 MySQL 数据库

注：加锁规则指的是next-key lock，如果还不了解next-key lock，请阅读上一篇博客

加锁规则可以概括为：两个原则、两个优化和一个bug:
原则1:加锁的基本单位是next-key lock，前开后闭
原则2:查找过程中访问到的对象才会加锁
优化1:索引上的等值查询，给唯一索引加锁的时候，next-key lock退化成行锁
优化2:索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
1个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);

接下来来看几个案例：

案例一：等值查询间隙锁

mysql加锁是加到哪里_加锁

由于数据库中没有 id=7这条数据，id又为主键索引，所以根据原则1可得：next-key lock的加锁范围是(5,10]。
SessionB要往这个间隙中插入id=8的数据，会被锁住，而SessionA是一个等值查询（id=7），且SessionCid=10的查询不满足查询条件（7 != 10），根据优化2可得，此时next-key lock退化成间隙锁：(5,10)

因此，SessionB会被阻塞，SessionC不会被阻塞

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

mysql加锁是加到哪里_加锁_02

根据原则1，加锁的单位是next-key lock，因此会(0,5]加上next-key lock
c是普通索引，所以找到c=5这一条数据后，并不会停下，而是继续向右遍历，直到c=10，根据原则2，访问到的都要进行加锁，所以要给(5,10]加上next-key lock
由于优化2，等值判断，向右遍历，最后一个值不满足c=5这个等值条件，所以next-key lock退化成间隙锁(5,10)
根据原则2，访问到的数据才会加锁，又因为SessionB这个查询使用了覆盖索引，只需要访问主键索引，而主键索引并没有加锁，所以SessionB可以执行。（锁是加在索引上的）SessionC要插入的元素位于(5,10)这个间隙锁的范围内，所以会被阻塞。

案例三：主键索引范围锁

mysql加锁是加到哪里_主键_03

SessionA的语句执行时，由于id是主键索引，找到了第一行id=10的数据，所以此时的next-key lock应该为(5,10]，根据优化1，主键id的等值条件，退化成行锁。
往后继续查找，到id=15这一行停下来，因此需要加next-key lock(10,15]
所以此时这个next-key lock的范围就是[10,15]

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

mysql加锁是加到哪里_主键_04

类似于案例3，唯一不同的地方是，在第一次使用c=10定位记录的时候，索引c上加上了(5,10]的next-key lock，这个next-key lock不会退化，所以最终SessionA加的锁是，索引c上的(5,10]和(10,15]这两个next-key lock

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

mysql加锁是加到哪里_主键_05

正常来说，唯一索引的next-key lock应该是(10,15]，但是由于bug1，扫描到15后，还会继续扫描第一个不符合条件的值，因此还会存在一个next-key lock范围是(15,20]，这应该算是一个bug，因为(10,15]的next-key lock已经可以保证不会有范围内的数据插入。