V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
jiangcheng97
V2EX  ›  程序员

关于 MySQL Gap Lock 和 Next-Key Lock 的一个问题

  •  
  •   jiangcheng97 · 2023-02-20 09:50:40 +08:00 · 1854 次点击
    这是一个创建于 402 天前的主题,其中的信息可能已经有所发展或是发生改变。

    MySql 的隔离级别是可重复读

    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);
    
    sessionA sessionB
    begin;
    select * from t where c >=15 and c<=20 order by c desc for update;
    insert into t (6,6,6);

    为什么 sessionB 会阻塞? 为什么在索引 c 上向左遍历,要扫描到 c=10 才停下来,next-key lock 会加到(5,10]? 求解答

    19 条回复    2023-03-10 11:31:44 +08:00
    PythonYXY
        1
    PythonYXY  
       2023-02-20 11:10:52 +08:00
    c 是非唯一索引,MySQL 会向左遍历到第一个不满足条件的值,然后在这个值上加 next-key lock
    jiangcheng97
        2
    jiangcheng97  
    OP
       2023-02-20 11:26:24 +08:00
    @PythonYXY 如果按照这个逻辑,向右遍历,到 25 停下来,应该加(20,25]和(25,+00)的间隙锁吧,但是并没有加(25,+00)的间隙锁。还是我理解上有问题呢...
    RedisMasterNode
        3
    RedisMasterNode  
       2023-02-20 11:51:50 +08:00
    复现成功插个眼蹲一手答案,另外几个测试 case:
    1. insert into t VALUES (6,5,6); -- 阻塞
    2. insert into t VALUES (6,4,6); -- 执行

    不靠谱猜测:
    1. 阻塞肯定是因为锁定区域有重叠;
    2. 既然重叠那肯定是猜测 session A 锁定了 [10, 15] 这部分,session B 锁定了 [5, 10] 的这部分(边界是开区间闭区间暂且不进行确认,但是必然是有重叠区域的,例如这里的猜测 [10])。

    其他的提示信息:
    1. Explain 结果显示 session A 的查询使用了 Backward index scan ,提示这里对 idx_c 的使用是反向的,因此 15 的 Next-Key 是 10 (可能)没错。

    蹲一手答案。
    PythonYXY
        4
    PythonYXY  
       2023-02-20 12:19:52 +08:00
    @jiangcheng97 25 对应的间隙锁就是(20,25),(25,+00)这个间隙锁对应的是 supremum 。加间隙锁要看对应的哪个 key 。
    mercurius
        5
    mercurius  
       2023-02-20 12:50:13 +08:00
    https://s2.loli.net/2023/02/20/nj27OqbSkJmsVAi.png
    应该就跟 3 楼说的一样,因为是 Backward index scan 所以找到第一个不满足条件的不是 25 ,而是 10 (个人猜测因为是倒序的,所以这里的间隙锁应该为 (10,5] ,前开后闭区间),把排序去掉后间隙锁就是 (20,25] 了
    Backward index scan 是 MySQL8.0 后才出现的,可以用 5.7 版本试试会不会一样的结果
    NeroKamin
        6
    NeroKamin  
       2023-02-20 14:23:57 +08:00
    8.0 版本的 MySQL 有 Backward index scan ,所以 c 上加锁的情况应该是(20,25)、(15,20]、(10,15]、(5,10],主键锁住记录 15 和 20
    jiangcheng97
        7
    jiangcheng97  
    OP
       2023-02-20 15:07:16 +08:00
    锁住(10.15],(15,20],(20.25]我可以理解,我其实不太理解的是为什么 desc 排序会锁住(5,10]这个区间;
    而在正常的 asc 排序中,也只比 desc 少了(5,10]这个区间
    wueryi
        8
    wueryi  
       2023-02-20 16:53:42 +08:00
    chatgpt 说:
    wueryi
        9
    wueryi  
       2023-02-20 16:53:51 +08:00
    chatgpt 说:Session B 阻塞是因为 Session A 在执行 select 时设置了 FOR UPDATE ,这意味着它会在表中加一个 next-key lock ,否则会导致读取到已经被修改的数据。

    向左遍历时,这个 next-key lock 会加到 (5,10] 上,是因为在主键 id 上,next-key lock 是一个包含前一行和本行的范围,而且根据索引 c ,可以看出 c=10 是最后一行满足条件的数据,因此会在 c=10 的位置停止遍历。
    lazyfighter
        10
    lazyfighter  
       2023-02-20 17:07:30 +08:00
    我认为 sessionB 不会阻塞
    initObject
        11
    initObject  
       2023-02-20 17:41:50 +08:00   ❤️ 3
    在索引遍历的过程上进行加锁
    索引搜索指的是就是:
    在索引树上利用树搜索快速定位找到第一个值
    然后向左或向右遍历
    order by desc 就是用最大的值来找第一个
    order by asc 就是用最小的值来找第一个

    因为 order by id desc 所以首先在普通索引找到 c=20 的第一条记录
    在 c=20 加上 next-key 锁 (15,20]
    因为是普通索引 引擎认为可能存在不止一条的 c=20 的记录 因此向右遍历找到第一条不符合条件的记录 c=25 加上间隙锁( 20,25 )
    然后 开始在索引上向左遍历扫描 扫描过程中 记录 c=15 符合条件 加上 next-key 锁 (10,15]
    可能存在不止一条 c=15 的记录 继续向左扫描 得到记录 c=10 (第一个不符合条件 c>=15 停止遍历) 加上 next-key 锁 ( 5,10]

    因为没有使用覆盖索引 在 c=15,c=20 对应行记录加上 主键的 行锁
    initObject
        12
    initObject  
       2023-02-20 18:08:38 +08:00   ❤️ 1
    正常的 asc 排序中 用最小的值来找第一个
    所以首先找到 c=15 的第一条记录
    在 c=15 加上 next-key 锁 ( 10,15]
    接着向右遍历 找到 c=20 的记录 满足条件 c<=20 在 c=20 加上 next-key 锁( 15,20]
    因为是普通索引 引擎认为可能存在不止一条的 c=20 的记录 因此接着向右遍历 找到 c=25 的记录 第一个不满足条件 停止遍历 在 c=25 加上 next-key 锁( 20,25] 因为优化规则 优化为间隙锁 ( 20,25 )

    加锁的顺序其实就是索引的遍历顺序 遍历到的记录或者区间都要加锁
    RedisMasterNode
        13
    RedisMasterNode  
       2023-02-20 20:15:04 +08:00
    @lazyfighter 3F 我已经回复过了会阻塞.....
    jiangcheng97
        14
    jiangcheng97  
    OP
       2023-02-20 21:19:09 +08:00
    @initObject 感谢回答,理解了
    UN2758
        15
    UN2758  
       2023-03-07 04:37:13 +08:00
    @initObject ‘( 20,25] 因为优化规则 优化为间隙锁 ( 20,25 )‘,我测试了一下,25 是闭区间啊
    initObject
        16
    initObject  
       2023-03-07 11:59:22 +08:00
    @UN2758 感谢纠正 非唯一索引范围查询 不会优化为间隙锁
    UN2758
        17
    UN2758  
       2023-03-07 16:28:23 +08:00
    @initObject #16 降序查询的时候,比如 where c<=20 的情况下,25 确实又是开区间,我真的好蛋疼
    initObject
        18
    initObject  
       2023-03-10 10:54:13 +08:00
    @UN2758 找第一个值用的是等值查询 接着范围查询 访问到第一个不满足的记录为止
    普通索引等值查询 优化规则 2 向右找到第一个不符合条件的值 退化为间隙锁
    降序查询 等值查询找第一个值为 20 接着向右找到第一个不符合条件(不等于 20 )的值 25 退化为间隙锁 ( 20,25 )接着范围查询(从 20 往左直到 10 )对于访问到的记录加上 next-key 锁
    升序查询 等值查询找第一个值为 15 接着向右找到第一个不符合条件(不等于 15 )的值 20 但是 20 是符合条件<=20 的 所以不会退化为间隙锁( 15,20](也可以理解为先退化为间隙锁, 在之后的范围查询(从 15 往右直到 25 )中 加上了 next-key 锁)

    等值查询之后就是范围查询 普通索引范围查询都加的是 next-key 锁 不会退化为间隙锁
    initObject
        19
    initObject  
       2023-03-10 11:31:44 +08:00
    @UN2758
    select * from t where c >=15 and c<=20 order by c desc for update;
    加锁过程:
    1.等值查询找第一个值 条件为等于 20 找到记录 20 加上 next-key 锁 ( 15,20]
    2.向右找到第一个不满足条件的记录 25 加上 next-key 锁 然后退化为间隙锁 (20,25)
    3.接着从第一步找的记录 20 开始向左范围查询(有待考证 有大神可以查查源码看下这一步是从第二步得到的记录 25 还是第一步得到的记录 20 开始) 条件为 c >=15 and c<=20 直到第一个不满足条件的记录 10 访问到的数据都加 next-key 锁 依次在 15 ,10 ,加上 next-key 锁
    综上就是 (5,10] (10,15] (15,20] (20,25)

    select * from t where c >=15 and c<=20 order by c asc for update;
    加锁过程:
    1.等值查询找到第一个条件为等于 15 找到记录 15 加上 next-key 锁 (10,15]
    2.向右找到第一个不满足条件的记录 20 加上 next-key 锁 然后退化为间隙锁 (15,20)
    3.接着从第一步找的记录 15 开始向右范围查询 条件为 c >=15 and c<=20 直到第一个不满足条件的记录 25 访问到的数据都加 next-key 锁 依次在 20 ,25 加上 next-key 锁
    综上就是 (10,15] (15,20] (20,25]
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1004 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 114ms · UTC 19:48 · PVG 03:48 · LAX 12:48 · JFK 15:48
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.