How to Choose the Right Database Transaction Isolation Level

1. Background

Transaction isolation is a core topic in senior‑level development interviews. After a previous article on Spring transaction management, this piece focuses on the isolation mechanisms of relational databases, using MySQL as the primary example.

Consider a bank transfer: moving 100 CNY from one account to another requires a series of operations (read balance, compute new balance, update balance) that must succeed as a single unit. If these operations are not atomic, another concurrent transfer could read the same balance before it is deducted, leading to inconsistent results. This illustrates why a transaction must guarantee that a group of database operations either all succeed or all fail.

In MySQL, transaction support is provided by storage engines. The InnoDB engine supports transactions, whereas the older MyISAM engine does not, which is a key reason InnoDB replaced MyISAM.

2. Isolation Mechanism

ACID (Atomicity, Consistency, Isolation, Durability) defines the four essential properties of a transaction. Isolation prevents concurrent transactions from interfering with each other. Insufficient isolation can cause three phenomena:

Dirty read

Non‑repeatable read

Phantom read

The four isolation levels, from lowest to highest, are:

Read Uncommitted (读未提交)

Read Committed (读提交)

Repeatable Read (可重复读)

Serializable (串行化)

Each level progressively eliminates the three anomalies.

Example: a table T(c int) engine=InnoDB with a single row value 1. Two transactions A and B modify the row. The following diagram (shown in the original article) illustrates the sequence of operations.

Results under each isolation level:

Read Uncommitted : A sees B’s uncommitted change, so V1, V2, V3 are all 2 (dirty read).

Read Committed : A sees B’s change only after B commits; V1=1, V2=V3=2.

Repeatable Read : A sees a consistent snapshot; V1=V2=1, V3=2.

Serializable : B is blocked by a write lock until A commits; from A’s perspective V1=V2=1, V3=2.

The article explains how MySQL creates a “view” of the data for each isolation level:

Read Committed: a view is created at the start of each SQL statement.

Repeatable Read: a view is created when the transaction starts and reused throughout.

Serializable: locks are used to prevent concurrent access.

Read Uncommitted: no view; the latest value is returned directly.

MySQL’s default isolation level is Repeatable Read . Oracle and PostgreSQL default to Read Committed . When migrating from Oracle to MySQL, you may need to set MySQL’s isolation level to READ‑COMMITTED to preserve behavior.

Configuration example: set tx_isolation='READ-COMMITTED'; You can verify the current setting with:

show variables like '%tx_isolation%';

3. Understanding Dirty, Non‑repeatable, and Phantom Reads

Dirty read occurs when a transaction reads data modified by another transaction that has not yet committed. If the uncommitted transaction rolls back, the read data never existed.

In the earlier example, under Read Uncommitted, V1 becomes 2 even though B later rolls back, illustrating a dirty read.

Non‑repeatable read happens when the same transaction reads the same row at different times and gets different values because another transaction committed a change in between.

Under Read Committed, the same transaction may see different values for the same row, demonstrating a non‑repeatable read.

Phantom read is similar but involves the appearance or disappearance of rows that match a query condition. For instance, transaction A checks whether a row exists and plans to insert; transaction B inserts a matching row and commits first, causing A’s subsequent read to see a new row (the “phantom”).

4. Implementation Details

MySQL implements Repeatable Read using Multi‑Version Concurrency Control (MVCC). Every update writes a rollback record. When a transaction reads a row, it sees a snapshot (read‑view) based on those rollback logs.

Example of a record evolving from 1 → 2 → 3 → 4, with corresponding rollback entries (illustrated by an image in the original article).

Different transactions may have different read‑views (A sees 1, B sees 2, C sees 4). This is the essence of MVCC.

Rollback logs are retained only as long as any active transaction’s read‑view needs them. When no transaction requires older versions, the logs are discarded.

Long‑running transactions keep old read‑views alive, forcing the server to retain many rollback records and lock resources, which can dramatically increase storage usage and degrade performance. In MySQL 5.5 and earlier, rollback logs reside in the shared ibdata file, so even after a long transaction commits, the file does not shrink, often requiring a full rebuild to reclaim space.

5. Summary

The article covered the theory of transaction isolation, detailed the four isolation levels, demonstrated their effects with concrete MySQL examples, explained the root causes of dirty, non‑repeatable, and phantom reads, and described MySQL’s MVCC implementation and the pitfalls of long transactions. Choosing the appropriate isolation level depends on the specific consistency and concurrency requirements of your application.