How InnoDB Performs Undo Log Rollback and Record Deletion

1. Preparation

Create a test table:

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;

Start a transaction and insert a row:

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

2. Reading Undo Logs

Each transaction has an rsegs object that holds the rollback segments allocated to it. The two relevant attributes are:

m_redo : rollback segment for undo logs generated by changes to regular tables.

m_noredo : rollback segment for undo logs generated by changes to temporary tables.

To roll back a transaction, InnoDB first reads the undo logs, then uses them to revert the data changes.

The selection of a rollback segment follows a simple rule: if the regular‑table segment still contains un‑rolled‑back logs, it is chosen; otherwise the temporary‑table segment is used.

Within a chosen rollback segment, an undo segment can be in one of three states:

Only an Insert undo segment is allocated.

Only an Update undo segment is allocated.

Both Insert and Update undo segments are allocated.

Each undo segment stores three key fields that point to the last un‑rolled‑back undo log:

top_page_no : page number of the undo log.

top_offset : offset of the undo log within the page.

top_undo_no : undo log sequence number.

The algorithm to locate the correct undo segment is:

If no Insert undo segment exists or it is fully rolled back, choose the Update undo segment; otherwise go to step 2.

If no Update undo segment exists or it is fully rolled back, choose the Insert undo segment; otherwise go to step 3.

Compare top_undo_no of the Insert and Update segments and pick the one with the larger value (newer log). The undo logs are therefore processed from newest to oldest.

After the undo segment is identified, InnoDB fetches the undo page using top_page_no and obtains a pointer to the log via top_offset . The pointer is then used to read the log and, after processing, the three fields are updated to point to the previous log. When no previous log exists, the segment’s empty flag is set to true.

3. Parsing Undo Logs

For an INSERT undo log, the relevant fields are:

undo_type (value 11 → TRX_UNDO_INSERT_REC )

undo_no (compressed, needs decompression)

table_id (compressed, needs decompression, used to open the table)

After extracting these parameters, the primary‑key field length is read (compressed in the log) and then the actual primary‑key value is retrieved. In the example, the primary key id has length 4 and value 50.

4. Constructing the Primary‑Key Index Record

Using the primary‑key value <id = 50> , InnoDB searches the primary‑key B+‑tree to locate the full record. The record’s fields (including hidden InnoDB fields) are stored in an undo_node object’s row attribute, and a pointer to the primary‑key entry is saved for later deletion.

For table t1 , the row contains:

id = 50

i1 = 501

DB_TRX_ID = 219404

DB_ROLL_PTR = 36310272026738960

5. Deleting the Record

Rollback deletes a row in two steps: first remove the secondary‑index entry, then remove the primary‑key entry.

5.1 Delete Secondary‑Index Record

The secondary index idx_i1 is searched using the i1 value (501) obtained from undo_node.row . The matching entry is then physically removed from the secondary‑index B+‑tree.

5.2 Delete Primary‑Key Record

The pointer saved during step 4 is used to directly delete the primary‑key entry <id = 50> from the primary‑key B+‑tree, without an additional lookup.

6. Summary

The rollback of a single undo log follows these steps:

Select the appropriate rollback segment and undo segment containing the last un‑rolled‑back log.

Read the undo log and decode its type, sequence number, table ID, and primary‑key value.

Locate the full record in the primary‑key B+‑tree using the primary‑key value.

Derive the secondary‑index field values from the full record.

For each secondary index, locate and physically delete the corresponding entry.

Physically delete the primary‑key entry using the saved pointer.

If a table has multiple secondary indexes, steps 4–6 are repeated for each index.