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

别告诉我有BUG

已于 2024-07-10 20:45:52 修改

阅读量580

点赞数 15

文章标签： mysql

于 2024-07-10 20:45:27 首次发布

本文链接：https://blog.csdn.net/qq_42207682/article/details/140333248

版权

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

现象

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

排查思路

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

解决办法

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

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

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