mysql如何加锁 mysql加锁规则

转载

编程小天才 2023-08-21 10:42:12

MYSQL的加锁规则

丁奇老师总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 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如何加锁 mysql加锁规则_加锁

由于等值查询id=7的记录不存在，此时会把包含id=7的这个间隙加上Next-key Lock（范围锁），就是(5,10]。
由于id=10这个记录明显不符合id=7的条件，所以范围锁退化成间隙锁，只锁住（5,10)

非唯一索引等值锁

mysql如何加锁 mysql加锁规则_mysql如何加锁_02

这个例子中，先加上间隙锁（0,5] ，由于非唯一索引，查到5之后还会继续往后查找，所以(5,10]之间不会加锁，又由于c=10明显不符合。所以范围锁退化成（0,9）了。

由于范围锁，间隙锁，行锁等都是加载索引上的，刚好session A的所有操作只涉及到覆盖索引，所以session A的中的锁都是加载覆盖索引上，这就是为何B事务可以成功的原因，因为B操作没有涉及到A的覆盖索引。

这个例子中有点难以理解，按照丁奇老师的课程所说，由于使用了覆盖索引，所有的数据都不用回表操作，所以只要锁住覆盖索引就可以了，主键索引不用管。

所以这个例子中session B的操作并没有改动覆盖索引，所以执行session B操作可以成功。

session C的操作就会被阻塞住，因为C中的操作会涉及到修改覆盖索引，被session A锁住了。

主键索引范围锁

mysql如何加锁 mysql加锁规则_主键_03

现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？
开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。根据优化 >1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
范围查找就往后继续找，找到 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 的时候，用的是范围查询判断。

上面是丁奇老师的课程中说的内容。我就不做自己的阐述了。