Understanding InnoDB Rollback Segment Allocation for Temporary and Regular Tables in MySQL 8.0.32

Based on the MySQL 8.0.32 source code with the InnoDB storage engine, this article explains the memory structure of rollback segments and the allocation mechanisms for user temporary tables and regular tables.

1. Memory Structure

The rollback segment has a corresponding in‑memory structure with 16 attributes; the article focuses on the eight most important ones.

Four attributes are used when allocating an Undo segment:

update_undo_cached : cached list of Update Undo segments.

update_undo_list : list managing Update Undo segments; newly created segments are inserted at the head.

insert_undo_cached : cached list of Insert Undo segments.

insert_undo_list : list managing Insert Undo segments; newly created segments are inserted at the head.

Another four attributes are used by the background purge thread to track the earliest Undo logs that have not yet been purged:

last_page_no : page number of the Undo page containing the head of the log set.

last_offset : offset within that page.

last_trx_no : transaction ID that generated the log set.

last_del_marks : flag indicating whether the log set contains delete‑mark or overflow‑field‑modification records; if true, the purge thread will clean them.

2. Allocating Rollback Segments for User Temporary Tables

The location of the temporary‑table Undo tablespace is controlled by the system variable innodb_temp_data_file_path , defaulting to ibtmp1:12M:autoextend .

The number of rollback segments in the temporary‑table Undo tablespace is governed by innodb_rollback_segments , default 128, and their in‑memory structures are stored in the global tmp_rsegs array.

Allocation uses a counter temp_rseg_counter (initially 0). The steps are:

Read temp_rseg_counter and add 1 (without modifying the counter).

Take the sum modulo 128 to obtain an index into tmp_rsegs .

Increment temp_rseg_counter .

Fetch the rollback segment at the computed index.

The first allocation after MySQL startup selects the segment at index 1 (the second segment).

3. Allocating Rollback Segments for User Regular Tables

InnoDB supports up to 127 Undo tablespaces, each with up to 128 rollback segments, also controlled by innodb_rollback_segments (default 128).

Conceptually this forms a 127 × 128 matrix where rows represent Undo tablespaces and columns represent segment numbers. Allocation proceeds column‑by‑column: first all row 0 segments, then row 1, and so on, looping back after the last column.

The allocation algorithm uses a counter rseg_counter (initially 0) and follows these steps:

Read rseg_counter into current .

Increment rseg_counter .

Compute window = current % total_number_of_segments , where the total is 127 × 128.

Determine the target tablespace: space_slot = window % 127 .

Determine the segment within that tablespace: reg_slot = window / 127 (integer division).

Retrieve the Undo tablespace object using space_slot and then fetch the segment at reg_slot .

The article includes a code block showing the sequence of (tablespace, segment) pairs generated by this algorithm.

(0, 0), (1, 0), ..., (126, 0)
/* continue */ (0, 1), (1, 1), ..., (126, 1)
/* continue */ ...
(0, 127), (1, 127), ..., (126, 127)

4. Who Receives the Segments?

Both temporary‑table and regular‑table rollback segments are assigned to transactions and stored in the transaction object ( trx ) under the rsegs field.

The rsegs object has two members:

m_redo : rollback segment for regular tables.

m_noredo : rollback segment for temporary tables.

5. Summary

Temporary‑table Undo tablespace resides in a single file (default ibtmp1 ). Allocation starts from the second segment and cycles through the 128 segments repeatedly.

Regular‑table Undo tablespaces are multiple. Allocation cycles through segment 0 of every tablespace, then segment 1 of every tablespace, and so on, repeating the whole matrix after the last segment.

Discussion question: Why doesn’t InnoDB allocate regular‑table rollback segments by exhausting all 0‑127 segments of one tablespace before moving to the next?