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

求分析一个死锁问题以及解决方案

  •  
  •   lry · 2020-10-22 21:50:22 +08:00 · 2826 次点击
    这是一个创建于 1253 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构如下

    CREATE TABLE `system_proxy` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      `ip` varchar(46) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
      `port` int unsigned NOT NULL DEFAULT '0',
      `status` tinyint unsigned NOT NULL DEFAULT '0',
      `holder` int unsigned NOT NULL DEFAULT '0',
      `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `unique_ip_port` (`ip`,`port`) USING BTREE,
      KEY `index_holder_status` (`status`,`holder`) USING BTREE
    ) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
    

    show engine innodb status 详细内容在这里 https://paste.ubuntu.com/p/7ZyjCpTDrX/

    业务上,多线程会并发进行如下两个 sql 的更新操作,观察上面的日志,应该就是造成概率性发生死锁的原因。

    (我不太会看这个日志。。求大腿讲解以及解决方案)

    事务 1 update system_proxy set status = 3 where id = ?

    事务 2 update system_proxy set status = 1, holder = 3 where status = 0 limit 1

    16 条回复    2020-10-23 21:44:14 +08:00
    DoctorCat
        1
    DoctorCat  
       2020-10-22 22:24:40 +08:00
    事务 2 应指定 id
    lry
        2
    lry  
    OP
       2020-10-22 22:39:35 +08:00
    @DoctorCat 这个业务上没法指定 id,本质就是多线程循环运行,每次运行前先占有一个资源。
    这个死锁看起来像是两个事务同时修改了 index_holder_status 这个索引,所以是不是让事务 1 不去更新这个索引就能解决?
    papa2
        3
    papa2  
       2020-10-22 22:54:56 +08:00
    事务 2 的 limit 要配合 order by id 才会精确锁定行,事务 2 每条更新单独开启一个事务,循环去开启事务是不科学的
    DoctorCat
        4
    DoctorCat  
       2020-10-22 23:00:14 +08:00
    @lry 那样是无法解决的。可以先获取需要更新的记录的主键,然后再去分批 update
    by73
        5
    by73  
       2020-10-22 23:15:55 +08:00
    看了下原因,可能的确跟索引有关,第一个线程找到了这一行 A,加上了行锁(索引锁),更新 status,然后获取 index_holder_status 的索引锁准备更新 status 的索引;但是第二个线程根据 index_holder_status 可能也选中了第一个线程里的行 A (猜测 A 原来的 status 为 0 ),然后准备更新这一行的数据,发现行 A 被加了锁,因此产生了死锁。

    当然,我对 MySQL 也不太懂,以上信息都是百度来的(逃

    Ref:
    1. https://dba.stackexchange.com/a/117030
    2. https://zhuanlan.zhihu.com/p/66676020
    geligaoli
        6
    geligaoli  
       2020-10-23 01:10:45 +08:00
    事务 2,既然 limit 1,不如读出这条记录,然后根据 ID 更新。
    beidounanxizi
        7
    beidounanxizi  
       2020-10-23 01:37:25 +08:00
    好好的 clear 代码不写,写个 concurrent code snippet 整活🐶
    beidounanxizi
        8
    beidounanxizi  
       2020-10-23 01:39:24 +08:00
    看这记录 大概率是没获取到 gap 锁
    xizismile
        9
    xizismile  
       2020-10-23 08:34:54 +08:00   ❤️ 1
    update 更新使用了两个索引导致的死锁问题

    可以看下面这个链接:
    http://mysql.taobao.org/monthly/2016/03/10/
    Visitor233
        10
    Visitor233  
       2020-10-23 10:02:33 +08:00
    @xizismile 我很好奇这种文章链接是在那找到的,这非常的有用,如果还能找到其他大厂的那更好
    user8341
        11
    user8341  
       2020-10-23 12:16:15 +08:00   ❤️ 1
    我觉得楼主可以试试 @DoctorCat 说的。将事务 2 分成两步执行:
    BEGIN;
    select id into @row_id where .... for update SKIP LOCKED
    update update system_proxy set status = 1, holder = 3 where id = @row_id;
    COMMIT;

    其中 select for update 用 skip locked 跳过被其他事务加锁的行。
    user8341
        12
    user8341  
       2020-10-23 12:21:11 +08:00
    @geligaoli 也是这么建议
    xizismile
        13
    xizismile  
       2020-10-23 15:06:51 +08:00   ❤️ 1
    @Visitor233 有意思,大多数人想要鱼,你想要的确是渔

    分享一哈,我查找这个问题的思路
    1.看问题日志描述和下面的一些回答,基本上可以确定的是,mysql 在双索引下,会出现死锁的问题
    2.然后拿着三个关键词去百度 /bing/google 搜索,“mysql 双索引 死锁”,这样你就能搜出一大堆博客来,遇到的问题和题主的差不多
    3.我上面发的那个淘宝内部网站,来源是我在学 mysql 的时候,在其他资料里看到的,里面大多数是数据库源码原理的解析
    因此想在这个网站里面查找,有木有上述的问题,就用到了高级搜索技巧(site 指令表示只在这一个网站内部进行搜索)
    “mysql 双索引 死锁 site:mysql.taobao.org
    Visitor233
        14
    Visitor233  
       2020-10-23 16:43:47 +08:00
    @xizismile 现如今的信息海洋,渔的熟练度不高,捞上来的死鱼解决不了温饱问题呀。
    分享一个美团的 tech.meituan.com
    xizismile
        15
    xizismile  
       2020-10-23 19:51:54 +08:00
    @Visitor233 美团的这个博客也是挺有深度的,赞一个~
    lry
        16
    lry  
    OP
       2020-10-23 21:44:14 +08:00   ❤️ 1
    @user8341 这个应该也可以。第一见 select for update skip locked 的用法。
    之前看过一篇文章说,select for update 可以用 先 update 后 select 代替来提高效率,所以没想到这里遇到了索引更新导致的死锁问题。今天我上午修改了一些表和查询设计,避免了同时锁 index_holder_status 索引 (其实就是调整了联合索引的顺序。。。)
    ```
    CREATE TABLE `system_proxy` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `ip` varchar(46) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
    `port` int unsigned NOT NULL DEFAULT '0',
    `holder` int unsigned NOT NULL DEFAULT '0',
    `used` tinyint unsigned NOT NULL DEFAULT '0',
    `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (`id`) USING BTREE,
    KEY `index_holder_used` (`holder`,`used`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=5585 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
    ```

    ```
    // 设置代理资源 holder 为当前线程 id
    update system_proxy set holder = #{holder} where holder = 0 limit 1
    // 查询线程当前独占的代理资源
    select id, ip, port from system_proxy where holder = #{holder} and used = 0
    // 更新行记录 修改代理资源状态为已使用
    update system_proxy set used = 1 where id = #{id}
    ```
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   992 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 22:13 · PVG 06:13 · LAX 15:13 · JFK 18:13
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.