Master MySQL Transaction Isolation: Theory, Levels, and Real‑World Practices

1. ACID Basics

ACID defines the four essential properties of a reliable database transaction: Atomicity (all‑or‑nothing), Consistency (state transitions from one valid state to another), Isolation (concurrent transactions do not interfere), and Durability (committed changes persist).

2. Typical Isolation Anomalies

2.1 Dirty Read

A transaction reads data modified by another transaction that has not yet committed.

-- Transaction A
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- not committed

-- Transaction B
SELECT balance FROM accounts WHERE id = 1; -- reads the uncommitted value

2.2 Non‑repeatable Read

The same query returns different results within a single transaction because another transaction modified the data.

-- Transaction A
SELECT balance FROM accounts WHERE id = 1; -- returns 1000

-- Transaction B
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- Transaction A (second read)
SELECT balance FROM accounts WHERE id = 1; -- now returns 900

2.3 Phantom Read

Repeated queries return a different number of rows because another transaction inserted or deleted rows.

-- Transaction A
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- returns 5

-- Transaction B
INSERT INTO accounts VALUES (6, 1500);
COMMIT;

-- Transaction A (second count)
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- returns 6

3. Overview of MySQL Isolation Levels

MySQL supports four isolation levels, each allowing or preventing specific anomalies:

READ UNCOMMITTED : permits dirty reads, non‑repeatable reads, and phantom reads (lowest level).

READ COMMITTED : prevents dirty reads but allows non‑repeatable and phantom reads (Oracle default).

REPEATABLE READ : MySQL default; prevents dirty and non‑repeatable reads, and thanks to MVCC + gap locks, practically eliminates phantom reads.

SERIALIZABLE : blocks all read anomalies by locking reads, but incurs the highest performance cost.

4. Behavior and SQL Examples per Level

4.1 READ UNCOMMITTED (Read Uncommitted)

All read anomalies may appear.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM accounts; -- may see uncommitted rows

4.2 READ COMMITTED (Read Committed)

Only dirty reads are prevented.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- row lock
COMMIT;

4.3 REPEATABLE READ (Repeatable Read)

MySQL's default level. Prevents dirty and non‑repeatable reads; phantom reads are avoided by InnoDB's MVCC and Next‑Key Lock .

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
UPDATE accounts SET balance = 0 WHERE balance > 1000; -- may acquire gap lock
COMMIT;

Why MySQL Rarely Shows Phantom Reads

InnoDB uses two mechanisms:

Next‑Key Lock (record lock + gap lock)

MVCC version chain

These together mask phantom rows under REPEATABLE READ.

4.4 SERIALIZABLE (Serializable)

Fully blocks read anomalies by implicitly adding LOCK IN SHARE MODE to SELECTs, which dramatically reduces concurrency.

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts; -- acquires shared lock on the whole range

5. Viewing and Setting the Isolation Level

View Current Level

SELECT @@global.transaction_isolation;
SELECT @@transaction_isolation;
SELECT @@tx_isolation; -- for MySQL 8.0 and earlier

Set Isolation Level

Global : SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; Session : SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Single Transaction :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

6. Lock Types and Strategies

Shared Lock (S) : allows reads, blocks writes.

Exclusive Lock (X) : allows writes, blocks other reads/writes.

Intention Locks : InnoDB‑managed meta‑locks that speed up lock checks.

Gap Lock : locks a range to prevent inserts.

Next‑Key Lock : combination of row lock and gap lock.

Lock strategies per isolation level:

RU: no row or gap locks; reads the latest value.

RC: row locks only; each read sees the latest snapshot.

RR: row locks + gap locks + next‑key lock; snapshot is consistent for the whole transaction.

SERIALIZABLE: row, gap, and next‑key locks on every SELECT; reads are locked.

7. Practical Example: Bank Transfer

Recommended isolation level: REPEATABLE READ .

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- Lock source account
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Debit source, credit destination
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Record the transfer
INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100);
COMMIT;

8. How to Avoid Deadlocks (Practical Tips)

Keep transactions short.

Access tables in a fixed order.

Use appropriate indexes to narrow lock scope.

Avoid large scans (e.g., WHERE age > 0) that may lock whole tables.

When necessary, downgrade isolation to READ COMMITTED to reduce lock contention.

9. Inspecting Locks and Transaction Waits

-- InnoDB engine status
SHOW ENGINE INNODB STATUS;
-- Current locks
SELECT * FROM information_schema.INNODB_LOCKS;
-- Lock wait information
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- Active transactions
SELECT * FROM information_schema.INNODB_TRX;

10. Summary (Interview & Practical Highlights)

MySQL defaults to REPEATABLE READ , not READ COMMITTED.

InnoDB solves phantom reads with MVCC + Next‑Key Lock . READ COMMITTED gives a fresh snapshot on each read; REPEATABLE READ provides a consistent snapshot for the whole transaction.

SERIALIZABLE guarantees correctness but severely hurts performance.

Most business workloads benefit from REPEATABLE READ .

Financial scenarios should use FOR UPDATE to lock rows explicitly and ensure strong consistency.

Deadlock prevention hinges on fixed access order, short transactions, and proper indexing.