Understanding InnoDB Rollback and UNDO Log Architecture in MySQL

The author, Wang Zhufeng, a MySQL database expert at Qunar.com, shares the final part of a series on MySQL log implementation, focusing on the rollback mechanism and UNDO log architecture.

In InnoDB, rollback is performed after REDO recovery; the function recv_recovery_from_checkpoint_start invokes recv_recovery_from_checkpoint_finish to apply UNDO operations after physical changes are persisted.

Rollback segments are managed in the transaction system header stored on page 6 (page 5 in zero‑based indexing). The layout includes several macro definitions that describe the offsets of fields such as TRX_SYS_TRX_ID_STORE, TRX_SYS_FSEG_HEADER, and TRX_SYS_RSEGS. The relevant code is:

/** Transaction system header */
/*------------------------------------------------------------- @{ */
#define TRX_SYS_TRX_ID_STORE    0   /*!< the maximum trx id or trx ... */
#define TRX_SYS_FSEG_HEADER     8   /*!< segment header for the ... */
#define TRX_SYS_RSEGS          (8+FSEG_HEADER_SIZE)
/*!< the start of the array of rollback segment specification slots */

These macros define the storage of five key pieces of information for each rollback segment: TRX_SYS_TRX_ID_STORE: stores the maximum transaction ID, written in multiples of WRITE_MARGIN (256) to guarantee uniqueness. TRX_SYS_FSEG_HEADER: holds the file‑segment information for the transaction. TRX_SYS_RSEGS: an array of 128 entries, each representing a rollback segment. TRX_RSEG_MAX_SIZE, TRX_RSEG_HISTORY_SIZE, TRX_RSEG_HISTORY, TRX_RSEG_FSEG_HEADER, and TRX_RSEG_UNDO_SLOTS further describe the size, history list, and slot layout of each segment.

Each rollback segment can contain up to 1024 undo slots, giving InnoDB a total capacity of 128 × 1024 = 131 072 undo slots. When a transaction starts, it is assigned an rseg from the array; during its lifetime the transaction may use one or two slots (INSERT and UPDATE) depending on the operations performed.

If no free slot is found, the transaction aborts with error 1637 (“Too many active concurrent transactions”).

The article then details the three‑level structure of an undo record:

Undo page header – defines page type, start offset, free offset, and list node:

/** Transaction undo log page header offsets */
#define TRX_UNDO_PAGE_TYPE   0   /*!< TRX_UNDO_INSERT or TRX_UNDO_UPDATE */
#define TRX_UNDO_PAGE_START  2   /*!< Byte offset where the undo log records for the LATEST transaction start */
#define TRX_UNDO_PAGE_FREE   4   /*!< On each page of the undo log this field contains the byte offset of the first free byte */
#define TRX_UNDO_PAGE_NODE   6   /*!< The file list node in the chain of undo log pages */

Undo segment header – stores the segment state, last log offset, file‑segment header, and page list base node:

/** Undo log segment header */
#define TRX_UNDO_STATE      0   /*!< TRX_UNDO_ACTIVE, ... */
#define TRX_UNDO_LAST_LOG   2   /*!< Offset of the last undo log header on the segment header page */
#define TRX_UNDO_FSEG_HEADER 4   /*!< Header for the file segment which the undo log segment occupies */
#define TRX_UNDO_PAGE_LIST  (4+FSEG_HEADER_SIZE)   /*!< Base node for the list of pages in the undo log segment */

Undo log header – contains transaction identifiers, delete marks, offsets to the next/previous log, and optional XA/D​DL information:

/** The undo log header. There can be several undo log headers on the first page of an update undo log segment. */
#define TRX_UNDO_TRX_ID     0   /*!< Transaction id */
#define TRX_UNDO_TRX_NO     8   /*!< Transaction number of the transaction */
#define TRX_UNDO_DEL_MARKS  16   /*!< Defined only in an update undo log: TRUE if the transaction may have done delete markings */
#define TRX_UNDO_LOG_START  18   /*!< Offset of the first undo log record on this page */
#define TRX_UNDO_XID_EXISTS 20   /*!< TRUE if undo log header includes X/Open XA transaction identification */
#define TRX_UNDO_DICT_TRANS 21   /*!< TRUE if the transaction is a table create, index create, or drop transaction */
#define TRX_UNDO_TABLE_ID   22   /*!< Id of the table if the preceding field is TRUE */
#define TRX_UNDO_NEXT_LOG   30   /*!< Offset of the next undo log header on this page, 0 if none */
#define TRX_UNDO_PREV_LOG   32   /*!< Offset of the previous undo log header on this page, 0 if none */
#define TRX_UNDO_HISTORY_NODE 34 /*!< If the log is put to the history list, the file list node is here */

The article also enumerates the four UNDO record types (INSERT_REC, UPD_EXIST_REC, UPD_DEL_REC, DEL_MARK_REC) and explains that each record stores the table ID, primary key, and, for updates, the old and new column values. The INSERT_REC format is illustrated with a diagram (Figure 11.13) showing a leading and trailing two‑byte pointer that links records into a doubly‑linked list within a page.

During database startup, after REDO recovery, InnoDB scans all possible rollback segments via trx_sys_init_at_db_start and builds a list of active UNDO segments. It then sorts them by transaction number (largest to smallest) and invokes trx_rollback_or_clean_recovered (unless srv_force_recovery is set high enough to skip rollback). The rollback process walks each UNDO segment, reads the page and log headers, and applies the appropriate reverse operation based on the record type.

Finally, the article concludes that understanding these internal structures—transaction system header, rollback segment layout, undo page/segment/log headers, and the recovery flow—provides a solid foundation for MySQL (InnoDB) operation, troubleshooting, and performance tuning.