V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
misoomang
V2EX  ›  MySQL

求助 MySQL 5.7 默认隔离级别下死锁问题

  •  1
     
  •   misoomang · 91 天前 · 1674 次点击
    这是一个创建于 91 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构:

    CREATE TABLE `student` (
    `id` int NOT NULL,
    `name` varchar(255) DEFAULT NULL,
    `age` int DEFAULT NULL,
    `score` int DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    

    已经有如下数据 (1, 'Alice', 20, 85),

    (8, 'Bob', 22, 92),

    (15, 'Charlie', 21, 78),

    (25, 'David', 23, 95), (33, 'Eva', 20, 88),

    (44, 'Frank', 22, 79),

    (50, 'Grace', 21, 94),

    (55, 'Henry', 23, 87);

    事务 A 执行: time1: update student set score=88 where id=22;

    time3: insert into student(id, name, age, score) value(22, 'John', 28, 88);

    事务 B 执行: time2:update student set score=99 where id=21;

    time4: insert into student(id, name, age, score) value(21, 'John', 28, 99);

    在 time1 时刻事务 A 会加上 next-key lock (15, 25)

    time2:update student set score=99 where id=21; 时刻下为什么不会被阻塞 死锁又是怎么产生的

    第 1 条附言  ·  91 天前
    感谢回复。通过手动执行上述场景的锁持有状况来看,确实是这样的

    time1 时刻,由于 id=22 在表中是没有的,此刻事务 A 会在 (15, 25) 加上间隙锁
    time2 时刻,由于 id=21 在表中也是没有的,且间隙锁之间不是互斥的,所以此刻事务 B 会在 ( 15, 25 )加上间隙锁

    time3 时刻的 insert 语句因为事务 B 间隙锁的存在所以需要等待事务 B 间隙锁释放;
    time4 时刻的 insert 语句因为事务 A 间隙锁的存在所以需要等待事务 A 间隙锁释放;

    从而出现死锁的情况
    8 条回复    2024-02-27 10:57:05 +08:00
    peyppicp
        1
    peyppicp  
       91 天前
    咨询了 gpt:结论如下:

    对于示例中的数据库事务:
    初步执行 time1: update student set score=88 where id=22; 时,因为 id=22 并不存在,所以它会添加一个 Next-key lock ,范围在项目 (15, 25) 上。
    然后 time2: update student set score=99 where id=21; 可以被成功执行,而不会被阻塞,这是因为 InnoDB 使用了 Next-key Locks (即记录锁和间隙锁的组合)。执行这条语句时,id=21 并不存在,不会触发 Next-key Locks 范围 (15, 25) 上的记录锁,并成功执行。但同时,这个语句添加了一个 Next-key lock ,范围在 (21, 25) 上。
    然后在 time3: insert into student(id, name, age, score) value(22, 'John', 28, 88); 语句执行时,因为 id=22 在之前 time2 执行的 Next-key Locks 泛围 (21, 25) 内,所以这条语句会被阻塞。
    同时,在 time4:insert into student(id, name, age, score) value(21, 'John', 28, 99); 中,因为 id=21 在事务 A 的 Next-key Locks 泛围 (15, 25) 内,所以这条语句亦会阻塞。
    最后形成了死锁,两个操作互相等待对方释放资源。
    misoomang
        2
    misoomang  
    OP
       91 天前
    感谢回复。通过手动执行上述场景的锁持有状况来看,确实是这样的

    time1 时刻,由于 id=22 在表中是没有的,此刻事务 A 会在 (15, 25) 加上间隙锁
    time2 时刻,由于 id=21 在表中也是没有的,且间隙锁之间不是互斥的,所以此刻事务 B 会在 ( 15, 25 )加上间隙锁

    time3 时刻的 insert 语句因为事务 B 间隙锁的存在所以需要等待事务 B 间隙锁释放;
    time4 时刻的 insert 语句因为事务 A 间隙锁的存在所以需要等待事务 A 间隙锁释放;

    从而出现死锁的情况
    kousei
        3
    kousei  
       91 天前
    mysql 的锁可以参考 https://www.yuque.com/kousei/kb/snht1u?singleDoc# 《 MySQL 文档翻译——InnoDB 锁》
    waytodelay
        4
    waytodelay  
       91 天前
    是研究还是说生产环境遇到了?
    morgan1freeman
        5
    morgan1freeman  
       91 天前
    https://blog.csdn.net/fedorafrog/article/details/104249140
    参考 何登成 这个,最消息了,各种情况下 加的什么锁
    LeeReamond
        6
    LeeReamond  
       90 天前
    挺有意思的,因为我以前做的业务都用默认隔离级别所以还真没遇到过这种情况。所以意思是 innodb 最高隔离级别下两个线程同时进行邻近区段的 [查询->更新] 操作就大概率导致死锁?那开发要咋做,难道业务端维护锁镜像么
    kanepan19
        7
    kanepan19  
       90 天前
    业务中很少碰到 update 不存在的 id ,都是先确认主键的, 所以没碰到这种间隙锁。
    changdy
        8
    changdy  
       90 天前
    太麻烦了 . 不推荐 可重复读 ..隔离级别.建议 读已提交
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1215 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 18:02 · PVG 02:02 · LAX 11:02 · JFK 14:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.