Understanding the Insert Undo Log Format in MySQL InnoDB

1. Preparation

Create a test table and insert a row to generate an Insert Undo log.

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Example insert statement:

INSERT INTO `t1` (`id`, `i1`) VALUES (50, 501);

2. Insert Undo Log Format

When a record is inserted, an Undo log is created for the primary‑key index entry. The log consists of several fields:

next_record_offset (2 bytes): offset of the next Undo log in the page.

undo_type (1 byte): type of the log, TRX_UNDO_INSERT_REC for inserts.

undo_no (1‑11 bytes, compressed): the log sequence number.

table_id (1‑11 bytes, compressed): the table identifier.

len (1‑5 bytes, compressed): length of the primary‑key value.

value : the actual primary‑key value (uncompressed).

current_record_offset : offset of this log within the Undo page.

If the primary key is composite, the log stores len_1, value_1, len_2, value_2, … in the defined order.

3. Insert Undo Log Content Example

The following diagram (omitted) shows a concrete log generated by the example INSERT. The field values are:

next_record_offset : 285 (fixed 2 bytes)

undo_type : 11 ( TRX_UNDO_INSERT_REC , fixed 1 byte)

undo_no : 0 (compressed to 1 byte)

table_id : 1412 (compressed to 2 bytes)

len : 4 (compressed to 1 byte)

value : 50 (type int unsigned , 4 bytes)

current_record_offset : 272 (fixed 2 bytes)

4. Insert Undo Log Address (DB_ROLL_PTR)

InnoDB adds a hidden 7‑byte field DB_ROLL_PTR to each row. It encodes the location of the Undo log and consists of four parts:

is_insert : 1 if the log is from an insert.

undo_space_id : ID of the Undo tablespace (0‑127).

page_no : page number of the Undo page.

offset : offset within that page.

The address is calculated as:

is_insert << 55 | undo_space_id << 48 | page_no << 16 | offset

For the example row:

is_insert = 1

undo_space_id = 2

page_no = 573

offset = 272

Shell command to compute the value:

# Output: 36591747009937680
echo $((1 << 55 | 2 << 48 | 573 << 16 | 272))

5. Summary

No additional summary is provided.