Unlock MySQL’s Secrets: Redo Log, Undo Log, Binlog & Transaction Mechanics

Redo Log

Concept

Redo log is an InnoDB‑specific log that records the modifications a transaction makes to the database. Compared with flushing all dirty pages at commit, writing only the redo log offers small space usage, high memory utilization, and sequential disk writes for better performance.

Diagram

Redo Log Block

Redo log is stored in 512‑byte blocks, each consisting of a header, body, and trailer.

Header : stores management information.

Body : the actual redo log data.

Trailer : additional management information.

Header content illustration:

Overall redo log write flow:

Undo Log

Concept

Undo log enables transaction rollback by recording the original state of modified rows, such as primary‑key values, so that changes can be undone if a transaction aborts.

Diagram

Binlog

Concept

Binlog (binary log) is an archive log that records logical operations on tables—what was changed, on which table, and the resulting state. Unlike the physical redo log, binlog is logical and not specific to InnoDB.

Differences between Binlog and Redo Log

Binlog is server‑wide; redo log is InnoDB‑only.

Binlog is logical; redo log is physical.

Binlog lacks idempotence; redo log is idempotent.

Binlog writes are appended; redo log writes are circular and may overwrite.

Binlog is used for replication and recovery; redo log is used for crash recovery of committed transactions.

Transaction Mechanics

Dirty Read / Write

Dirty write occurs when a transaction modifies data that another uncommitted transaction has also modified. Dirty read occurs when a transaction reads data modified by an uncommitted transaction.

Non‑Repeatable Read

Occurs when a transaction reads a row, another transaction updates and commits it, and the first transaction reads the row again and sees a different value.

Phantom Read

Occurs when a transaction re‑executes a query and sees newly inserted rows that were not present before.

Isolation Levels

SQL standard defines four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. MySQL’s default is REPEATABLE READ, which prevents phantom reads using undo‑log version chains.

Undo Log Version Chain

Concept

Each row stores hidden fields trx_id (the ID of the last transaction that modified the row) and roll_pointer (a pointer to the previous undo‑log entry). When a transaction updates a row, an undo‑log record of the prior value is created and linked via roll_pointer .

Diagram

MVCC Mechanism

MySQL implements Multi‑Version Concurrency Control (MVCC) using undo logs and version chains, allowing consistent reads without locking.

Transaction Flow Diagrams