Understanding MySQL Transaction Internals: MVCC, Isolation Levels, and Concurrency Control

MySQL is a fundamental skill for Java developers, and mastering its transaction internals is essential for advanced performance tuning.

Concurrency in MySQL is solved through MVCC 多版本控制机制 , transaction isolation mechanisms, and lock mechanisms, which together prevent data corruption when multiple transactions modify the same record.

The article defines four types of dirty data—dirty write, dirty read, non‑repeatable read, and phantom read—and illustrates each with concrete examples and diagrams.

It then presents the four standard isolation levels:

Read Uncommitted : allows reading uncommitted changes.

Read Committed (RC): only reads data that has been committed.

Repeatable read (RR): MySQL's default, ensures repeatable reads by keeping the same ReadView throughout the transaction.

Serializable : the strictest level, serializing all transactions.

A comparison table shows which anomalies each level prevents.

The core of MySQL's concurrency control is the MVCC mechanism. Each record stores a transaction ID and a rollback pointer, forming a version chain. When a transaction starts, a ReadView is created, recording active transaction IDs (m_ids), the smallest active ID (min_trx_id), the next ID to be assigned (max_trx_id), and the creator's ID (creator_trx_id).

Through a series of diagrams, the article demonstrates how ReadView determines which version of a row a transaction should see, based on whether the associated transaction ID is active, committed, or older than the current view.

For the RC isolation level, a new ReadView is generated for each query, allowing a transaction to see newly committed changes after another transaction commits.

For the RR isolation level, the ReadView is created once at the start of the transaction and reused until commit, guaranteeing that repeated reads return the same data even if other transactions commit in the meantime.

The article concludes that understanding these low‑level mechanisms—undo logs, version chains, and ReadView —provides deep insight into how MySQL maintains data consistency under concurrent workloads.