The idea: one commit, two logs, one fsync for many
With the binary log on, a MySQL transaction is written to two logs: InnoDB's redo log, which crash recovery replays, and the server's binlog, which replicas and point-in-time recovery replay. Both must agree on which transactions committed, so every commit is a small two-phase commit between them. And because each phase wants an fsync, MySQL runs commits through a three-stage pipeline, binlog group commit, where one thread does the slow work for a whole group of others.
This page zooms in on the commit. For the whole life of a query (parser, optimizer, buffer pool, undo) see How MySQL Runs a Query.
Two-phase commit inside MySQL
The server layer is the coordinator, InnoDB and the binlog are the two participants. For one transaction:
- InnoDB prepare. The transaction's state in its undo log becomes
PREPARED, and a redo record carries its Xid, an id the server gave the transaction. After this InnoDB can go either way. - Binlog write. The session's events, collected in its binlog cache during the transaction, are written to the binlog:
GTID,BEGIN, the row events, and finally an Xid event with the same Xid. Once that is on disk, the transaction is committed, whatever happens next. - InnoDB commit. The commit record is written, locks are released, the client gets OK.
Since MySQL 5.7, step 1 does not fsync the redo log by itself. The prepare record only goes into the log buffer, and the FLUSH stage below fsyncs it, once for the whole group. Step 3 needs no fsync at all: the prepare record plus the Xid event already decide the outcome. In Demo: one group, one fsync the commit records stay orange in the log buffer.
The three stages and their leaders
The binlog part of the commit (MYSQL_BIN_LOG::ordered_commit) is split into three stages. Each has a queue. A thread that arrives at an empty queue becomes that stage's leader; a thread that finds others already queued becomes a follower and simply sleeps until the leader wakes it at the end.
| Stage | The leader, for the whole queue |
|---|---|
| FLUSH | takes the queue, fsyncs the redo log up to the last prepare record, assigns GTIDs, and write()s each member's binlog cache to the binlog file (into the OS page cache) |
| SYNC | calls fsync() on the binlog once (sync_binlog = 1), optionally after waiting for more groups |
| COMMIT | commits every member in InnoDB, in binlog order, then wakes the followers |
When a FLUSH leader finishes and moves its group to the SYNC queue, it may find a SYNC leader already waiting there. It then becomes a follower too, and the two groups merge. Different groups can be in different stages at the same time, so the redo fsync of one group overlaps the binlog fsync of the previous one (Demo: two groups in the pipeline). On the canvas a stage runs when you press Next Stage; in a real server the leader starts at once, and the queue fills up while the previous group still holds the stage.
One fsync for many
An fsync costs from tens of microseconds (a drive with a power-safe cache) to several milliseconds. With one binlog fsync per commit, a disk that does 1,000 fsyncs a second caps the server at 1,000 commits a second. With group commit, a group of 20 shares one fsync. Busy servers form groups by themselves; two settings make them bigger:
binlog_group_commit_sync_delay(microseconds, default 0): the SYNC leader waits this long before its fsync, so more FLUSH groups pile up behind it.binlog_group_commit_sync_no_delay_count(default 0, here 4): stop waiting as soon as this many transactions are queued.
Demo: sync_delay grows the group commits four transactions with one binlog fsync, where Demo: two groups in the pipeline needs two. The price is latency: every commit in the group waits for the delay. On a replica, bigger groups also mean more transactions that can be applied in parallel, since transactions in the same group did not conflict.
Commit order: binlog_order_commits
With binlog_order_commits = ON (the default) the COMMIT leader commits the members in InnoDB in the same order as their events are in the binlog. So the order in which transactions become visible on the source is the order in which a replica will apply them, and a backup tool that reads the binlog position from InnoDB gets a consistent point. With OFF, each thread commits itself in parallel, a little faster, in any order.
Crash recovery: the binlog decides
After a crash InnoDB replays its redo log and finds the transactions that are PREPARED without a commit record. It cannot decide them alone. The server opens the last binlog file, collects the Xids of the complete transactions in it, and tells InnoDB: commit the prepared transactions whose Xid is in that list, roll back the rest. (A transaction that never even reached prepare on disk is simply rolled back with its undo.)
| Power fails… | Redo on disk | Binlog on disk | After recovery |
|---|---|---|---|
| after prepare, still in the FLUSH queue | nothing (log buffer) | nothing | rolled back; the client never got OK |
| after FLUSH, before SYNC | prepare, Xid | nothing (OS cache lost) | rolled back ✗ (Demo: crash after FLUSH) |
| after SYNC, before COMMIT | prepare, Xid | Xid event | committed ✓ (Demo: crash after SYNC) |
| after COMMIT | prepare (commit record maybe) | Xid event | committed ✓ |
The client gets OK only after the binlog fsync, so a transaction it was told about is always in the binlog, and therefore committed after recovery. The opposite case is harmless: a transaction that was committed by recovery but whose client saw a broken connection. Applications that retry after a lost connection must check whether the commit happened. MySQL 8.0 also truncates a partly written transaction at the end of the binlog during this step. See InnoDB Crash Recovery for the redo and undo passes.
sync_binlog and innodb_flush_log_at_trx_commit
The guarantees above need both logs fsynced: innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1, the defaults and the "double 1" setup. Relaxing either trades safety for fewer fsyncs:
| Setting | What a power loss can do |
|---|---|
sync_binlog = 0 | The binlog is never fsynced by MySQL. Transactions the clients got OK for can vanish, or be committed in InnoDB but missing from the binlog, so replicas never get them. |
sync_binlog = N | fsync every N groups: up to N groups lost. |
innodb_flush_log_at_trx_commit = 2 or 0 | prepare records reach disk only about once a second: a transaction can be in the binlog but unknown to InnoDB, so the source misses what replicas have. |
Demo: sync_binlog = 0, power loss: two clients get OK, the binlog is still in the OS page cache when the power fails, and recovery rolls both back. A plain mysqld crash would not lose them, since the OS page cache survives a process crash; it is the power loss or kernel crash that does. To see the other failure, commit S1 with sync_binlog = 0, then commit S2 and run its FLUSH stage: the redo fsync carries S1's commit record to disk while its binlog event is still in memory. After a crash, S1 is committed in InnoDB but missing from the binlog.
Replication itself, how replicas read this binlog and apply groups in parallel, is on MySQL Replication. The redo log's own pipeline (log buffer, LSN, checkpoints) is on InnoDB Redo Log. PostgreSQL has only one log, so its group commit is a single stage: see PostgreSQL WAL. The same prepare-then-decide idea across machines is the distributed two-phase commit.
The same prepare-then-commit idea across several databases: Two-Phase Commit.
What the page leaves out
Real thread timing: here a stage runs only when you press Next Stage, and a tick stands for the 1000 µs delay. Left out are the stage mutexes (LOCK_log, LOCK_sync, LOCK_commit), the redo of the UPDATE itself, undo and locks, binlog rotation (a restart opens a new binlog file), sync_binlog = N, binlog_order_commits = OFF, engines other than InnoDB, XA transactions from clients, semi-synchronous replication waiting in the commit, the logical clocks written for parallel replicas, and the binlog truncation of MySQL 8.0 recovery.