记录一次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的索引文件发生变化，但是会扰动数据库优化执行器）