MySQL备份数据死锁_由xtrabackup导致的MySQL从库死锁分析及参数深究

最新推荐文章于 2025-09-08 16:14:44 发布

原创

最新推荐文章于 2025-09-08 16:14:44 发布 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL备份数据死锁

本文详细分析了一种由xtrabackup执行flush tables with read lock引发的MySQL从库死锁现象。在并行复制环境下，由于MDL锁和全局读锁、提交锁的交互，导致了死锁，具体表现为145->162->146->145和183->167->166->183的循环。解决方案包括设置xtrabackup参数、调整复制策略或禁用slave_preserve_commit_order。同时，文章指出kill-long-query-type参数的使用需要注意，避免因文档误导造成问题。

最近线上执行备份的从库时出现复制卡死现象，分析以后发现是两个死锁，show full processlist的状态如图1所示，其中，数据库版本是官方5.7.18版本，我们内部做了些许修改，但与此次死锁无关。

先说一下结论，图一中：

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

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

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

144Coordinator线程分发relaylog中事务时发现这个事务不能执行，要等待前面的事务完成提交，所以处于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的死循环，形成死锁。

同样的，图二中：

183是备份程序执行的flush tables with read lock;

165是SQL线程，并行复制的Coordinator线程；

166/167是并行复制的worker线程。

165Coordinator线程分发的事务还不能执行，进入waiting for dependent transaction to commit的状态，183、166、167三个线程形成死锁，183占有全局读锁，获取全局commit锁的时候进入阻塞，等待167释放事务涉及到表的commit锁；166，167的事务可以并行复制，167占有表级commit锁，但是事务对应的binlog在后面，阻塞等待166先提交进入waiting for preceding transaction to commit的状态；166线程事务执行时提交要获得表级commit锁，但已经被183占有

最低0.47元/天解锁文章