MySQL Core Module Deep Dive #11: What Happens When InnoDB Commits a Transaction?

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

1. About Caching Undo Segments

InnoDB can cache insert or update undo segments during transaction commit to improve allocation efficiency. The segment is cached only when two conditions are met:

There is exactly one undo page in the segment.

The used space on that page is less than three‑quarters of the page size (e.g., < 12KB for a 16KB page).

If the conditions are satisfied, the segment is linked to the head of the insert_undo_cached or update_undo_cached list of the rollback segment.

2. InnoDB Commit Transaction

During the two‑phase commit, the flush sub‑stage writes redo logs generated before the prepare stage to disk and also writes binlog records. The sync sub‑stage may force the binlog to be flushed to the OS depending on sync_binlog . After these two sub‑stages, the transaction is still in the TRX_STATE_PREPARED state.

The commit sub‑stage actually commits the InnoDB transaction and performs the following actions:

Modify the state of the insert undo segment.

Generate a transaction commit number (stored in trx->no ).

Modify the state of the update undo segment.

Add the update undo log group to the rollback segment’s history list .

Set the transaction state to TRX_STATE_COMMITTED_IN_MEMORY .

Release locks held by the transaction.

Re‑initialise the transaction object for future use.

2.1 Modify Insert Undo Segment State

Depending on whether the insert undo segment meets the caching conditions, its state is set to TRX_UNDO_CACHED or TRX_UNDO_TO_FREE . After commit, InnoDB either caches the segment or frees the associated undo pages.

2.2 Generate Transaction Commit Number

The commit number is the no field of the transaction object, often referenced as trx->no . It is generated by calling trx_sys_allocate_trx_no() , which internally uses trx_sys_allocate_trx_id_or_no() to fetch and increment trx_sys->next_trx_id_or_no .

static inline bool trx_add_to_serialisation_list(trx_t *trx) {
    ...
    trx->no = trx_sys_allocate_trx_no();
    ...
}

inline trx_id_t trx_sys_allocate_trx_no() {
    ut_ad(trx_sys_serialisation_mutex_own());
    return trx_sys_allocate_trx_id_or_no();
}

The same counter is used for both transaction IDs and commit numbers, ensuring they never collide.

2.3 Modify Update Undo Segment State

If the update undo segment meets the caching criteria, its state becomes TRX_UNDO_CACHED ; otherwise it becomes TRX_UNDO_TO_PURGE . The state determines whether the segment will be cached for reuse or later purged.

2.4 Add Undo Log Group to History List

After the update undo segment state is changed, its undo log group is linked into the rollback segment’s history list . The purge thread later scans this list to clean up obsolete undo logs, using the stored transaction commit number to decide eligibility.

3. Transaction Commit Completion

Once all the above steps finish, InnoDB sets the transaction state to TRX_STATE_COMMITTED_IN_MEMORY . New transactions can now see the committed changes, while deleted rows remain invisible. Locks are released, and the insert undo segment is either cached or freed based on its final state.

Redo logs generated in the commit sub‑stage are not forced to disk by InnoDB; the operating system decides when to flush them. This is safe because the redo logs are no longer needed to determine transaction outcome after the commit state is persisted.

4. Re‑initialise Transaction Object

The transaction object’s state is reset to TRX_STATE_NOT_STARTED and the object is kept for reuse by the same connection, rather than being returned to the transaction pool.

5. Summary

Committing an InnoDB transaction involves three major actions: (1) updating the state of all associated undo segments, (2) changing the transaction object’s state to indicate commit, and (3) linking undo log groups to the history list for later purge. These steps ensure durability and allow fast recovery after a crash.