Master MySQL Transaction Isolation: From READ UNCOMMITTED to SERIALIZABLE

MySQL Transactions

MySQL transactions are supported only by the InnoDB engine; MyISAM does not support transactions. A transaction is a group of operations that either all succeed or all fail, providing the ACID properties (Atomicity, Consistency, Isolation, Durability). This article focuses on the Isolation property.

Concept Explanation

Dirty Read

A dirty read occurs when a transaction reads data that has been modified by another transaction but not yet committed, meaning the data may later be rolled back.

Repeatable Read

In a repeatable read, a transaction sees the same data for the same query throughout its lifetime, even if other transactions commit changes to those rows.

Non‑repeatable Read

A non‑repeatable read happens when a transaction reads the same rows at different times and gets different values because another transaction has committed updates.

Phantom Read

A phantom read occurs when a transaction re‑executes a query and sees newly inserted rows that were not visible in the previous execution.

Transaction Isolation Levels

The SQL standard defines four isolation levels, all supported by MySQL: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The isolation strength increases from top to bottom, while performance decreases. MySQL’s default level is REPEATABLE READ.

Effect of each level on the three phenomena:

READ UNCOMMITTED – may allow dirty reads, non‑repeatable reads, and phantom reads.

READ COMMITTED – prevents dirty reads but may allow non‑repeatable and phantom reads.

REPEATABLE READ – prevents dirty and non‑repeatable reads; phantom reads are largely prevented by MySQL’s implementation.

SERIALIZABLE – prevents all three phenomena but incurs the highest performance cost.

One‑by‑One Analysis

How to Set Isolation Level

You can view the current isolation level and change it with the following statements:

<code>show variables like 'transaction_isolation';
SELECT @@transaction_isolation;
show variables like 'tx_isolation';
SELECT @@tx_isolation;</code>

To set a new level:

<code>SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};</code>

Example – set the global level to READ COMMITTED:

<code>set global transaction isolation level read committed;</code>

Executing Transactions in MySQL

A transaction starts with BEGIN (or START TRANSACTION ) and ends with COMMIT or ROLLBACK . The transaction actually begins with the first statement after BEGIN .

Example diagram:

READ UNCOMMITTED

This level provides the best performance because it does not acquire locks, but it allows dirty reads. Any change made by a transaction becomes visible to other transactions immediately, even before commit.

Demo:

<code>set global transaction isolation level read uncommitted;</code>

Two sessions (A and B) illustrate that B can read uncommitted changes made by A, leading to possible dirty data if A rolls back.

READ COMMITTED

Only committed data is visible, eliminating dirty reads. However, non‑repeatable reads can still occur because each statement gets a fresh snapshot.

Demo:

<code>set global transaction isolation level read committed;</code>

Session B reads the original value until Session A commits, after which B sees the new value, demonstrating a non‑repeatable read.

REPEATABLE READ

Provides a consistent snapshot for the whole transaction, preventing non‑repeatable reads. MySQL also prevents phantom reads at this level using next‑key locks.

Demo:

<code>set global transaction isolation level repeatable read;</code>

Session A updates a row and commits; Session B, which started earlier, sees the same data before and after A’s commit, confirming repeatable reads.

SERIALIZABLE

The strongest isolation level forces transactions to execute sequentially, eliminating all three phenomena but greatly reducing concurrency.

How MySQL Implements Isolation

READ UNCOMMITTED uses no locks. SERIALIZABLE uses shared locks for reads and exclusive locks for writes. READ COMMITTED and REPEATABLE READ rely on MVCC (multi‑version concurrency control) and snapshots.

Implementing REPEATABLE READ

MySQL stores multiple versions of a row, each with a transaction ID (trx_id). A snapshot determines which versions are visible based on four rules:

The transaction’s own updates are visible.

Uncommitted versions are invisible.

Committed versions created after the snapshot are invisible.

Committed versions created before the snapshot are visible.

Concurrent Write Issues

When two transactions try to update the same row, the first acquires a row lock that is released only after commit. The second transaction waits until the lock is released, potentially timing out.

<code>update user set age=11 where id = 1;</code>

If the WHERE clause uses a non‑indexed column, MySQL may lock all rows, filter, and then release locks on non‑matching rows, which can be costly for large tables.

<code>update user set age=11 where age=10;</code>

Resolving Phantom Reads

MySQL uses next‑key locks (row lock + gap lock) on indexed columns to prevent other transactions from inserting rows into the gap, thereby eliminating phantom reads. Without an index, a gap lock is applied to the whole table.

Summary

Only InnoDB supports transactions in MySQL, with REPEATABLE READ as the default isolation level. READ UNCOMMITTED offers no locking, SERIALIZABLE enforces single‑threaded execution, and READ COMMITTED solves dirty reads but not non‑repeatable reads. MySQL’s REPEATABLE READ largely prevents phantom reads using next‑key locks, while row locks handle concurrent updates.