大事务在存储过程中直接提交会阻塞从库SQL线程,因其生成单个超大binlog event,从库必须串行重放完该事件才能处理后续事务,导致Seconds_Behind_Master跳涨、SQL线程卡死;需拆分事务、禁用autocommit、监控binlog文件突增及单event行数,并避免混入非DB操作、MyISAM表或错误隔离级别。

大事务在存储过程中直接提交会阻塞从库SQL线程
MySQL主从复制的SQL回放是单线程(即使开了slave_parallel_workers),一个存储过程里执行DELETE FROM huge_table WHERE status = 'old'这种没加LIMIT的语句,会生成单个超大binlog event;从库必须等这个event全部重放完,才能处理后续任何事务。Seconds_Behind_Master不是慢慢涨,而是“跳涨”——比如主库花3秒提交,从库可能要27秒回放,期间完全卡死。
存储过程里的隐式长事务比手动写更难察觉
很多人在存储过程中用BEGIN开事务,中间调用SELECT ... FOR UPDATE、再做HTTP请求或日志落盘,最后才COMMIT。这导致锁持有时间远超SQL执行本身,而INFORMATION_SCHEMA.INNODB_TRX里只显示trx_state = "RUNNING",不看trx_started时间戳根本发现不了问题。
- 事务内混入非DB操作(如调外部API)会让锁等待变成网络延迟的放大器
- MyISAM表混在InnoDB事务中:事务只能回滚InnoDB部分,MyISAM变更已落地,主从数据实际已不一致
-
READ COMMITTED隔离级别下,SELECT FOR UPDATE仍会锁住间隙,尤其WHERE条件无索引时直接升为表锁
拆分逻辑写在存储过程里反而更危险
有人试图在存储过程中用循环+LIMIT模拟分批,但没控制好边界条件,结果出现漏删或重复删。比如用WHERE created_at 分页,高并发写入下新数据不断插入,created_at不是单调递增,就会跳过部分行。
- 正确做法是用唯一递增字段(如
id)+显式记录最大已处理值,每次WHERE id > @last_id LIMIT 5000 - 每批必须
COMMIT,禁用autocommit=1,否则每批都触发一次binlog刷盘,I/O压力翻倍 - 避免在存储过程里用变量传参(如
WHERE col = @val),优化器无法准确估算行数,容易选错索引
真正该监控的不是延迟值,而是binlog事件体积
光盯Seconds_Behind_Master是马后炮。主库上一旦有存储过程产生大事务,SHOW MASTER LOGS会立刻暴露异常:某个binlog文件大小突增到几百MB,而平时只有几MB。这说明事务没拆分,所有变更被塞进单个event。
- 用
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000012 | grep -A 5 -B 5 "DELETE\|UPDATE" | head -20可快速确认单event是否含数万行变更 -
binlog_row_image=FULL会加剧问题——每行变更都记完整镜像,大事务下binlog体积直接翻3–5倍 - 从库
relay_log_info_repository=TABLE比FILE更可靠,避免因崩溃丢失位点导致重放错乱
复杂点在于:存储过程本身不记录执行计划,你得把里面每条SQL单独抽出来EXPLAIN;更麻烦的是,同一段逻辑在不同数据量级下,执行计划可能完全不同——上线前必须用生产数据量级压测。










