What are transaction isolation levels and which anomalies do they prevent?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
Isolation levels trade correctness for concurrency: Read Uncommitted (sees dirty reads), Read Committed (no dirty reads), Repeatable Read (no non-repeatable reads), Serializable (also no phantoms — fully correct, slowest). PostgreSQL defaults to Read Committed; use stronger levels only for transactions that truly need them.
Detailed explanation
Three classic anomalies define the ladder. A dirty read sees another transaction's uncommitted change that later rolls back — acting on data that never officially existed. A non-repeatable read re-reads the same row in one transaction and gets a different value because a concurrent transaction committed an update in between. A phantom read re-runs a range query (e.g., 'all orders over $100') and finds new rows a concurrent insert added. The SQL levels: Read Uncommitted allows everything including dirty reads (rarely used; in PostgreSQL it behaves as Read Committed). Read Committed takes a fresh snapshot per statement — no dirty reads, but the same row can change between two statements in one transaction. Repeatable Read takes one snapshot per transaction — re-reads are stable, but new rows matching a range can still appear (phantoms). Serializable executes transactions as if strictly ordered — no anomalies at all — but conflicting transactions abort with serialization errors the app must retry. Stronger levels hold more locks or cause more retries, reducing throughput. Practical guidance: stay at the default Read Committed; escalate single critical transactions (balance transfers, inventory decrements) to Repeatable Read or Serializable; and always code retry loops for serialization failures rather than surfacing them to users.
Example
-- Two concurrent sessions at READ COMMITTED:
-- Session A: BEGIN; SELECT balance FROM accounts WHERE id='alice'; -- 500
-- Session B: BEGIN; UPDATE accounts SET balance=400 WHERE id='alice'; COMMIT;
-- Session A: SELECT balance FROM accounts WHERE id='alice'; -- now 400 (non-repeatable)
-- At REPEATABLE READ, session A would still see 500 in the second SELECT.
-- Critical section at the strongest level:
-- BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- UPDATE inventory SET stock = stock - 1 WHERE product_id = 42 AND stock > 0;
-- COMMIT; -- may raise a serialization error under contention: retry the txnInterview tip
Name the three anomalies in escalating order with a one-line example each, then give the rule: default level everywhere, stronger level plus retry loop for the critical few transactions.
Common mistakes
- Listing the levels without the anomalies they prevent.
- Running everything at Serializable and wondering why throughput collapsed.
- Not retrying transactions that abort with serialization errors.