Understanding Update Undo Log Format and Content in MySQL InnoDB

The article, based on MySQL 8.0.32 source code with the InnoDB storage engine, demonstrates how an UPDATE statement creates undo logs, what each field in the log represents, and how to locate the log using the hidden DB_ROLL_PTR field.

1. Preparation

Create a test table and insert sample data:

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;

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','男','流沙河');

Example UPDATE statement:

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

2. Update Undo Log Format

When an UPDATE modifies a row, InnoDB first generates an undo log for the primary‑key index record; secondary indexes are not logged. The undo log consists of many fields, each with a specific size and meaning (e.g., next_record_offset , type_flag , lob_flag , undo_no , table_id , info_bits , DB_TRX_ID , DB_ROLL_PTR , primary‑key length/value, number of updated fields, field positions, lengths, and values, as well as secondary‑index information).

The type_flag byte encodes the undo type and several flag bits (bits 1‑4: type, bits 5‑6: whether primary/secondary fields changed, bit 7: presence of lob_flag , bit 8: overflow field update).

The info_bits byte mirrors the record header flags before the update (e.g., min_rec_flag , deleted_flag , version_flag , instant_flag ).

3. Update Undo Log Content

For the example updating id = 35 , the undo log fields are:

next_record_offset : 420 (2 bytes, not compressed)

type_flag : 76 (1 byte) – combination of TRX_UNDO_UPD_EXIST_REC (12) and TRX_UNDO_MODIFY_BLOB (64)

lob_flag : 0x00

undo_no : 0

table_id : 1431

info_bits : 0

DB_TRX_ID : 2342

DB_ROLL_PTR : 36310272004391275

primary_field_len : 4, primary_field_value : 35

n_updated : 2

First updated field ( mobile ): position 4, length 11, old value 17900179000

Second updated field ( address ): position 6, length 9, old value 花果山

Secondary‑index information for id and name fields follows, including positions, lengths, and old values.

current_record_offset : 337 (2 bytes)

4. Update Undo Log Address

Each InnoDB record stores a hidden DB_ROLL_PTR (7 bytes) that points to the undo log. It is composed of four parts:

is_insert (1 bit)

undo_space_id (7 bits, 0‑127)

page_no (16 bits)

offset (16 bits)

The address is calculated as:

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

For the example: is_insert = 0 , undo_space_id = 2 , page_no = 135 , offset = 337 . The shell command:

# echo $((0 << 55 | 2 << 48 | 135 << 16 | 337))
# Output: 562949962269009

5. Summary

The article provides a detailed breakdown of the InnoDB update undo log structure, field meanings, and how to compute the log’s physical address, offering developers deep insight into MySQL transaction internals.