How MySQL Handles Transactions: WAL, MVCC, and Buffer Pool Explained

1. Underlying Principles of Transactions

MySQL implements transactions using Write‑Ahead Logging (WAL). All modifications are first written to a log (containing redo and undo information) before being applied to the data pages.

WAL allows the system to recover after a crash by replaying redo logs and using undo logs to roll back incomplete changes.

2. Transaction IDs

Each transaction that performs a DML operation receives a unique transaction ID. Read‑only transactions get an ID only when they first modify a temporary table; read‑write transactions get an ID when they first modify any table. The ID is generated from a global counter stored in memory and persisted periodically.

3. MVCC (Multi‑Version Concurrency Control)

MVCC uses hidden columns trx_id and roll_pointer in InnoDB’s clustered index records. The trx_id records the transaction that created the version, while roll_pointer links to the previous version stored in the undo log, forming a version chain.

Read operations create a ReadView that contains four fields (m_ids, min_trx_id, max_trx_id, creator_trx_id). A record version is visible to the current transaction if it satisfies the visibility rules defined by the ReadView.

Examples of isolation levels:

READ COMMITTED generates a new ReadView for each SELECT.

REPEATABLE READ generates a ReadView only on the first SELECT, reusing it for subsequent reads.

These rules prevent dirty reads, non‑repeatable reads, and largely avoid phantom reads.

4. InnoDB Buffer Pool

The Buffer Pool is a contiguous memory area allocated at server startup to cache disk pages. The default size is 128 MiB and can be changed with the innodb_buffer_pool_size parameter.

When a page is modified, it becomes a dirty page. Dirty pages are linked into a flush list and written back to disk by a background thread, reducing random I/O.

5. Redo Log

Redo logs record the changes needed to redo a transaction after a crash. They are written sequentially to a log buffer in memory and flushed to disk on log‑buffer half‑full, transaction commit, or periodic background flush.

Because redo logs are small and written sequentially, they provide efficient durability without flushing whole 16 KiB pages.

-- Create table
CREATE TABLE mvcc_test (
  id INT,
  name VARCHAR(100),
  domain VARCHAR(100),
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

-- Insert data
INSERT INTO mvcc_test VALUES (1, 'habit', 'demo mvcc');

6. Undo Log

Undo logs store the before‑image of each modification so that a transaction can be rolled back. Each DML operation generates an undo record that is linked via the roll_pointer.

During rollback, the engine uses the undo log to restore the original values, ensuring atomicity.

Images illustrating version chains and ReadView behavior are included below.