mysql两个修改事务相互阻塞_MySQL 5.7并发复制和mysqldump相互阻塞引起的复制延迟...

最新推荐文章于 2025-02-12 16:51:49 发布

张弼弛

最新推荐文章于 2025-02-12 16:51:49 发布

阅读量180

点赞数

CC 4.0 BY-SA版权

文章标签： mysql两个修改事务相互阻塞

本文链接：https://blog.youkuaiyun.com/weixin_32600979/article/details/113385820

在MySQL 5.7环境中，发现主从复制存在严重延迟，但slave_behind_master值显示为0。分析发现，复制SQL线程与备份线程相互阻塞，但并非死锁。问题源于基于ROW格式的复制和并发复制机制，以及mysqldump备份时与复制的冲突，可能导致复制和备份作业相互hang住，需要DBA手动干预。解决此问题需关注复制线程的状态以及主从库的binlog位置对比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本来MySQL BINLOG和SHOW PROCESSLIST命令属于八竿子打不着的两个事务，但在最近故障排查中，发现主库和从库已经存在很严重的复制延迟，但从库上显示slave_behind_master值为0，复制SQL线程与备份线程之间相互阻塞，但未报死锁。

在从库上执行SHOW PROCESSLIST发现复制的SQL线程等待锁，而等待SQL的WHERE条件竟然是类似于WHERE C1=’ABC’ AND C2>’2018-03-01′ AND C2

使用MySQL Binlog导出日志一看：

发现真错怪研发同事啦，rbr_only=yes说明基于ROW格式进行复制，“SET TRANSACTION ISOLATION LEVEL READ COMMITTED”也是基于行格式复制的典型特征之一，last_committed和sequence_number用于MySQL 5.7版本中的并发复制，row_query后跟的是在主库上执行的原始SQL，也就是我们在从库SHOW PROCESSLIST中看到的SQL，但实际上从库执行的还是BINLOG部分，该BINLOG可以直接可以直接直接在从库上执行，也可以解析成一行行的数据DML操作，BINLOG部分如下：

==========================================================================================================

另外一个很有意思的问题，如果在从库上运行mysqldump进行备份，且从库上使用并行复制，会导致备份和复制相互阻塞：

在上面的阻塞中，多个SQL线程与备份线程相互之间阻塞，且MySQL无法有效检测出死锁环路而触发死锁的回滚机制，导致复制线程和备份作业相互hang住，需要DBA进行干预(取消备份或停止复制)，在复制SQL线程被hang住期间，复制的IO线程仍可以正常工作接受到主库的Binlog信息，但slave_behind_master并不会随之增大，如果仅通过监控slave_behind_master值来判断主从复制延迟，则会导致延迟监控存在严重漏洞，因此在监控复制延迟时，除监控slave_behind_master值外，还需要监控主库binlog位置点和从库执行的binlog位置点。