Skip to content

CONCEPT Cited by 1 source

MySQL undo log

The undo log is InnoDB's mechanism for reconstructing previous versions of a row on demand. Unlike Postgres — which keeps multiple row versions on the heap and garbages them later (see concepts/xmin-xmax-row-versioning) — MySQL overwrites rows in place when they are modified, and maintains an append-only log of the modifications so that concurrent transactions still needing the old value can reconstruct it.

Row metadata

Every InnoDB row carries two hidden metadata columns (Source: sources/2026-02-22-planetscale-what-is-a-database-transaction):

  • xid — the transaction ID that most recently updated the row.
  • ptr — a reference to the most recent entry in the undo log for this row.

Together xid + ptr tell any concurrent transaction where to look if the current on-heap value is too new for it.

Version reconstruction

When transaction A overwrites a row that transaction B still needs to see in its consistent snapshot, B follows the row's ptr into the undo log and reconstructs the pre-A value. The undo log is a chain: multiple undo records can exist for the same row simultaneously (if it has been modified by many recent transactions), and MySQL selects the correct historical version by comparing transaction identifiers against the reader's snapshot.

Why less maintenance than Postgres

Because row modifications overwrite in place, MySQL does not accumulate thousands of duplicate rows on the main table heap the way Postgres does, and therefore does not need VACUUM FULL. The undo log is naturally ephemeral: an undo record can be discarded once no running transaction needs the pre-record value, which is typically much sooner than Postgres's xmin/xmax versions are safe to reclaim.

The flipside: readers on long-running transactions pay a log traversal rather than a simple disk read — old versions must be reconstructed, not just looked up on the heap. This makes very long-lived transactions against a hot row expensive in MySQL the same way they make VACUUM ineffective in Postgres: both engines' MVCC machinery assumes transactions are short.

Dual role: undo and rollback

The undo log also serves the transaction-rollback use case: if a transaction aborts before commit, InnoDB walks the undo log entries it generated and replays them backwards to revert its writes. Consistent reads (MVCC) and rollback thus share the same substrate.

Seen in

Last updated · 517 distilled / 1,221 read