Master MySQL Transaction Isolation Levels: From READ UNCOMMITTED to SERIALIZABLE

MySQL Transaction

This article focuses on MySQL transactions under the InnoDB engine; MyISAM does not support transactions.

A transaction is a group of database operations that either all succeed or all fail, rolling back if any step fails, ensuring atomicity.

Example: an online purchase that updates order status, deducts inventory, etc., must either complete fully or be rolled back to avoid inconsistent state.

Transactions have the ACID properties (Atomicity, Consistency, Isolation, Durability); this article concentrates on the Isolation aspect.

Concepts

Dirty Read

Reading data that has been modified by another transaction but not yet committed; such data may be rolled back.

Repeatable Read

Within a transaction, the data read at the beginning remains consistent for the entire transaction, typically concerning UPDATE operations.

Non‑repeatable Read

Data read at different times within the same transaction may differ because other transactions have committed changes.

Phantom Read

Occurs with INSERT operations: a transaction sees new rows inserted by another transaction after it has begun.

Isolation Levels

The SQL standard defines four isolation levels, all supported by MySQL:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ (default in MySQL)

SERIALIZABLE

Higher isolation provides stronger consistency but lower performance.

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

READ UNCOMMITTED

Possible

Possible

Possible

READ COMMITTED

Impossible

Possible

Possible

REPEATABLE READ

Impossible

Impossible

Possible

SERIALIZABLE

Impossible

Impossible

Impossible

How to Set Isolation Level

Check the current level:

# Show transaction isolation level (MySQL 5.7.20+)
show variables like 'transaction_isolation';
SELECT @@transaction_isolation;

# Alternative name
SELECT @@tx_isolation;
show variables like 'tx_isolation';

Set a new level (SESSION or GLOBAL):

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

Example – set global level to READ COMMITTED:

set global transaction isolation level read committed;

Executing a Transaction in MySQL

Start with BEGIN or START TRANSACTION, perform statements, then COMMIT to finish or ROLLBACK to abort.

Note: the transaction actually begins with the first statement after BEGIN, not the BEGIN command itself.

Read Uncommitted

Does not acquire locks, offering the best performance but allowing dirty reads.

Experiment: set global level to READ UNCOMMITTED, update a row in session A, and query the same row in session B before committing – B sees the uncommitted value, demonstrating dirty reads.

Read Committed

Only committed data is visible, eliminating dirty reads but still allowing non‑repeatable reads.

After setting to READ COMMITTED, an update in session A is not visible to session B until A commits, illustrating non‑repeatable reads.

Repeatable Read

Provides a consistent snapshot for the whole transaction, preventing non‑repeatable reads; however, phantom reads can still occur with INSERTs.

MySQL’s implementation actually prevents phantom reads at this level using next‑key locks.

Serializable

The strictest level; transactions are executed sequentially, eliminating all three anomalies but with the highest performance cost.

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). Each row version stores the creating transaction ID (row trx_id). A snapshot determines which versions are visible based on four rules:

Updates made by the current transaction are visible.

Uncommitted versions are invisible.

Committed versions after the snapshot are invisible.

Committed versions before the snapshot are visible.

REPEATABLE READ creates the snapshot once at transaction start; READ COMMITTED recreates it for each statement.

Concurrent Write Handling

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

With indexed columns, MySQL can locate rows directly; without an index, it must lock all rows and then filter, which hurts performance.

Phantom‑Read Prevention

MySQL uses next‑key locks (row lock + gap lock) to block inserts into the range scanned by a REPEATABLE READ transaction, thereby preventing phantom reads.

Summary

InnoDB is the only MySQL engine that supports transactions; REPEATABLE READ is the default isolation level.

READ UNCOMMITTED offers no isolation, SERIALIZABLE provides full isolation but poor performance.

READ COMMITTED solves dirty reads; REPEATABLE READ (with MVCC and next‑key locks) solves non‑repeatable reads and phantom reads; row locks handle concurrent updates.