Parsing Delete Undo Log in MySQL 8.0.32 InnoDB

1. Preparation

Create a test table and insert sample data:

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;
ROLLBACK;

2. Read Undo Log

The Undo log reading process for the DELETE operation is identical to that for an INSERT rollback and is omitted for brevity.

3. Parse Undo Log

3.1 Parse Parameters

The DELETE Undo log lacks an "update field area" but contains a parameter area. Key parameters include:

type_flag : value 78 (derived from 14 | 64). 14 corresponds to TRX_UNDO_DEL_MARK_REC indicating a delete‑record Undo log; 64 corresponds to TRX_UNDO_MODIFY_BLOB which adds a lob_flag .

lob_flag : hard‑coded to 0x00 and not used.

undo_no : 0, the first Undo log in the Update Undo segment.

table_id : 1067, the identifier of the table containing the deleted row.

3.2 Parse Header and Hidden Fields

The header and hidden‑field area (offset [344, 358)) contains three attributes:

info_bits : 0 (bits 5‑8 of the first byte of the row header).

DB_TRX_ID : 2846 (transaction ID before deletion).

DB_ROLL_PTR : 36310272004456759 (pointer to the previous Undo log).

These values are stored in the undo_node.update structure and later copied back to the row during rollback.

3.3 Parse Primary Key Fields

The primary‑key field area (offset [358, 363)) holds:

primary_field_len : 4 bytes.

primary_field_value : 40 (the deleted row's id ).

The value is saved in undo_node.ref for later lookup.

3.4 Parse Secondary Index Fields

The secondary‑index field area (offset [363, 377)) records the values of all secondary index entries associated with the deleted row. This information is not needed for rollback but is used by the purge thread.

4. Find Primary Index Record

Using the primary‑key value (40) stored in undo_node.ref , the B+‑tree of the primary index is searched to locate the row id = 40 . The full row, including hidden fields DB_TRX_ID and DB_ROLL_PTR , is loaded into undo_node.row , and a pointer to the leaf node is kept for later updates.

5. Rollback Secondary Index Records

The table has one secondary index idx_i1 . The rollback proceeds by reconstructing the secondary‑index entry (i1 = 401, id = 40) from undo_node.row , locating it in idx_i1 , and clearing its delete flag, thereby restoring the index entry.

6. Rollback Primary Index Record

The saved pointer to the primary‑index leaf node is used to restore the row. The DB_TRX_ID and DB_ROLL_PTR values from undo_node.update are written back, and the header bits are reset, turning the previously marked‑deleted row back into a normal record.

7. Summary

Read one Undo log.

Parse the Undo log fields.

Locate the primary‑index record.

Rollback secondary‑index records using the values from the primary record.

Rollback the primary‑index record using the parsed header and hidden‑field values.