BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 984 of 1,9539. MySQL and MariaDB EcosystemsFree lesson

Redo Log Mechanics

Trace the lifecycle of a transaction from memory to the physical recovery log.

In this lesson, you will learn how a transaction moves from an volatile memory state to a hardened, crash-resilient physical redo log file.

The Memory Stage

When you execute an UPDATE or INSERT statement in MySQL, InnoDB does not immediately write the modified data page to the permanent disk files (the tablespace). Doing so would be incredibly slow because of random I/O. Instead, InnoDB modifies the data page inside the Buffer Pool (the memory cache). Once a change is made in memory, it is marked as a "dirty page."

The Log Buffer

Before that transaction is considered "committed," InnoDB must ensure the changes are recoverable if the power fails. To achieve this, it writes the change into the Log Buffer, which is a small memory area.

When you issue a COMMIT command, InnoDB triggers an operation to flush the contents of the Log Buffer into the Redo Log files on disk. This is a sequential append operation—much faster than writing random data pages—which satisfies the Write-Ahead Logging (WAL) principle.

The Lifecycle Trace

You can visualize the flow of a transaction like this:

  1. Transaction Start: Changes are applied to pages in the Buffer Pool.
  2. Log Entry: A record describing the change (the redo log record) is copied to the Log Buffer.
  3. Commit: Upon COMMIT, the Log Buffer is flushed to the physical Redo Log on disk.
  4. Checkpointing: Periodically, the background thread flushes the "dirty pages" from the Buffer Pool to the actual tablespace files on disk. Once the data pages are safe on disk, the space in the redo logs can be reused.
-- Conceptual flow:
-- 1. SQL executed -> Memory (Buffer Pool)
-- 2. Changes recorded -> Log Buffer
-- 3. COMMIT -> Sequential flush to physical Redo Log
-- 4. Checkpoint -> Dirty pages written to disk, Redo Log cleared

Because the redo log is written sequentially, the database can rapidly replay these logs during recovery after a crash, bringing the data pages in the tablespace back to their last consistent state.

Key Takeaway: The redo log acts as a sequential safety net that ensures transaction durability by flushing log entries to disk before the slower process of updating the actual data pages occurs.