Understanding MySQL Transaction Isolation: ACID, Concurrency Issues, and Practical Examples

1. Basic Elements of a Transaction (ACID)

Atomicity : All operations in a transaction succeed or none do; on error the transaction rolls back to its initial state.

Consistency : Integrity constraints remain valid before and after the transaction, e.g., money transferred from A to B cannot disappear.

Isolation : Only one transaction can access a specific data row at a time, preventing interference between concurrent sessions.

Durability : Once a transaction commits, its changes are permanently stored and cannot be rolled back.

Summary: Atomicity is the foundation, while isolation and durability are mechanisms to achieve data consistency.

2. Concurrency Problems

Dirty read : Transaction A reads data modified by transaction B before B commits; if B rolls back, A has read invalid data.

Non‑repeatable read : Transaction A reads the same row multiple times and gets different results because another transaction updates and commits in between.

Phantom read : Transaction A reads a set of rows, another transaction inserts a new row, and A’s subsequent read sees the new row, appearing as a “phantom”.

Resolution: Use row‑level locks for non‑repeatable reads and table‑level locks for phantom reads.

3. MySQL Transaction Isolation Levels

MySQL’s default isolation level is REPEATABLE‑READ . The following diagram shows the default setting:

Another diagram illustrates the isolation level hierarchy:

4. Example Scenarios for Each Isolation Level

4.1 Read Uncommitted

Open client A and set SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; then query the account table.

Before A commits, open client B and update account.

Client A can see B’s uncommitted changes, demonstrating a dirty read.

If B rolls back, A has read invalid data.

When A later updates the same row, the balance may be inconsistent, showing why READ COMMITTED is preferred.

4.2 Read Committed

Open client A with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; and query the initial balances.

Client B updates the table but does not commit.

Client A cannot see B’s uncommitted changes, preventing dirty reads.

After B commits, A’s subsequent read may return a different value, illustrating a non‑repeatable read.

4.3 Repeatable Read

Open client A with SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; and query the table.

Before A commits, client B updates and commits a row.

MySQL’s repeatable‑read does not lock the rows read by A, so A still sees the original values (no non‑repeatable read).

When A later updates the same row, the balance calculation uses the original value, which may appear inconsistent to the application.

mysql> select * from account;

+------+--------+---------+
| id   | name   | balance |
+------+--------+---------+
| 1    | lilei  | 300     |
| 2    | hanmei | 16000   |
| 3    | lucy   | 2400    |
+------+--------+---------+
3 rows in set (0.00 sec)

4.4 Serializable

Set isolation to SERIALIZABLE on client A, start a transaction, and query the table.

Client B attempts to insert a new row; the operation blocks or fails because the whole table is locked.

Serializable guarantees no phantom reads but severely reduces concurrency.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

5. Practical Recommendations

Higher isolation levels provide stronger data integrity but hurt concurrency. For most applications, READ COMMITTED offers a good balance: it prevents dirty reads while allowing acceptable parallelism. When non‑repeatable reads or phantom reads are problematic, developers can use explicit row locks (pessimistic) or version checks (optimistic) instead of moving to SERIALIZABLE.

Key takeaways:

MySQL’s default REPEATABLE READ does not lock rows read by a transaction.

In SERIALIZABLE, the entire table is locked, eliminating phantom reads but drastically reducing throughput.

Understanding the trade‑offs helps avoid user‑experience issues where a client sees inconsistent balances.