Transactions - Isolation & Common Anomalies

Transaction isolation controls how one transaction's reads and writes interact with others. The SQL standard defines isolation levels by which anomalies they prevent: dirty read, non-repeatable read, and phantom read. This article summarizes the four levels and the three phenomena, with small examples and a reference table.

Overview

• Read Uncommitted: No isolation; can see uncommitted changes (dirty read). Rarely used.
• Read Committed: Each query sees only committed data; no dirty read. Same row can change between two reads in the same transaction (non-repeatable read).
• Repeatable Read: A transaction sees a consistent snapshot; no dirty read, no non-repeatable read. In MySQL/InnoDB, gap locks can prevent phantom read in many cases.
• Serializable: Strongest; effectively serial execution. In practice often implemented with strict locking or validation.

The three classic anomalies:

1. Dirty read: Reading a row that another transaction has modified but not yet committed. If that transaction rolls back, you saw data that "never existed."
2. Non-repeatable read: Reading the same row twice in one transaction and seeing different values because another transaction committed a change in between.
3. Phantom read: Two identical range queries in one transaction return different numbers of rows because another transaction inserted or deleted rows in the range.

Example

Example 1: Dirty read (only possible at Read Uncommitted)

SQLCopy
-- Session A
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
START TRANSACTION;
UPDATE accounts SET balance = 0 WHERE id = 1;  -- not committed

-- Session B (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1;     -- may see 0 (dirty)

-- Session A
ROLLBACK;   -- balance back to original; B saw a value that was never committed

Example 2: Non-repeatable read (possible at Read Committed)

SQLCopy
-- Session A
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;     -- e.g. 100

-- Session B
UPDATE accounts SET balance = 50 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;     -- 50 (different within same tx)
COMMIT;

Example 3: Phantom read (possible at Read Committed; prevented at RR in InnoDB with gap locks)

SQLCopy
-- Session A (READ COMMITTED)
START TRANSACTION;
SELECT * FROM orders WHERE user_id = 100;       -- 3 rows

-- Session B
INSERT INTO orders (user_id, ...) VALUES (100, ...);
COMMIT;

-- Session A
SELECT * FROM orders WHERE user_id = 100;       -- 4 rows (phantom)

• Under Repeatable Read, InnoDB uses a snapshot for Session A's reads and gap locks for locking reads, so A typically does not see the new row until A commits.

Core Mechanism / Behavior

• Read view: In READ COMMITTED, each statement gets a new read view (latest committed state). In REPEATABLE READ, the read view is established at the first read and reused, so you get a consistent snapshot.
• Locks: Row locks and gap locks (see Locks - Row Lock vs Gap Lock) prevent other transactions from changing or inserting rows that would affect your repeatable read or locking reads.
• Standard vs implementation: The SQL standard defines levels by anomalies; databases implement them with different techniques (snapshots, locking). MySQL InnoDB's REPEATABLE READ often prevents phantoms; the standard allows them at RR.

Level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Committed	No	Possible	Possible
Repeatable Read	No	No	Usually no (InnoDB)
Serializable	No	No	No

Key Rules

• Default in MySQL is REPEATABLE READ; use READ COMMITTED if you want to see other transactions' commits immediately and accept non-repeatable and phantom reads.
• Avoid long-running transactions at high isolation; they hold snapshots or locks and increase blocking or undo size. Keep transactions short and narrow.
• When you need "current" value for a decision (e.g. balance check), use a locking read (SELECT ... FOR UPDATE) or explicit lock so another transaction cannot change the row before you update.

What's Next

See MVCC Explained for how InnoDB implements read views and snapshots. See Locks - Row Lock vs Gap Lock for how gap locks prevent phantoms.