A transaction groups statements into one atomic unit: either all commit or all roll back, giving the A, C, I, D of ACID. The isolation level tunes the "I" — how much concurrent transactions can see of each other's uncommitted or changing data.
A transaction groups statements into one atomic unit: either all commit or all roll back, giving the A, C, I, D of ACID. The isolation level tunes the "I" — how much concurrent transactions can see of each other's uncommitted or changing data.
| Level | Dirty read | Non-repeatable read | Phantom |
|---|
| READ UNCOMMITTED | possible | possible | possible |
| READ COMMITTED | no | possible | possible |
| REPEATABLE READ (InnoDB default) | no | no | mostly no* |
| SERIALIZABLE | no | no | no |
*InnoDB's REPEATABLE READ uses MVCC (each transaction reads a consistent snapshot) plus next-key locks, so it blocks most phantoms too — stronger than the SQL standard requires.
SELECT @@transaction_isolation; -- REPEATABLE-READ by default
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- snapshot taken here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both updates apply, or ROLLBACK undoes both
Keep the InnoDB default (REPEATABLE READ) unless you have a reason. READ COMMITTED (what Postgres/Oracle default to) reduces lock contention for high-write systems; SERIALIZABLE is correct but serializes conflicting work and can deadlock more.
Isolation is the dial between correctness and concurrency. Understanding these anomalies lets you explain bugs like "the total didn't add up under load" and choose a level that keeps money-moving transactions correct without needlessly throttling throughput.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate