Understanding MySQL InnoDB Undo Log: Concepts, Storage, and Practical Scenarios

Hello everyone, I'm Wukong. This article introduces the MySQL InnoDB undo log , an essential log that stores the before‑image of rows so that transactions can be rolled back or read by other transactions.

Article Overview

We start with a brief overview of the undo log concept.

Concept

The undo log is a log used by the InnoDB engine. Before a row is modified, its original value (the before‑image) is saved in the undo log, enabling the system to restore the original value on error or to provide consistent reads for other transactions.

Purpose

The undo log serves two main purposes:

Transaction rollback (atomicity): When a ROLLBACK statement is issued, MySQL uses the undo log to revert data to the state before the transaction began.

Multi‑Version Concurrency Control (MVCC) – isolation: InnoDB uses the undo log to provide non‑locking consistent reads by reconstructing previous row versions from the log.

When Is an Undo Log Generated?

Four kinds of DML operations generate an undo log:

INSERT on a user table

UPDATE or DELETE on a user table

INSERT on a temporary table

UPDATE or DELETE on a temporary table

Where Is It Stored?

In MySQL versions prior to 5.6.3 the undo tablespace was stored together with the system tablespace in ibdata1 . Starting with MySQL 5.6.3, a separate undo tablespace can be created, but it must be defined at installation time and its space IDs must be contiguous.

MySQL 5.7 introduced online TRUNCATE of the undo tablespace, and MySQL 8.0 further improved it by default enabling two undo tablespaces and allowing non‑sequential space IDs.

Undo Tablespace Structure

The undo tablespace defines rollback segments that store undo logs. Below is the source definition of the undo tablespace structure (MySQL 8.0.13):

/** An undo::Tablespace object is used to easily convert between
undo_space_id and undo_space_num and to create the automatic file_name
and space name.  In addition, it is used in undo::Tablespaces to track
the trx_rseg_t objects in an Rsegs vector. So we do not allocate the
Rsegs vector for each object, only when requested by the constructor. */
struct Tablespace {
  // Undo Tablespace ID.
  space_id_t m_id;
  // Undo Tablespace number, from 1 to 127.
  space_id_t m_num;
  char *m_space_name;
  char *m_file_name;
  char *m_log_file_name;
  Rsegs *m_rsegs;
};

The Rsegs field holds the rollback segments ( trx_rseg_t ), whose definition is shown next.

/** The rollback segment memory object */
struct trx_rseg_t {
  ulint id;                     // segment id
  RsegMutex mutex;              // protects fields below
  space_id_t space_id;          // tablespace id
  page_no_t page_no;            // header page number
  page_size_t page_size;        // page size
  ulint max_size;               // max pages
  ulint curr_size;              // current pages
  // Lists for update undo logs
  UT_LIST_BASE_NODE_T(trx_undo_t) update_undo_list;
  UT_LIST_BASE_NODE_T(trx_undo_t) update_undo_cached;
  // Lists for insert undo logs
  UT_LIST_BASE_NODE_T(trx_undo_t) insert_undo_list;
  UT_LIST_BASE_NODE_T(trx_undo_t) insert_undo_cached;
  // Purge information
  page_no_t last_page_no;
  ulint last_offset;
  trx_id_t last_trx_no;
  ibool last_del_marks;
  std::atomic
trx_ref_count;
};

Undo Log Types

Undo logs are logical logs. For a DELETE , an INSERT record is stored; for an INSERT , a DELETE record is stored; for an UPDATE , the opposite update is stored. The two main types are insert undo log and update undo log .

Insert Undo Log Example

An insert undo log records the row ID so that a rollback can precisely delete the inserted row. The diagram below shows the fields stored for a composite primary key.

Update Undo Log Example

An update undo log stores the before‑image of the row, enabling the system to revert the change.

Practical Scenario: How a Transaction Rolls Back

When an INSERT is executed, the undo log records the inserted row ID; a rollback deletes that row. For a DELETE , the deleted row is stored so it can be re‑inserted on rollback. For an UPDATE , the previous values are stored and applied in reverse during rollback. The article shows chain diagrams illustrating how multiple transactions (A, B, C) create linked undo log entries, allowing each transaction to roll back to the appropriate previous state.

Brief Overview of MVCC

Undo logs are generated before a transaction starts and are retained after commit until a purge thread removes them. During an UPDATE , the original row is saved in the undo buffer; other transactions can read the consistent version from the buffer without waiting for the committing transaction.

Conclusion

The article started from the definition of the undo log , described how and where it is generated, explained its storage in separate undo tablespaces, detailed the internal structures of tablespaces and rollback segments, distinguished insert and update undo log types, and finally demonstrated practical rollback scenarios and MVCC behavior.

Source code referenced is from MySQL 8.0.13. Interested readers can obtain the source package by replying mysql8013 to the "Lianbian" subscription account, or browse the files directly in the indicated paths using an IDE such as IntelliJ IDEA.