InnoDB Commit Phase: Flush, Sync, and Group Commit Explained

Author: Cao Shengchun, technical expert at iKangsheng, author of the public account "Yi Shu Yi Xi", focusing on MySQL and OceanBase source code.

Source: iKangsheng Open‑Source Community, original content, please contact for reuse.

Table of Contents

1. The Flush Operation

2. Commit Phase

3. Group Commit

4. Queues and Mutexes

5. Summary

1. The Flush Operation

The operating system provides a buffer for files called the page cache .

When a program writes data to a disk file, it first writes to the page cache, and the OS later flushes the page cache contents to the actual disk file.

This operation of writing the page cache to disk is commonly referred to as flushing .

There are two ways to flush:

Asynchronous flushing by the OS background thread.

Synchronous flushing triggered actively by the program.

2. Commit Phase

The second phase of the two‑phase commit, the commit phase, is divided into three sub‑stages.

Flush sub‑stage performs two tasks:

Task 1 : Triggers the OS to flush the redo logs generated up to and including the prepare stage.

During transaction execution, redo logs generated by data changes (INSERT/UPDATE/DELETE) and redo logs generated by the prepare stage that modify the undo segment are first written to the page cache, then flushed to disk by the OS.

The exact timing of this flush is nondeterministic; InnoDB may proactively trigger an immediate flush when needed.

In the previous article we noted that the prepare stage does not trigger a flush; that work is deferred to the flush sub‑stage.

Task 2 : Writes the binlog generated during transaction execution into the binlog file. This write also goes through the page cache, and the OS decides when to flush it.

Sync sub‑stage decides, based on the system variable sync_binlog , whether to force an immediate flush of the binlog page cache.

Commit sub‑stage completes the InnoDB transaction commit.

3. Group Commit

The flush sub‑stage may trigger redo‑log flushing, and the sync sub‑stage may trigger binlog flushing; both involve disk I/O.

In typical OLTP scenarios, a transaction usually changes only a small amount of data, producing relatively few redo and binlog entries. We call such transactions small transactions .

Because multiple small transactions can share the same OS page for their redo logs, flushing each transaction individually would cause the same page to be written to disk many times.

When the database is idle, repeated flushing of the same page is harmless, but under heavy load, flushing the same page thousands of times is inefficient.

For example, if 10,000 small transactions are submitted simultaneously and every ten transactions share one OS page, the system would need to flush 1,000 pages 10,000 times.

By grouping these transactions and flushing the shared page only once, the number of flushes drops from 10,000 to 1,000, improving efficiency tenfold.

The same issue exists for binlog writes. To solve this, InnoDB introduces group commit , which batches multiple transactions together before flushing.

4. Queues and Mutexes

Group commit works by accumulating a batch of transactions in a queue . Each sub‑stage (flush, sync, commit) has its own queue.

Each queue selects a leader thread (the first thread that enters the queue) to manage the work; subsequent threads become followers.

In the code, the leader is called leader and the followers are called follower .

The leader has the privilege to do all the work for itself and its followers, while followers remain idle.

To prevent two leaders from working simultaneously and causing corruption, InnoDB uses mutexes:

LOCK_log for the flush sub‑stage.

LOCK_sync for the sync sub‑stage.

LOCK_commit for the commit sub‑stage.

5. Summary

The commit phase of the two‑phase commit consists of three sub‑stages: flush, sync, and commit.

The flush sub‑stage flushes redo logs generated up to the prepare stage and writes binlog entries to the binlog file.

The sync sub‑stage decides, based on sync_binlog , whether to flush the binlog to disk.

To avoid each transaction triggering redundant page flushes, InnoDB introduces group commit.

To avoid multiple leaders working concurrently in any sub‑stage, InnoDB also introduces three mutexes: LOCK_log , LOCK_sync , and LOCK_commit .

Question for readers: If you have any questions about this content, feel free to leave a comment.

Next issue preview: MySQL Core Module Reveal | Episode 09 | Two‑Phase Commit (3) Flush, Sync, Commit Sub‑Stages.