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
yibo2018
V2EX  ›  MySQL

Mysql 死锁问题,原因探讨

  •  
  •   yibo2018 · 2021-12-31 15:28:08 +08:00 · 2505 次点击
    这是一个创建于 818 天前的主题,其中的信息可能已经有所发展或是发生改变。
    # InnoDB , RR 事务隔离级别
    # t2 表的 pulbish_id 为非唯一索引
    # S1 语句:
    
    INSERT INTO `t1` (contract_no...)
    SELECT 'A10-SJZ-21017' ...
    FROM DUAL
    WHERE NOT EXISTS(SELECT *
                     FROM `t2` tmrd
                     where publish_id = '1123424214');  #这里的 publish_id 一定不存在,所以会产生全表锁
    
    
    # S2 语句:
    INSERT INTO t2
    
    (publish_id...)
    values ('1123424214'...)
    

    目前的问题是:

    1. 事务一运行 S1 未提交,事务二运行 S2 会阻塞
    2. 事务一运行 S1 未提交,事务二运行对 t2 表的 for update /lock in share model不会阻塞
    3. 事务一运行 S1 未提交,事务二运行 S1 ,S2 (阻塞),事务一运行 S2 ,产生死锁异常 [40001][1213] Deadlock found when trying to get lock; try restarting transaction
    19 条回复    2022-01-05 09:58:00 +08:00
    yibo2018
        1
    yibo2018  
    OP
       2021-12-31 15:31:19 +08:00
    问题一:
    事务一种 S1 会在 t2 表产生共享锁,事务二 insert t2 首先会插入意向写锁,所以阻塞

    问题二:
    事务一运行 S1 同上,所以事务二运行 t2 的 for update / lock in share model 应该会阻塞!但是并没有阻塞,和问题一的答案相悖

    问题三:
    如果问题一回答的是正确的,那么是可以解释的
    yibo2018
        2
    yibo2018  
    OP
       2021-12-31 15:39:40 +08:00
    翻阅了大量的资料,让我头越来越大,确丝毫用不到自己的问题中,学以致用对我来说真难,我好菜
    orzwalker111
        3
    orzwalker111  
       2021-12-31 16:01:11 +08:00
    个人理解:
    1.s1 事务未提交,因为间隙锁,事务 2 执行 s2 阻塞,正确
    2.s1 select 添加悲观锁( x/s ),命中覆盖索引 publish_id ,加锁范围(xx, 1123424214],下一个锁范围是(1123424214, xxx],继续向右遍历加 next-key lock ,因为最右值 xxx 不等于 1123424214 ,这个锁回退化成间隙锁(1123424214,xxx);加锁结束后,事务 2 执行 s2 ,inset 时,publish_id= 1123424214 不在锁范围,所以写入成功,正确
    orzwalker111
        4
    orzwalker111  
       2021-12-31 16:04:41 +08:00
    --(xx, 1123424214]这个回退化成间隙锁(xx, 1123424214)
    Feiex
        5
    Feiex  
       2021-12-31 16:12:51 +08:00
    INSERT INTO T SELECT……FROM S WHERE……
    对于被插入到表 T 中的元组,在其对应的索引项上施加排他记录锁
    《数据库事务处理的艺术》
    yibo2018
        6
    yibo2018  
    OP
       2021-12-31 16:49:29 +08:00
    @orzwalker111 哇塞分析的很详细

    问题 2 中
    事务一运行 S1 ,如果 publish_id 没有命中的话,会产生全表的间隙锁吧?(自问自答)也不其然,有一个 semi-consistent read 机制,对于不满足查询条件的记录,MySQL 会提前释放,同时不加 GAP 锁,就和你说的退化一样。
    但是对于事务 2 的 select ... where publish_id = 1123424214 for update 也是针对全表的排它锁,如果他也退化,至少也要对 1123424214 左右进行 GAP ,但是目前看下来也没有(没阻塞)
    yibo2018
        7
    yibo2018  
    OP
       2021-12-31 16:52:00 +08:00
    @Feiex 这句话我理解的就是,会在 t 表上加 X 锁。然后呢?
    ozipin
        8
    ozipin  
       2021-12-31 17:10:54 +08:00
    产生锁是和索引有关的,有索引应该是间隙锁。然后 s1 加的应该是读锁吧,所以 s2 for update 不会阻塞,读写之间才会阻塞
    yibo2018
        9
    yibo2018  
    OP
       2021-12-31 17:20:43 +08:00
    @ozipin 不对哦,我可以试试
    我简单的测试了下
    事务一
    SELECT *
    FROM `t2` tmrd
    where publish_id = '1123424214' lock in share mode

    事务二
    SELECT *
    FROM `t2` tmrd
    where publish_id = '1123424214' for update

    事务二阻塞了

    换句话说 for update 加的是 X 锁(排它锁)也就是写锁
    Feiex
        10
    Feiex  
       2021-12-31 19:52:42 +08:00
    @yibo2018 #5 ,在 S 表的索引项加的 s 锁;
    你的过程里事务二的 s2 要申请 t2 表的 ix 锁,就需要事务一的 s1 先释放 t2 表的 s 锁;然后事务一又想申请 t2 表的 ix 锁,就成环了
    yibo2018
        11
    yibo2018  
    OP
       2022-01-04 10:08:11 +08:00
    @Feiex 嗯,问题三的死锁问题是可以解释的

    但是问题 2 就让我很困惑,对 t2 的共享锁( S1 语句运行)和排它锁(对 t2 进行 for update )应该是互斥。但其实没有互斥锁。
    写着写着突然发现答案了:
    那么原因只有一个就是:俩个上锁的地方没有重合!
    即便是全表加锁,也会因为 MySQL 自己的优化机制退化。
    为了证实上述的点,我选择了一个已经有的条件,会上间隙锁+定向锁,结果是阻塞了

    至此我提出来的 3 个问题就解决了,感谢大家
    YIERIC
        12
    YIERIC  
       2022-01-04 14:40:31 +08:00
    @yibo2018 请教一下,“事务一运行 S1 未提交,事务二运行 S2 会阻塞”,为什么“事务一种 S1 会在 t2 表产生共享锁”?
    YIERIC
        13
    YIERIC  
       2022-01-04 14:42:20 +08:00
    @YIERIC 因为既没有 for update / lock in share model 也没有数据更新,所以我不明白为什么会在 t2 上有共享锁
    yibo2018
        14
    yibo2018  
    OP
       2022-01-04 16:11:07 +08:00
    @YIERIC 我的理解是 s1 (insert ... exists (select ...)) 这个形式会对 select 的内容上共享锁
    yibo2018
        15
    yibo2018  
    OP
       2022-01-04 16:12:34 +08:00
    @YIERIC 对的,我也很纳闷,这个结论可以说是由问题一反证回来的
    YIERIC
        16
    YIERIC  
       2022-01-04 18:14:49 +08:00
    @yibo2018 我的理解是这样的:
    1 、事务一持有 t2 间隙锁,事务二插入意向锁被间隙锁阻塞
    2 、事务一持有 t2 间隙锁,事务二同样间隙锁,不阻塞
    3 、事务一持有 t2 间隙锁,事务二运行 S1 不阻塞(同 2 ),运行 S2 阻塞(同 1 ,插入意向锁被间隙锁阻塞),事务一运行 S2 阻塞(插入意向锁被间隙锁阻塞),彼此的插入意向锁在等对方的间隙锁,所以死锁了
    YIERIC
        17
    YIERIC  
       2022-01-04 18:18:43 +08:00
    @yibo2018 总结一下,我的观点有两个关键点:1 、S1 间隙锁; 2 、间隙锁不互斥
    YIERIC
        18
    YIERIC  
       2022-01-04 18:30:53 +08:00
    再补充一句,是间隙锁不互斥,而不是 next-lock
    yibo2018
        19
    yibo2018  
    OP
       2022-01-05 09:58:00 +08:00
    @YIERIC 哇塞,感谢大佬,很清晰
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1278 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 23:28 · PVG 07:28 · LAX 16:28 · JFK 19:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.