MySQL Transaction Internals Explained: How Locks, Undo Logs, MVCC, and Isolation Levels Work in InnoDB

This article focuses on InnoDB transaction internals and systematically explains how locks, Undo Log, MVCC, and isolation levels work together to solve concurrency consistency, failure rollback, and read/write conflict problems. Core keywords: MySQL transactions, MVCC, Undo Log.

Technical Specifications Snapshot

Parameter	Description
Database	MySQL / InnoDB
Language	SQL, C/C++ (implementation semantics)
Protocol	MySQL Client/Server, TCP
Star Count	Not provided in the source content
Core Dependencies	Row Locks, Undo Log, Redo Log, MVCC, ReadView, Buffer Pool

AI Visual Insight: This diagram presents the knowledge topology of the MySQL transaction mechanism. It typically connects the lock system, Undo/Redo logs, MVCC, isolation levels, and transaction control statements into a causal chain, making it useful for building a global mental model.

Transactions solve the indivisibility of business operations first

A transaction is not just another name for a single SQL statement. It defines the execution boundary for multiple logically related SQL statements. Business workflows such as course enrollment, money transfer, and order placement all depend on the semantic guarantee of “either all succeed or all fail.”

A single SQL statement is atomic, but multiple SQL statements do not become atomic automatically when combined. Thread switching, connection interruption, and client crashes can all leave a business process in an intermediate state where only half of the work has completed.

BEGIN;
SELECT remain FROM course WHERE course_id = 101; -- Read the current remaining capacity
INSERT INTO student_course(student_id, course_id) VALUES (1001, 101); -- Insert the enrollment record
UPDATE course SET remain = remain - 1 WHERE course_id = 101; -- Decrease the available seats
COMMIT;

This transaction binds three SQL statements into one business unit and avoids a dirty state such as “enrollment succeeded but the seat count was not decremented.”

Locks only solve concurrent contention, not execution interruption

Locks are good at handling conflicts where multiple threads modify the same data at the same time, but they cannot handle failures where a single transaction crashes halfway through execution. The former requires mutual exclusion; the latter requires rollback.

Therefore, a transaction is essentially a higher-level abstraction built on top of locks: locks provide isolation, Undo Log provides rollback, and Redo Log provides durability after commit.

InnoDB balances correctness and concurrency through a two-layer lock model

InnoDB does not rely on only one kind of lock. It maintains both Page Latches and Row Locks. They protect different objects, have different lifetimes, and serve different design goals.

A Page Latch protects physical structures inside a page, such as the Page Header, page directory, and free list. It is held for a very short time and covers only the instant when the page structure is being modified.

A Row Lock protects record access at the transaction level. It serves business logic and must be held from the moment it is acquired until the transaction ends.

struct lock_t {
    trx_t* trx;              // The transaction that owns this lock
    uint32_t type_mode;      // Lock mode and type
    bitmap_t bitmap[];       // Marks which records in the page are locked
};

This kind of lock object uses a bitmap to compress multiple record locks of the same transaction, on the same page, with the same lock type, which significantly reduces memory overhead.

Row locks are not stored per record; they are managed in page-level groups

InnoDB uses a global lock hash table keyed by (space_id, page_no) and places lock objects for the same page into the same bucket. The transaction object also maintains a reverse-linked list of all locks it currently holds.

This design supports conflict detection from the record perspective while also allowing fast batch release of all lock objects when a transaction commits or rolls back.

AI Visual Insight: This diagram shows the page-granularity lock hash organization: a global hash bucket locates a page, the bucket-level linked list attaches multiple transactions’ lock objects, and each object uses a bitmap to point to specific heap_no entries. This matches InnoDB’s efficient lock conflict detection path.

Undo Log is the real safety net behind atomicity

Before modifying a record, a transaction does not write directly to the data page. It first writes an undo record that describes how to reverse the modification. Only after the old state is safely backed up does it actually update the record in the page.

For an UPDATE, the undo record stores the old value. For an INSERT, rollback deletes the new record. For a DELETE, rollback usually clears the delete mark and revives the record.

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1; -- Generate undo before debiting
UPDATE account SET balance = balance + 100 WHERE id = 2; -- Generate undo before crediting
ROLLBACK; -- Undo the preceding changes in reverse order

Undo Log gives a transaction the ability to restore the original state when execution fails. That is the engineering implementation of atomicity.

Undo Pages, Undo Segments, and Rollback Segments organize rollback data together

Undo records are physically stored in undo pages. Multiple pages form an undo segment, and a rollback segment manages multiple undo segments through a slot array.

AI Visual Insight: This diagram highlights both the physically compact write pattern of Undo Records and the logically chained backtracking model. Both transaction rollback and MVCC depend on page-number-plus-offset pointers to trace old versions, which is the core of InnoDB’s time-based recovery capability.

In addition, INSERT Undo and UPDATE Undo are managed separately, because the former can be reclaimed more quickly after commit, while the latter must continue serving as historical versions for MVCC.

MVCC removes the need for complete read/write mutual exclusion through version chains

If every write blocked all reads, database throughput would drop sharply. The core idea of MVCC is that a read request does not always need the newest version. It only needs a version that is visible to the current transaction.

InnoDB stores two important hidden fields in clustered index records: DB_TRX_ID, which identifies the transaction that last modified the record, and DB_ROLL_PTR, which points to the undo record generated by the most recent modification.

struct ReadView {
    trx_id_t creator_trx_id;         // The transaction that created this view
    std::vector
<trx_id_t> m_ids;     // The set of currently active transaction IDs
    trx_id_t up_limit_id;            // The smallest ID among active transactions
    trx_id_t low_limit_id;           // The next transaction ID to be assigned
};

A snapshot read compares the record version’s trx_id against the ReadView. If the current version is not visible, InnoDB follows DB_ROLL_PTR along the undo version chain and walks back to older versions.

The core difference between RC and RR is only when ReadView is created

Under RC, each ordinary SELECT creates a new ReadView, so each query may see the latest committed world at that moment. This prevents dirty reads but does not prevent non-repeatable reads.

Under RR, InnoDB creates the ReadView at the first snapshot read in the transaction and reuses the same view for subsequent queries. As a result, repeated reads within the same transaction remain stable.

AI Visual Insight: This diagram describes how ReadView partitions transaction ID space into a committed region, an active-transaction region, and a future-transaction region. Together, these regions determine whether a record version is visible and form the core of MVCC read-consistency decisions.

The four isolation levels represent different trade-offs between consistency and performance

RU reads the latest version directly and may even read uncommitted data, so dirty reads can occur. RC reads only committed versions, which solves dirty reads, but query results may still change within the same transaction.

RR solves non-repeatable reads by reusing the same ReadView and also works with Next-Key Locks to handle phantom reads in current-read scenarios. Serializable is more conservative: it forces ordinary reads into the lock system as well, at the cost of noticeably lower concurrency.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- Set the isolation level for the current session
START TRANSACTION;
SELECT * FROM course WHERE id = 101; -- Ordinary snapshot read
SELECT * FROM course WHERE id = 101 FOR UPDATE; -- Current read, attempts to acquire an X lock
COMMIT;

This SQL example shows the difference between snapshot reads and current reads: the former relies on MVCC, while the latter relies on locks.

Transaction boundaries are jointly defined by BEGIN, COMMIT, ROLLBACK, and autocommit

Explicit transactions define boundaries through BEGIN, COMMIT, and ROLLBACK. If autocommit=1, each individual DML statement is automatically wrapped in an implicit transaction.

However, autocommit is only a convenience mechanism. It does not provide failure recovery by itself. Forced rollback after a connection is lost still fundamentally depends on the transaction system and Undo Log.

FAQ

1. Why can’t locks replace transactions?

Locks can only prevent concurrent threads from overwriting each other. They cannot handle the case where a transaction crashes halfway through execution. Transactions use Undo Log to guarantee rollback on failure and solve the risk of partial execution.

2. Why must MVCC depend on Undo Log?

Because historical versions are not stored directly inside the record itself. Instead, DB_ROLL_PTR points to undo records, and those undo records are connected into a version chain. Without Undo Log, there would be no historical versions to read.

3. Why does MySQL choose RR by default instead of Serializable?

RR uses MVCC plus Next-Key Locks to preserve relatively high concurrency while still providing strong consistency. Serializable is stricter, but it makes ordinary reads participate heavily in lock contention, which creates too much throughput overhead.

Core Summary

This article uses InnoDB as the foundation to systematically reconstruct the MySQL transaction mechanism: from lock layering under concurrency, Undo Log rollback, and MVCC version chains to the visibility rules of the four isolation levels. The goal is to help developers build a complete understanding of transaction atomicity and isolation.