记录一次mysql死锁问题的分析排查

记录一次死锁问题的分析排查

现象

  1. 底层往kafka推送设备上线数据
  2. 应用层拉取设备上线消息,应用层有多个消费者并发执行
  3. 将设备上线数据同步数据库表pa_terminal_channel
  4. 日志报:(Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction)死锁
  5. sql语句为:update pa_terminal_channel SET status = ‘Y’ WHERE channel_id = ‘通道id’ and device_id = ‘设备id’

排查思路

  1. 由于应用层有多个消费者对消息进行处理,导致并发更新的情况发生
  2. 手动开启事务的方式复现现象
  3. 第一个窗口执行更新,但不提交
    在这里插入图片描述
  4. 第二个窗口执行更新,发现进入等待,随后报1025错误
    在这里插入图片描述
    在这里插入图片描述
  5. 经过框架查找,数据库报1025错经过jdbc框架错误码转换后报Deadlock found when trying to get lock,即日志中所出现的错误
  6. 查看执行计划,发现该条数据的type为index_merge,即数据库查询优化器介入处理,此时在更新操作中使用了两个条件,并且这些条件涉及到两个索引(channelid和deviceid对应的索引),MySQL 可能会使用多个索引来查找符合条件的行,从而可能会锁定更多的行。也就意味着在同一批设备id相同,但通道id不同的数据也会被锁住。因此本次死锁原因出现。
    在这里插入图片描述

解决办法

增加channelid和deviceid的复合索引,让数据库走正常的复合索引,避免数据库查询优化器介入处理,此时缩小更新时锁的数据行数,避免触发死锁问题。
ALTER TABLE pa_terminal_channel ADD INDEX terminal_channelid_deviceid (channelID, deviceId);
增加复合索引后再观察执行计划,发现已正常走复合索引。
在这里插入图片描述

该问题分析过程中产生的错误猜想

  1. 猜想一、业务发生事务问题,导致经典死锁问题,但排查业务后发现并无事务介入(排除)
  2. 猜想二、代码中有一段获取channelid的操作,有可能发生线程安全问题,导致获取到相同channelid导致不同线程更新同一条数据触发死锁。(排除,因为在业务日志中可以看到处理后的channelid打印结果并无异常,并且通过多线程模拟调用该方法并无异常)
    在这里插入图片描述
  3. 猜想三、数据库没加索引,排除(数据库已有channelid单值索引,如果数据库执行优化器没有介入,光走单值索引也不会触发死锁)
  4. 猜想四、数据库索引文件发生损坏(排除、创建新的索引不会直接改变现有索引的结构。)。验证(将原有表增加复合索引,后又删除复合索引后,锁表问题得到解决。此过程不会导致原有channelid的索引文件发生变化,但是会扰动数据库优化执行器)

相关推荐

  1. 记录kill杀不进程

    2024-07-10 23:08:04       39 阅读
  2. 【SQL】MySQL 问题以及解决方式

    2024-07-10 23:08:04       3 阅读
  3. 事务失效问题排查

    2024-07-10 23:08:04       30 阅读
  4. 问题记录】线程池问题

    2024-07-10 23:08:04       3 阅读

最近更新

  1. Linux常用命令(持续更新)

    2024-07-10 23:08:04       0 阅读
  2. spring boot 实现token验证登陆状态

    2024-07-10 23:08:04       0 阅读
  3. nginx的安装和使用

    2024-07-10 23:08:04       0 阅读
  4. 深入了解 GCC

    2024-07-10 23:08:04       0 阅读
  5. 【MyBatis】Mybatis中的动态SQL——bind标签

    2024-07-10 23:08:04       0 阅读
  6. GreenDao实现原理

    2024-07-10 23:08:04       0 阅读
  7. 分布式缓存设计:深入理解 Memcached 架构

    2024-07-10 23:08:04       0 阅读
  8. 项目相关方不配合,项目经理怎么办?

    2024-07-10 23:08:04       0 阅读
  9. oneos虚拟文件系统vfs源码分析

    2024-07-10 23:08:04       0 阅读

热门阅读

  1. python excel openpyxl

    2024-07-10 23:08:04       6 阅读
  2. 生物环保的技术原理和优点是什么

    2024-07-10 23:08:04       6 阅读
  3. 深入理解Spring Cloud中的服务注册

    2024-07-10 23:08:04       6 阅读
  4. SIFT代码,MATLAB

    2024-07-10 23:08:04       4 阅读
  5. Scala 数据类型

    2024-07-10 23:08:04       8 阅读
  6. DP学习——简单工厂模式

    2024-07-10 23:08:04       6 阅读
  7. 从 Spark 离线数仓到 Flink 实时数仓:实战指南

    2024-07-10 23:08:04       6 阅读
  8. 浅析DDoS高防数据中心网络

    2024-07-10 23:08:04       6 阅读