一个SQL无法kill掉的案例

一个SQL无法kill掉的案例

问题描述

因为业务需要，客户有一个ddl需求扩展某一drds单表的列的长度，但是在drds上执行ddl的时候被一个select阻塞mdl（metadata lock）锁，详情见官方文档8.11.4 Metadata Locking。报错以及SQL如下：

这样的SQL是手工执行，客户想要通过kill掉SQL来解决，但是出现问题：

执行kill命令成功，但是一直显示SQL为killed状态，还是在执行中。由于这个SQL一直不能释放资源，导致ddl无法执行。

解决过程

Killed状态是正在释放SQL所占用的资源，官方解释如下：
Killed
  Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.

通过查看SQL的事务，发现这个查询所在的事务已经执行了一周以上的时间，如果是等待回滚的话，需要等待很长一段时间。（估计是hang死在这了）

看SQL的执行计划，笛卡尔积总共扫描200亿行数据。

为了快速恢复客户业务，首先想到的就是通过RDS主备切换，因为是drds单表，访问都是在0号库上，所以只针对RDS 0号库主备切换，然后重启备库。（还是需要重启解决啊==）
通过DRDS层面看这个SQL已经因为执行超时被kill了，但是下发到了RDS上还是在执行。

检查好主从状态，确定没有延迟，通过杜康进行主备切换，切换成功后验证使用ddl语句，发现报错：

根据报错发现是drds分库上表不存在。

DRDS单表与小表广播的区别：

单表与小表广播都是会在所有drds的分库上创建一张表，只不过小表广播的表所有的分表都有全表数据；而单表的数据只会存储在0号库，其他库只有一个表结构，这么做是为了方便将来可能使用小表广播。
小表广播：在各分库就完成了数据join汇总，避免跨库操作

检查分库分表情况，发现确实只有0号库有这张表，其他分库没有

SQL没有开启审计，客户反馈没有执行过删除其他分库的表命令，无法定位为什么导致表结构只有在0号库存在的问题，先解决当前问题。通过在DRDS上创建表，create table if not exists table_name，将分库的表结构补齐后，客户反馈执行ddl成功。
该处理备库了，备库不出意外的也出现了MDL锁，由于同步过来的ddl语句导致，开始重启备库了。

重启备库出现了小插曲，在杜康上重启备库失败，重跑也失败，最后是黑屏客户端登录到备库上，手动拉起的mysql进程，重新跑成功，有惊无险，问题解决！

总结：

Killed这种状态，一般是大事务、大查询、ddl等执行过程中被kill，回滚需要很长的一段时间，只能通过等待SQL事务回滚完成，这里是select阻塞直接重启mysql，可以通过切换主备后重启备库的方式解决。另外在执行ddl命令前可以先在备库执行，set sql_log_bin=OFF停止写binlog，然后主备切换，这样旧主上就不会因为这个ddl同步过来再次产生mdl锁，然后在旧主上执行ddl相同命令，从而降低对业务的影响。