Understanding MySQL Transaction Isolation Levels: ACID, Tests, and Practical Guide

What is a Transaction

A transaction is a tightly coupled set of operations in an application; all operations must succeed, otherwise all changes are rolled back. It has atomicity: either all operations succeed or none are applied. A transaction ends either by committing when all steps succeed, or by rolling back when any step fails.

ACID Properties

Transactions have four characteristics: Atomicity, Consistency, Isolation, and Durability (ACID).

1. Atomicity : All operations in a transaction are performed as a single unit—either all succeed or none do. 2. Consistency : A transaction transforms the database from one consistent state to another. If a failure occurs, the database must not remain in an inconsistent state. 3. Isolation : Transactions execute independently without interfering with each other. 4. Durability : Once a transaction is committed, its changes persist permanently, unaffected by subsequent operations or failures.

MySQL Isolation Levels

SQL defines four isolation levels, each balancing concurrency and consistency.

Read Uncommitted : Transactions can see uncommitted changes from other transactions (dirty reads). Rarely used in practice. Read Committed : Default for many DBMS (but not MySQL). Transactions see only committed changes; non‑repeatable reads can occur. Repeatable Read : MySQL’s default. Guarantees that repeated reads within the same transaction return the same rows, preventing non‑repeatable reads; phantom reads are handled via MVCC. Serializable : Highest isolation; forces transactions to execute sequentially, eliminating phantom reads but causing higher lock contention.

Testing MySQL Isolation Levels

The following experiments use two MySQL client sessions (A and B) on a database demo with table test.

1. Read Uncommitted (Dirty Read)

Session A sets isolation to READ UNCOMMITTED and starts a transaction. Session B updates a row but does not commit. A reads the updated value, demonstrating a dirty read. B then rolls back, and A sees the original data again.

2. Read Committed (Non‑repeatable Read)

A sets isolation to READ COMMITTED. B updates a row without committing. A reads the row twice; the second read sees the change after B commits, illustrating a non‑repeatable read.

3. Repeatable Read (Phantom Read)

A uses REPEATABLE READ. B inserts a new row and commits. A’s repeated reads return the same result set, showing no phantom read in this test, though the level can allow phantom reads depending on implementation.

4. Serializable (Full Serialization)

A sets isolation to SERIALIZABLE. B attempts to read or write the same data and must wait until A commits, demonstrating full locking and potential performance impact.

These experiments illustrate how each isolation level affects visibility of data changes and the trade‑offs between concurrency and consistency.