技术分享 | MySQL大事务导致数据库卡顿

本文为墨天轮数据库管理服务团队第66期技术分享，内容原创，作者为技术顾问孙文龙，如需转载请联系小墨（VX：modb666）并注明来源。

一、现 象

业务侧反馈连接数据库异常，报错 connection is not avaliavle

二、分 析

1）在数据库的error日志中没有发现异常，在数据库监控数据中发现当时数据库连接短时间内激增， 但是 活动会话没有明显波动。并且问题时间段产生了一个2GB大小的binlog（max\_binlog\_size 配置为 1GB），说明这个时间段执行了一个大事务。

解析binlog日志进行确认：

事务开始是的binlog pos 31127469

事务结束时的binlog pos：1946331810

也就是说这一个事务产生的binlog将近2GB，修改的数据量大概150万

2）一个事务被记录到binlog时，必须连续、完整的记录到一个binlog文件中， 中间不能插入其它的事务。因此有可能是此大事务导致的故障现象

三、复 现

准备

1）将测试数据导入到生产环境，并准备一个update语句，一次修改数据量大概150万

2）准备一个脚本，循环连接测试数据库并执行一个小事务，用来探测大事务提交时，对小事务的影响。

脚本中计算连接数据库、执行"show master status"、执行update小事务花费的时间， 并且当花费的时间超过1秒时，进行打印。

3）准备一个用来监控测试数据库会话和事务状态的SQL

场景1

1）不开binlog

2) 测试结果

数据库中未发现异常事务， update语句会话状态一直是updating，直到update执行完

探测脚本中的事务执行时间均为超过1秒

场景2

1）开binlog ，没有从库

2）测试结果

从数据库会话状态来看，探测脚本中的事务的coommit和 ‘show master status’语句都被大事务的提交阻塞

从探测脚本的结构也得到印证

场景3

1）开binlog，并且有增强半同步的从库

2）测试结果

写本地binlog期间，'show master status’和探测脚本中的事务的commit， 都被阻塞。last\_wait 都是 LOCK\_log

向从库同步期间，探测脚本中的事务的commit被阻塞，last\_wait 为MYSQL\_BIN\_LOG::LOCK\_commit

四、结 论

在事务提交写binlog的过程中，其它事务的提交动作已经查看binlog的操作将被阻塞。在事务被同步到从库的过程中， 会阻塞其它事务的提交。

---

墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。

服务官网：https://www.modb.pro/service