Why Does MySQL Use Two-Phase Commit? Understanding Redo Log and Binlog Coordination

Why Two‑Phase Commit Is Needed

MySQL maintains two independent logging systems: the InnoDB redo log, which records page modifications for crash recovery, and the server‑level binlog, which records logical data changes for replication and recovery. When a transaction commits, both logs must be written atomically; otherwise, inconsistencies arise.

(1) If the redo log commits but the binlog fails, the transaction remains after crash recovery, but replicas cannot synchronize the latest data.

(2) If the binlog commits but the redo log fails, the transaction is rolled back during recovery, yet replicas may have already applied the changes, leading to divergence.

To guarantee consistency between redo log and binlog writes, MySQL uses a two‑phase commit.

Two‑Phase Commit Process

The coordination between redo log and binlog is illustrated below:

InnoDB writes the redo log in two stages: a prepare phase and a commit phase.

Prepare Phase

MySQL writes the transaction XID together with the modified data to the redo log and marks the entry as “PREPARED”. The actual data changes are not yet visible; if an exception occurs, the transaction can be rolled back.

Commit Phase

MySQL writes the transaction XID to the binlog; after the binlog write succeeds, it updates the redo log status to “COMMITTED”. The transaction then becomes visible to other sessions.

If an error occurs during either phase, different recovery behaviors apply:

If a crash happens right after the redo log is written in the prepare phase, recovery finds no “COMMITTED” flag, looks for the XID in the binlog, fails to locate it, and rolls back the transaction.

If a crash occurs after the binlog write, recovery finds the XID in the redo log, locates the corresponding binlog entry, and can commit the data.

Summary

The core purpose of MySQL’s two‑phase commit is to keep InnoDB’s redo log and the server’s binlog consistent, preventing errors during crash recovery or master‑slave synchronization.

Introducing two‑phase commit also brings challenges: increased lock contention in high‑concurrency environments, extra disk I/O due to multiple fsync() calls, and potential blocking because a transaction holds locks for the entire commit process.