Master MySQL Transaction Isolation Levels: From READ UNCOMMITTED to SERIALIZABLE

Why Need Transaction Isolation Levels?

Without proper isolation, concurrent operations can cause data anomalies such as two users seeing the same inventory count and both placing orders, leading to overselling. Isolation balances concurrency and data consistency.

Three Classic Concurrency Problems

In a multi‑transaction environment, three issues may arise: dirty read , non‑repeatable read , and phantom read . These problems degrade data consistency to varying degrees.

2.1 Dirty Read

A transaction reads data that another transaction has not yet committed, potentially seeing temporary or rolled‑back values.

2.2 Non‑repeatable Read

The same query within a single transaction returns different results because another transaction modified and committed the data in between.

2.3 Phantom Read

Repeated range queries within a transaction return a different number of rows because other transactions inserted or deleted rows that match the range.

Four Isolation Levels in Detail

3.1 READ UNCOMMITTED

Lowest level; transactions can read uncommitted changes, leading to dirty reads, non‑repeatable reads, and phantom reads.

3.2 READ COMMITTED

Prevents dirty reads by only allowing reads of committed data, but non‑repeatable reads and phantom reads may still occur.

3.3 REPEATABLE READ

MySQL's default level. Guarantees that repeated reads of the same row within a transaction see the same data, using MVCC and gap/next‑key locks to largely prevent phantom reads.

3.4 SERIALIZABLE

Highest level; forces transactions to execute serially by acquiring shared and exclusive locks, eliminating all three anomalies at the cost of performance.

Adjust the isolation level with the command SET SESSION TRANSACTION ISOLATION LEVEL. For example, to set READ UNCOMMITTED: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Choosing the Right Isolation Level

Read‑heavy, write‑light scenarios : Use the default REPEATABLE READ for good consistency with minimal impact.

Write‑heavy scenarios : Consider READ COMMITTED to reduce lock contention, accepting possible non‑repeatable reads.

Strong consistency requirements (e.g., financial transactions) : Use SERIALIZABLE or REPEATABLE READ with explicit pessimistic locks.

Balance: higher isolation improves consistency but incurs higher performance overhead.

Understanding MVCC (Multi‑Version Concurrency Control)

MVCC maintains multiple versions of each row, allowing reads and writes to proceed without blocking each other.

In InnoDB, each row stores a hidden transaction ID and a rollback pointer, forming a version chain.

When a transaction starts, InnoDB creates a Read View that records active transaction IDs and the maximum committed ID. A row version is visible to the transaction if its transaction ID is less than the smallest active ID, not in the active list, and not greater than the maximum committed ID.

Two read modes exist:

Snapshot read : ordinary SELECT that reads from the consistent snapshot without locking.

Current read : SELECT ... FOR UPDATE, SELECT LOCK IN SHARE MODE, UPDATE, DELETE – reads the latest version and acquires locks.

Understanding these mechanisms explains why phantom reads can still appear under REPEATABLE READ when using current reads.