Slave SQL 线程与 PXB FTWRL 死锁问题分析

1. 问题背景

2.27 号凌晨生产环境 MySQL 备库在执行备份期间出现因 FLUSH TABLES WITH READ LOCK 未释放导致备库复制延时拉大，慢日志内看持锁接近 25 分钟未释放。

版本：

• MySQL 5.7.21

• PXB 2.4.18

慢查询日志：

备份脚本中的备份命令：

mysql_kill.sh 的主要逻辑内容：

备份参数：

2. 问题复现及分析

2.1 问题分析

• 144 是 SQL 线程，并行复制中的 Coordinator 线程；

• 145/146 是并行复制的 worker 线程，145/146worker 线程队列中的事务可以并行执行。

• 162 线程是执行 innobackup 执行的 flush tables with read lock;

144 Coordinator 线程分发 relay log 中事务时发现这个事务不能执行，要等待前面的事务完成提交，所以处于 waiting for dependent transaction to commit 的状态。145/146 线程和备份线程 162 形成死锁，145 线程等待 162 线程 global read lock 释放，162 线程占有 MDL::global read lock 全局读锁，申请全局 commit lock 的时候阻塞等待 146 线程，146 线程占有 MDL:: commit lock，因为从库设置 slave_preserve_commit_order=1，保证从库 binlog 提交顺序，而 146 线程执行事务对应的 binlog 靠后面，所以等待 145 的事务提交。最终形成了 145->162->146->145 的死循环，形成死锁。

三个线程相互形成死锁，还是很少见的。

2.2 相关参数为何未生效

--ftwrl-wait-timeout=60　指的是执行 FTWRL 之前，如果检测到存在长 SQL，先等待指定时间（秒），如果超时后还存在长 SQL，则备份报错退出。默认为 0 则表示立即执行。

--ftwrl-wait-threshold=5 指的是执行 FTWRL 之前，检测长 SQL 的方法，如果在执行 flush 前存在已经运行了超过指定时间（秒）的 SQL，则将该 SQL 定义为长 SQL，默认 60s。

--kill-long-queries_timeout=0 在执行 FTWRL 后，如果 flush 操作被阻塞了 N 秒，则 kill 掉阻塞它的线程，默认 0 的情况就是不 kill 任何阻塞 flush 的 SQL，直到该 SQL 执行完成。

从上面各个参数的解释，不难看出，--ftwrl-wait-*参数是针对执行 FTWRL 之前的长 SQL 检测机制，对于已执行 FTWRL 时无济于事，--kill-long-*参数则是设置默认值 0，不起任何作用。

3. 结论与建议

• PXB 备份中执行 FTWRL 加全局读锁与 SQL 线程形成死锁是导致本次从库延迟过高的原因。

• 启用--kill-long-queries_type 和 --kill-long-queries_timeout 参数，在检测到 flush 被阻塞后执行 kill 掉相关线程的操作。比较暴力，存在较大的风险，若备库无业务访问则可考虑。

• 启用--safe-slave-backup 参数，执行备份时该参数会停掉 SQL 线程，从而避免死锁的产生。仅建议在无业务访问的备库上执行。

• 设置 MySQL 参数 slave_preserve_commit_order=0，关闭从库 binlog 的顺序提交，关闭该参数只是影响并行复制的事务在从库的提交顺序，对最终的数据一致性并无影响，所以如果无特别要求从库的 binlog 顺序必须与主库保持一致，可以考虑设置 slave_preserve_commit_order=0 避免死锁的产生。