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

关于 mysql 死锁(dead lock)的问题

  •  
  •   Buffer2Disk · 2019-03-19 21:11:04 +08:00 · 4322 次点击
    这是一个创建于 1836 天前的主题,其中的信息可能已经有所发展或是发生改变。

    线上项目有 2 个定时任务在跑

    任务 1:向 A 表中批量插入数据,采用的是 mybatis SqlSession ---> ExecutorType.BATCH 方式来批量提交数据

    任务 2: 向 A 表中删除一条数据

    在某个时间点,2 个任务同时执行了,然后产生了死锁的情况。

    想问下各位大佬,这个死锁是怎么造成的?

    我看了一些文档猜测是 gap 锁和插入意向锁,但是又有点理不清楚到底怎么死锁的

    mysql 查看死锁的日志 show engine innodb status\G;

    日志如下 :

    任务 2 的锁

    任务 1 的锁(使用了 spring 的事务回滚)

    17 条回复    2019-03-20 14:25:54 +08:00
    xjy110207
        1
    xjy110207  
       2019-03-20 00:55:29 +08:00 via Android
    一个可能的解释:
    1、事务 2 先执行,在索引 218 页的位置先获得了一把排它锁
    2、事务 1 开始执行,顺序扫描索引并用 gap 锁锁住了处于 200 页的某条记录
    3、事务 2 执行到要在 200 页获得插入意图锁的阶段,开始等待事务 1
    4、事务 1 顺序扫描到了 218 页,需要获得排它锁,开始等待事务 2
    5、至此,就产生了死锁。
    xjy110207
        2
    xjy110207  
       2019-03-20 01:07:51 +08:00
    其产生的根本在于你 mysql 的隔离级别是可重复读,gap 锁仅在可重复读模式下出现。所以要解决这个死锁问题有两种方案:
    1、修改你删除定时任务的连接的事务隔离级别为读已提交。
    2、将插入定时任务修改为一个事务插入一行。
    charles2java
        3
    charles2java  
       2019-03-20 01:13:47 +08:00 via Android
    有可能,批量更新时加锁顺序不一致而导致的
    xjy110207
        4
    xjy110207  
       2019-03-20 01:14:14 +08:00
    补充: 处于可重复读模式下也不一定会产生 gap 锁,前提是你的语句会使用唯一索引, 但是你这个两条语句就这个信息来看使用的 uid 索引并不是唯一索引。
    Buffer2Disk
        5
    Buffer2Disk  
    OP
       2019-03-20 12:48:10 +08:00
    @xjy110207 确实是 RR 模式,uid 加了普通索引
    任务 1 插入的时候数据太多,是放在一个事务里面批量提交的
    Buffer2Disk
        6
    Buffer2Disk  
    OP
       2019-03-20 12:49:16 +08:00
    @xjy110207 我目前的解决方案是,给 2 个定时任务加了可重入锁,让它们不要同时执行


    或者 把 RR 改成 RC 也可以,这个没试过
    Buffer2Disk
        7
    Buffer2Disk  
    OP
       2019-03-20 12:56:08 +08:00
    @xjy110207 其实我没太明白,任务 2 的作用就是执行了一条 sql,删除了一条记录

    为啥
    "顺序扫描索引并用 gap 锁锁住了处于 200 页的某条记录"



    "顺序扫描到了 218 页,需要获得排它锁,开始等待事务 2"

    发生了这 2 件事情
    Buffer2Disk
        8
    Buffer2Disk  
    OP
       2019-03-20 12:58:05 +08:00
    这个 gap 锁范围有这么大么?
    xjy110207
        9
    xjy110207  
       2019-03-20 12:58:46 +08:00   ❤️ 1
    @Buffer2Disk 你这个死锁的原因就是因为两个事务都是修改大量数据, 这些数据的上锁顺序不一致导致的,这是产生死锁的主要原因之一。 一个通用解决方案就是每个事务都尽可能的只修改少量的数据, 从你这个 log 里面来看,这两个事务改动的都比较多, 执行时间都在 10s 以上,出问题概率还是挺大的。
    ps: 可能还有一种解决方案就是,因为索引本身是有序的,索引 update delete 会按照索引顺序加锁,你可以修改插入事务,对 uid 排序后再插入,那插入时候获得锁的顺序就跟 delete 时是一样的,理论上也不会出现死锁,顶多就是会有一个事务等待时间较长.
    xjy110207
        10
    xjy110207  
       2019-03-20 13:00:21 +08:00
    @Buffer2Disk delete 之类的 gap 锁会锁定记录之前的,你这个 1675 的 id 起始记录位置可能处于 200 到 218 页之间的某个页上,那么这个记录之前的都会被加上 gap 锁
    Buffer2Disk
        11
    Buffer2Disk  
    OP
       2019-03-20 13:03:53 +08:00
    @xjy110207 "一个通用解决方案就是每个事务都尽可能的只修改少量的数据"

    会不会造成插入的时间耗时增加呢?
    Buffer2Disk
        12
    Buffer2Disk  
    OP
       2019-03-20 13:07:31 +08:00
    我目前 插入的定时任务一次性插入 200 多条数据,采用的是 mybatis SqlSession ---> ExecutorType.BATCH 方式来批量提交数据;

    如果用 mybatis 的 foreach 来提交不知道会不会快一点,但是看这个老外评论好像效率会更低
    https://stackoverflow.com/a/40608353/2379891
    xjy110207
        13
    xjy110207  
       2019-03-20 13:10:32 +08:00 via Android
    @Buffer2Disk 你这个是离线任务的话,其实没太大影响。关于事务,锁方面的知识点你可以参考 mysql-refman 里面 14.7 章节
    xjy110207
        14
    xjy110207  
       2019-03-20 13:12:50 +08:00 via Android
    @Buffer2Disk 如果你这个表 uid 不会重复的话,建唯一索引可以快速的解决问题,无需程序做任何改动
    Buffer2Disk
        15
    Buffer2Disk  
    OP
       2019-03-20 13:13:00 +08:00
    @xjy110207 嗯嗯,非常感谢你的回复,受教了
    amwyyyy
        16
    amwyyyy  
       2019-03-20 13:59:49 +08:00
    跟我理解的有点不一样,请问下删除语句那里,uid=1675 的数据当时是存在的吗?
    Buffer2Disk
        17
    Buffer2Disk  
    OP
       2019-03-20 14:25:54 +08:00
    @amwyyyy 是存在的
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4538 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 09:58 · PVG 17:58 · LAX 02:58 · JFK 05:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.