Understanding the Delete Undo Log Format in MySQL 8.0 InnoDB

The article is based on MySQL 8.0.32 source code with the InnoDB storage engine and walks through the creation of a test table, insertion of sample data, and a DELETE statement that removes the row with id = 40 .

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;

INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);

DELETE FROM t1 WHERE id = 40;

When a DELETE statement is executed, InnoDB first marks the primary‑key index record and then the secondary‑index records as deleted. The transaction’s hidden fields DB_TRX_ID and DB_ROLL_PTR are updated, making the operation effectively an update.

The generated Undo log for a Delete has the same overall format as an Update Undo log, except that the region for updated column values is omitted. The log consists of several fields, each with a specific size and meaning:

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

type_flag (1 byte): identifies the log type and contains flag bits.

lob_flag (1 byte): hard‑coded to 0x00 for Delete logs.

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

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

info_bits (1 byte): bits from the record header indicating status flags.

DB_TRX_ID (5‑9 bytes, compressed): transaction ID before the Delete.

DB_ROLL_PTR (5‑9 bytes, compressed): pointer to the previous version of the record.

primary_field_len (1‑5 bytes, compressed): length of the primary‑key field.

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

index_field_bytes (2 bytes): total size of the secondary‑index field region.

index_field_pos (1‑5 bytes, compressed): position of the secondary‑index field in the table.

index_field_len (1‑5 bytes, compressed): length of the secondary‑index field value.

index_field_value : the secondary‑index field value.

current_record_offset : offset of this Undo log within the page.

The type_flag byte is divided into four parts: bits 0‑3 indicate the Undo type, bits 4‑5 are used only by Update, bit 6 signals the presence of lob_flag , and bit 7 is also Update‑specific.

The info_bits byte stores flags from the record header, such as deleted_flag (bit 5) which marks the record as logically deleted until the purge thread removes it.

To locate the Undo log, InnoDB uses the hidden field DB_ROLL_PTR , a 7‑byte value composed of four sub‑fields:

is_insert : 1 bit indicating whether the log originates from an INSERT.

undo_space_id : 7 bits identifying the Undo tablespace (0‑127).

page_no : 16 bits for the page number within the Undo tablespace.

offset : 16 bits for the offset inside the page.

The composition formula is:

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

For the example DELETE, the values are is_insert = 0 , undo_space_id = 1 , page_no = 133 , and offset = 337 . Executing the shell command below yields the DB_ROLL_PTR:

# echo $((0 << 55 | 1 << 48 | 133 << 16 | 337))
# Output: 281474985427281

In summary, the Delete Undo log shares the same structure as the Update Undo log, with the only difference being the absence of the updated‑field region, allowing developers to distinguish between the two by inspecting the log layout.