Mastering Transaction Isolation Levels in MySQL: From Theory to Practice

Transaction Basics

First, review the four basic elements of a transaction (ACID):

Atomicity : All operations in a transaction either complete fully or are completely rolled back.

Consistency : The database remains in a valid state before and after the transaction.

Isolation : Only one transaction can access the same data at a time.

Durability : Once a transaction commits, its changes persist permanently.

Typical Concurrency Problems

Common anomalies that arise when transactions run concurrently:

Dirty read : A transaction reads data modified by another uncommitted transaction.

Non‑repeatable read : Re‑executing the same query within a transaction returns different results because another transaction modified the data in between.

Phantom read : A transaction sees rows that were inserted by another transaction after the first query.

SQL‑Standard Isolation Levels

The SQL standard defines four isolation levels to balance concurrency and consistency:

Read uncommitted : Allows dirty reads; the lowest isolation.

Read committed : Prevents dirty reads but permits non‑repeatable and phantom reads; most commonly used.

Repeatable read : Guarantees that repeated reads of the same row return the same data, preventing dirty and non‑repeatable reads; phantom reads may still occur.

Serializable : Executes transactions one after another, eliminating all anomalies but greatly reducing concurrency.

The following table shows which anomalies each level allows:

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

Read uncommitted

✓

✓

✓

Read committed

✗

✓

✓

Repeatable read

✗

✗

✓

Serializable

✗

✗

✗

Higher isolation levels reduce concurrent performance.

MySQL Isolation Level Details

MySQL uses the global variable @@tx_isolation (or @@session.tx_isolation) to indicate the current isolation level. The default is REPEATABLE‑READ :

select @@tx_isolation;

select @@session.tx_isolation;

To view the global default: select @@global.tx_isolation; Setting the isolation level can be done per session or globally:

set session transaction isolation level repeatable read;

set global transaction isolation level repeatable read;

Practical Tests

Read uncommitted test : Client A sets isolation to read uncommitted and reads a table. While A’s transaction is open, client B updates the same table but does not commit. A can see B’s uncommitted changes, demonstrating a dirty read.

When B rolls back, A’s previously read data becomes invalid (dirty data).

Read committed test : With isolation set to read committed, client A cannot see B’s uncommitted updates. However, if B commits between two reads by A, the results differ, illustrating a non‑repeatable read.

Repeatable read test : Setting isolation to repeatable read ensures that repeated queries within the same transaction return identical result sets, preventing non‑repeatable reads. MySQL’s implementation, however, does not produce phantom reads under this level.

Experiments confirm that MySQL’s repeatable read behaves like snapshot isolation, avoiding phantom reads, which differs from the strict SQL standard.

Conclusion

Understanding transaction isolation levels and testing them in a real database helps bridge theory and practice, revealing how different levels affect data consistency and concurrency.