MySQL gap锁(next-key lock)

mysql在可重复读(Repeatable Read)隔离级别下,会产生间隙锁(gap lock),其他隔离级别不会产生这个问题。

问题描述

  • 表初始化
CREATE TABLE z (
  id INT PRIMARY KEY AUTO_INCREMENT,
  b  INT,
  KEY b(b)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO z (id, b)
VALUES (1, 2),
  (3, 4),
  (5, 6),
  (7, 8),
  (9, 10);
  • session A
BEGIN;
SELECT *
FROM z
WHERE b = 6 FOR UPDATE;
  • session B
INSERT INTO z VALUES (2, 4);/*success*/
INSERT INTO z VALUES (2, 8);/*blocked*/
INSERT INTO z VALUES (4, 4);/*blocked*/
INSERT INTO z VALUES (4, 8);/*blocked*/
INSERT INTO z VALUES (8, 4);/*blocked*/
INSERT INTO z VALUES (8, 8);/*success*/
INSERT INTO z VALUES (0, 4);/*blocked*/
INSERT INTO z VALUES (-1, 4);/*success*/

分别执行 session B中的insert 会出现上述情况,为什么?

加锁过程

在索引 b 上的等值查询,给索引 b 加上了 next-key lock (4, 6];索引向右遍历,且最后一个值不满足条件时退化为间隙锁;所以会再加上间隙锁 (6,8);所以索引 b 上的 next-key lock 的范围是(b=4,id=3)到(b=6,id=5)这个左开右闭区间和(b=6,id=5)到(b=8,id=7)这个开区间
for update 会给 b = 6 这一行加上行锁;因此 (b=6,id=5) 这一行上有行锁

  • 这么看来上述语句都不在锁的范围内,为什么会被锁

这个问题其实是因为没有理解索引的结构,所以认为所有值都不应该被锁

  • 如图所示,此时索引 b 上的锁:

索引 b 锁范围.png

图中绿色部分即为被锁范围;索引会根据 b 和 id 的值进行排序,插入不同的值,锁的范围是不一样的;分别插入 (b=4,id=2) 和(b=4,id=4),插入的位置如图所示:

(2,4)&(4,4)索引 b 锁范围.png

  • 因为索引是有序的,此时,由于记录(b=4,id=3)的存在,(b=4,id=2)不在锁的范围内,可以插入,但(b=4,id=4)在锁的范围内,所以插入时需要等待锁释放,被 blocked
  • 对于其他(id,b)的值(2,8),(4,8),(8,4),(8,8)也是同样的道理;因此,得出以下结论:
    • id <= 2 时,b 索引锁范围为 (4,8]
    • 2 < id < 8 时,b 索引锁范围为 [4,8]
    • a >= 8 时,b 索引锁范围为 [4,8)

(2,8),(4,8),(8,4),(8,8)索引 b 锁范围.png

  • 但是,实践发现,当 id=0 时,被锁的范围为 [4,8),这和我们得到的第一个结论(4,8]不一样;此时分析得到的示意图为:

(0,4),(0,8)索引 b 锁范围.png

  • 在 session A 中尝试插入 (b=4, id=0)并查询结果:
INSERT INTO z
VALUES (0, 4);

SELECT *
FROM z;

此时,发现表中并没有发现 (b=4, id=0)这条记录,但是多了 (b=4,id=10)这条;所以插入 (b=4, id=0)的时候真正插入的是 (b=4,id=10)这个值;这是因为我们在创建表的时候指定主键 id 的值 AUTO INCREMENT,当插入的主键值为0的时候,会被替换为 AUTO_INCREMENT的值,即10
(0,4)插入后查询结果.png

对此,MySQL 官方文档中的解释是:在非 NO_AUTO_VALUE_ON_ZERO模式下,给自增的列赋值为 0,都会被替换为自增序列的下一个值;当该自增列值指定 NOT NULL 时赋值 NULL,也会被替换;当插入其他值时,自增序列的值会被替换为当前列中最大值的下一个值;参考 MySQL 8.0 Reference Manual 文档,Tutorial 的 Examples of Common Queries , 3.6.9 Using AUTO_INCREMENT

  • 如果将主键修改为不自增,插入 (b=4, id=0) 会得到这条记录
  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值