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

求教 MySQL 加锁的一个疑问

  •  
  •   gps32251070 · 2023-04-17 18:27:29 +08:00 · 1869 次点击
    这是一个创建于 600 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构如下:

    create table t
    (
        id int not null
            primary key,
        c  int null,
        d  int null
    );
    
    create index c
        on t (c);
        
    insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
    

    mysql 版本:8.0.32 隔离级别 RR

    session A session B
    begin;
    select * from t where id>=15 and id<=20 order by id desc for update ;
    insert into t values (22,22,22); //会被阻塞
    commit;

    请问,id 是主键,为什么 session B 会进入锁等待?按理说唯一索引不会检查区间(20, 25)吧

    23 条回复    2023-04-18 20:06:23 +08:00
    jimmzhou
        1
    jimmzhou  
       2023-04-17 18:46:46 +08:00
    索引 c 上面的加锁范围应该是 (5,25)
    gps32251070
        2
    gps32251070  
    OP
       2023-04-17 18:55:12 +08:00
    @jimmzhou 应该跟索引 c 无关吧,我删除了索引 c ,还是会阻塞
    yyyfor
        3
    yyyfor  
       2023-04-17 19:24:14 +08:00
    索引范围查询需要访问到不满足条件的第一个值为止,这里是等值查询,感觉这里主键 id 加锁的范围为(10,25]
    phpcyy
        4
    phpcyy  
       2023-04-17 19:30:00 +08:00
    @yyyfor 👍,我按照 OP 的表新插入了一条 (21,21,21),session B 就不会阻塞了
    gps32251070
        5
    gps32251070  
    OP
       2023-04-17 19:32:36 +08:00 via iPhone
    @yyyfor 不是,测试的主键加锁范围是(5,25)
    zhzy0077
        6
    zhzy0077  
       2023-04-17 19:34:04 +08:00
    TABLE,IX,GRANTED,
    RECORD,"X,GAP",GRANTED,20
    RECORD,X,GRANTED,10
    RECORD,X,GRANTED,15

    这里大概还有一个 20 的 next key lock, 虽然直觉上 id 是 PK 的话应该不用这个锁,可能是为了兼容 PK 有多个字段的情况,所以把等值的时候也 lock 起来了

    不过现在 MySQL 不是也早就有 Snapshot Isolation 了吗,真有必要 for update 这样写嘛
    admol
        7
    admol  
       2023-04-17 19:39:29 +08:00
    改成 RC 级别试试?
    jaggle
        8
    jaggle  
       2023-04-17 19:53:12 +08:00 via iPhone   ❤️ 1
    这题目我会,谷歌搜索 “间隙锁”
    tedzhou1221
        9
    tedzhou1221  
       2023-04-18 09:22:50 +08:00
    @jaggle 谢谢你的抢答,直接搜索到答案了,哈哈
    yc8332
        10
    yc8332  
       2023-04-18 09:23:48 +08:00
    mysql innodb 加范围锁的时候是会这样的。就算不存在的记录也会被加锁。。叫间隙锁
    liudaolunhuibl
        11
    liudaolunhuibl  
       2023-04-18 09:57:54 +08:00
    @yyyfor 说的对,但是原因就是因为间隙锁,上锁范围是( 15 ,20],(20,25],20 之后由于有数据的就是 25 ,所以间隙锁会从 20 一直锁到 25
    liudaolunhuibl
        12
    liudaolunhuibl  
       2023-04-18 10:04:54 +08:00
    我理解的简单来说,间隙锁的锁定范围应该是锁定数字行到最近一行(左右均会)存在数据之间的数据都会锁,也就说,>=15 的时候锁住的是 10-20 区间,<=20 的时候锁定的是 15-25 的区间,合并一下就可以 10-25 区间都会上锁,楼主可以试试 insert into t values (11,11,11)会不会上锁
    gps32251070
        13
    gps32251070  
    OP
       2023-04-18 10:07:17 +08:00
    @liudaolunhuibl 嗯,我知道间隙锁,如果 id 不是唯一索引,这个间隙锁可以避免 id=20 的值插入进来,所以没问题。我想问的是 id 是唯一索引,锁这个间隙感觉毫无必要。
    gps32251070
        14
    gps32251070  
    OP
       2023-04-18 10:08:38 +08:00
    @liudaolunhuibl 这个 sql 的测试锁定范围是(5,25)
    liudaolunhuibl
        15
    liudaolunhuibl  
       2023-04-18 10:26:33 +08:00
    @gps32251070 啊?不要应该是从 10 开始吗。。不太懂
    xiaohundun
        16
    xiaohundun  
       2023-04-18 10:29:03 +08:00
    大概、可能、也许是因为你用的 RR 隔离,更严格吧
    fengpan567
        17
    fengpan567  
       2023-04-18 15:12:42 +08:00
    你把 order by 去掉应该就正常了,倒叙查第一个查到的数就是 25
    wtfedc
        18
    wtfedc  
       2023-04-18 16:02:36 +08:00
    是 order by 引起的问题,但是没查到为什么可以导致 Next-Key Locks 范围变化
    PythonYXY
        19
    PythonYXY  
       2023-04-18 16:32:30 +08:00
    确实好奇怪啊,我把 order by 去掉也会阻塞
    initObject
        20
    initObject  
       2023-04-18 18:56:34 +08:00
    SachinBeyond
        21
    SachinBeyond  
       2023-04-18 19:38:28 +08:00
    mysql45 讲 第 21 篇里面提到的第 5 点已经 讲了
    https://blog.csdn.net/bohu83/article/details/105344930

    一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

    加锁的基本单位是 next-key lock,
    (10,15],(15,20] ,(20,25] 被命中的就是 (10,15],(15,20]

    因为这个 id 是主键唯一索引, 这个(15,20] next-key lock 就 i 已经锁住了 id=20 的数据,且在这条数据之后不会有 id=20 数据了(主键唯一性保证),所以理论上( 20 ,25] 可以不用加锁,但是实际上 mysql 会在( 20 ,25]上加锁,又因为 25 这条数据本身没有被命中,所以会退化为( 20 ,25 )的间隙锁。

    至于 mysql 为什么会加锁,林哥说这可能是一个 feature
    gps32251070
        22
    gps32251070  
    OP
       2023-04-18 19:58:23 +08:00 via iPhone
    @SachinBeyond 意思是这有可能是个 BUG ?还有,这个左边锁的范围是到 5 ,整体是(5,25)
    SachinBeyond
        23
    SachinBeyond  
       2023-04-18 20:06:23 +08:00
    @gps32251070 ( 5 ,10] 这个被锁住我没想到。我试了下确认是被锁住了,整体范围是(5,25)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2291 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 05:40 · PVG 13:40 · LAX 21:40 · JFK 00:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.