MySQL Binlog Commit Process: Parameter Settings and Step‑by‑Step Analysis

Introduction

This excerpt is taken from the 15th chapter of "Deep Understanding of MySQL Master‑Slave Principles" and outlines the detailed commit process of MySQL's binary log, focusing on important steps and related parameters.

1. Parameter Settings

binlog_group_commit_sync_delay = 0

binlog_group_commit_sync_no_delay_count = 0

binlog_order_commits = ON

sync_binlog = 1

binlog_transaction_dependency_tracking = COMMIT_ORDER

The binlog_transaction_dependency_tracking parameter determines which dependency tracker (COMMIT_ORDER, WRITESET, or WRITESET_SESSION) is used to compute last_commit and seq_number . This section concentrates on the COMMIT_ORDER mode.

2. Overall Flowchart

The overall process is illustrated in the diagram (Figure 15‑1). The diagram is omitted here for brevity.

3. Phase One – Prepare (Blue Part)

Before step 1 a MDL COMMIT lock is acquired, which may block FTWRL. The following actions occur:

Binlog preparation – write the previous commit’s maximum sequence number into last_commit (see binlog_prepare ).

InnoDB preparation – change transaction state to PREPARED and write XID to undo (see trx_prepare ).

Generate XID_EVENT and write it to the binlog cache (see MYSQL_BIN_LOG::commit ).

4. Phase Two – Flush (Pink Part)

Form the FLUSH queue; the first transaction becomes the leader, others wait.

Acquire LOCK log.

Fetch the FLUSH queue for processing (see stage_manager.fetch_queue_for ).

Persist InnoDB redo and help other transactions persist redo: /* We flush prepared records of transactions to the log of storage engine (for example, InnoDB redo log) in a group right before flushing them to binary log. */ ha_flush_logs(NULL, true); // do InnoDB redo persistence

Generate GTID and seq_number , then write a GTID_EVENT directly to the binary log (see binlog_cache_data::flush ). trn_ctx->sequence_number = mysql_bin_log.m_dependency_tracker.step(); if (!error) if ((error = mysql_bin_log.write_gtid(thd, this, &writer))) thd->commit_error = THD::CE_FLUSH_ERROR; if (!error) error = mysql_bin_log.write_cache(thd, this, &writer);

Write all events (QUERY_EVENT, MAP_EVENT, DML EVENT, XID_EVENT) from the binlog cache to the binary log.

Check whether the binary log needs rotation based on max_binlog_size (see MYSQL_BIN_LOG::process_flush_stage_queue ). if (total_bytes > 0 && my_b_tell(&log_file) >= (my_off_t)max_size) *rotate_var = true; // mark rotation needed

5. Phase Three – Sync (Purple Part)

Move the FLUSH queue into the SYNC queue; the first becomes the leader.

Release LOCK log.

Acquire LOCK sync.

Delay handling based on binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count to increase group size.

Fetch the SYNC queue for processing.

Perform disk sync according to sync_binlog (see MYSQL_BIN_LOG::sync_binlog_file ).

6. Phase Four – Commit (Yellow Part)

Move the SYNC queue into the COMMIT queue; the first becomes the leader.

Release LOCK sync.

Acquire LOCK commit.

If binlog_order_commits is ON, process the COMMIT queue in order; otherwise, skip ordered commit.

Update last_commit for each transaction (see Commit_order_trx_dependency_tracker::update_max_committed ). { m_max_committed_transaction.set_if_greater(sequence_number); }

Commit each transaction in InnoDB (see innobase_commit ), which updates read‑views, undo state, and releases locks.

7. Phase Five – Cleanup (Green Part)

Leader thread wakes up all members.

Reset binlog cache for each transaction.

If binlog_order_commits is OFF, each transaction commits in InnoDB independently.

Rotate binary log if the rotation flag was set.

Clean up old binary logs according to expire_logs_days .

Conclusion

The generation of last_commit and seq_number incurs minimal overhead in COMMIT_ORDER mode.

Each stage (FLUSH, SYNC, COMMIT) has its own queue; transaction order is preserved across queues.

Parallel replay on replicas depends on the last_commit values obtained during the prepare phase.

Large transactions cause all their events to be written to the binary log at once, which can block other transactions and is a reason MySQL discourages very large transactions.

For a deeper dive, refer to the full book "Deep Understanding of MySQL Master‑Slave Principles (32 Lectures)" by Gao Peng.