Parsing and Rolling Back Update Undo Logs in MySQL 8.0.32 (InnoDB)

This article is based on the MySQL 8.0.32 source code with the InnoDB storage engine.

1. Preparation

First a test table t6 is created:

CREATE TABLE `t6` (
  `id` int unsigned NOT NULL,
  `name` varchar(32) DEFAULT '',
  `mobile` char(11) DEFAULT '',
  `sex` enum('男','女','未填写') DEFAULT NULL,
  `address` varchar(128) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Test data is inserted:

INSERT INTO `t6` (`id`,`name`,`mobile`,`sex`,`address`) VALUES
(1,'唐僧','12800128000','男','东土大唐'),
(5,'西梁女王','11800118000','女','女儿国'),
(10,'张三','13800138000','男','张家口'),
(15,'李四','13900139000','男','李家庄'),
(20,'王五','15900159000','男','王家大院'),
(25,'紫霞仙子','19900199000','女','九龙城'),
(30,'猪八戒','16900169000','男','高老庄'),
(35,'孙悟空','17900179000','男','花果山'),
(40,'沙和尚','18900189000','男','流沙河');

The record with id = 35 is then updated:

UPDATE `t6`
SET `mobile` = '17988179888', `address` = '水帘洞'
WHERE `id` = 35;

Finally a ROLLBACK is issued.

2. Reading the Undo Log

The Undo log generated by the UPDATE is read in the same way as the Undo log for an INSERT; the article does not repeat the procedure.

3. Parsing the Undo Log

3.1 Parsing Parameters

The Undo log for an UPDATE contains four attributes, adding a lob_flag (hard‑coded to 0x00 ) and a type_flag that combines the original type with additional flags. The type_flag value 76 is derived from 12 | 64 , where 12 corresponds to TRX_UNDO_UPD_EXIST_REC and 64 to TRX_UNDO_MODIFY_BLOB .

type_flag : 76 (12 | 64)

lob_flag : 0x00 (unused)

undo_no : 0 (first Undo record in the segment)

table_id : 1431 (the table ID of t6 )

3.2 Parsing Header and Hidden Fields

info_bits : 0

DB_TRX_ID : 2342 (transaction ID before the UPDATE)

DB_ROLL_PTR : 36310272004391275 (rollback pointer before the UPDATE)

3.3 Parsing Primary Key Field

primary_field_len : 4 (bytes)

primary_field_value : 35

3.4 Parsing Updated Fields

The region stores the positions, lengths, and previous values of the updated columns mobile and address . The number of updated fields ( n_updated ) is followed by a series of <upd_field_pos, upd_field_len, upd_field_value> tuples.

3.5 Parsing Secondary Index Fields

This area records the original values of all secondary index fields, including the primary key, but they are not needed for the rollback of the primary record; they are used only by the purge thread.

4. Finding the Primary Index Record

The primary key value 35 (saved in undo_node.ref ) is used to search the B+‑tree of the primary index and locate the record id = 35 . The full row is loaded into undo_node.row and a pointer to the leaf node is kept for later updates.

5. Constructing the Rollback Record

The rollback record represents the state of the row before the UPDATE. It is built by merging the original values stored in undo_node.row (post‑update values) with the previous values stored in undo_node.update (the mobile , address , DB_TRX_ID , and DB_ROLL_PTR fields). The resulting undo_row contains:

id = 35

name = 孙悟空

mobile = 17900179000

sex = 男

address = 花果山

DB_TRX_ID = 2342

DB_ROLL_PTR = 36310272004391275

6. Rolling Back Secondary Index Records

For the UPDATE, the secondary index idx_address was changed from address = 花果山 to address = 水帘洞 . The rollback process deletes the new entry (address = 水帘洞, id = 35) and re‑inserts the original entry (address = 花果山, id = 35) using the values stored in undo_node.row and undo_node.undo_row .

7. Rolling Back the Primary Index Record

The primary index record is restored directly using the pointer saved earlier. The fields mobile , address , DB_TRX_ID , and DB_ROLL_PTR are set back to their original values, and the header bits are restored.

8. Summary

Read one Undo log.

Parse the Undo log (parameters, header, hidden fields, primary key, updated fields, secondary index fields).

Locate the primary index record.

Construct the rollback record ( undo_row ).

Rollback secondary index records using the stored values.

Rollback the primary index record using the header, hidden fields, and updated field values.