Analysis of Commit Blocking and SHOW MASTER STATUS Contention in MySQL 5.7 Replication

MySQL master‑slave replication can provide an online backup system, but it cannot perform automatic failover; a large transaction may block statements like SHOW MASTER STATUS , leading to misjudgments during failover.

Scenario simulation : Two‑tens‑of‑millions‑row dataset is deleted within a single transaction. While the transaction is running, a separate session executes SHOW MASTER STATUS . The command succeeds before the transaction commits but becomes blocked during the commit phase. Stack traces show the blockage occurs while acquiring lock_log .

#6  0x0000000000ee8278 in MYSQL_BIN_LOG::get_currrent_log (this=0x1e839c0 <mysql_bin_log>, linfo=0x7f3ea82e62d0, need_lock_log=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/binlog.cc:5514

Cause analysis – commit : The commit process in MySQL 5.7 consists of four stages (prepare, flush, sync, commit). The prepare stage writes redo/undo logs. The flush stage persists redo and acquires lock_log , which blocks other transactions and SHOW MASTER STATUS . Sample code:

static int binlog_prepare(handlerton *hton, THD *thd, bool all) { DBUG_ENTER("binlog_prepare"); if (!all) { thd->get_transaction()->store_commit_parent(mysql_bin_log.m_dependency_tracker.get_max_committed_timestamp()); } }

int MYSQL_BIN_LOG::process_flush_stage_queue(my_off_t *total_bytes_var, bool *rotate_var, THD **out_queue_var) { DBUG_ENTER("MYSQL_BIN_LOG::process_flush_stage_queue"); #ifndef DBUG_OFF // number of flushes per group. #endif DBUG_ASSERT(total_bytes_var && rotate_var && out_queue_var); my_off_t total_bytes= 0; int flush_error= 1; mysql_mutex_assert_owner(&LOCK_log); }

The sync stage releases lock_log and acquires lock_sync , while the final commit stage releases lock_sync , acquires lock_commit , waits for follower transactions, and finally clears the binlog cache.

Cause analysis – SHOW MASTER STATUS : The command retrieves the latest binlog file’s Executed_Gtid information by calling MYSQL_BIN_LOG::get_current_log , which also acquires LOCK_log when need_lock_log is true.

int MYSQL_BIN_LOG::get_current_log(LOG_INFO* linfo, bool need_lock_log) { if (need_lock_log) mysql_mutex_lock(&LOCK_log); int ret = raw_get_current_log(linfo); if (need_lock_log) mysql_mutex_unlock(&LOCK_log); return ret; }

Conclusion : In the presented case, a single large transaction holds lock_log during the flush stage, causing SHOW MASTER STATUS to block. The lock is a log‑level lock, not an MDL or engine lock; larger transactions increase the lock hold time, leading to I/O spikes, replication lag, and possible failover issues. Recommended solutions are to avoid large transactions and replace SHOW MASTER STATUS with SELECT @@global.gtid_executed to obtain GTID information without acquiring the log lock.

Additional information about the SQLE tool and related resources is provided at the end of the article.