Walk through the transaction isolation levels — Read Uncommitted to Serializable — and the anomaly each one fixes

#Why isolation levels exist

A database transaction is supposed to feel like it runs alone, even though dozens of other transactions are executing concurrently against the same rows. Full isolation — literally running transactions one at a time — is called serializability, and it's the safest possible guarantee. It is also usually too slow, because it forces the database to serialize work that could otherwise run in parallel.

Isolation levels are a knob that trades some of that safety for throughput. The SQL standard defines four levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each one closing off a specific category of concurrency anomaly. The interview question is really asking: do you understand what can go wrong when transactions overlap, and which level closes which hole?

#The three classic anomalies

• Dirty read — transaction B reads a row that transaction A has modified but not yet committed. If A rolls back, B has read data that never officially existed.
• Non-repeatable read — transaction B reads the same row twice within one transaction and gets two different values, because A committed a change to that row in between.
• Phantom read — transaction B runs the same range query twice and gets a different set of rows the second time, because A inserted or deleted a row that matches the query's predicate.

#Dirty read, in SQL

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- not committed yet

-- Session B, running at READ UNCOMMITTED
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- sees the debited balance
COMMIT;

-- Session A
ROLLBACK; -- the debit never happened, but B already acted on it

If session B used that balance to approve a withdrawal, you've now handed out money for a transaction that was undone. Almost nobody runs production workloads at Read Uncommitted for this exact reason — it's mostly there for analytics queries that intentionally accept stale or inconsistent numbers in exchange for zero blocking.

#Non-repeatable read, in SQL

-- Session A, at READ COMMITTED (Postgres/Oracle default)
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- returns 500

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

-- Session A, same transaction
SELECT balance FROM accounts WHERE id = 1; -- returns 300, same query, different answer
COMMIT;

Read Committed only promises that you never see uncommitted data — it says nothing about seeing the same data twice. A report that reads a value at the start of a transaction and reads it again later, expecting consistency, will get burned here.

#Phantom read, in SQL

-- Session A, at REPEATABLE READ (standard semantics)
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- returns 10

-- Session B
BEGIN;
INSERT INTO orders (status, amount) VALUES ('pending', 42.00);
COMMIT;

-- Session A, same transaction
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- returns 11
COMMIT;

Repeatable Read locks (or snapshots) the rows it has already read, so the balance-style non-repeatable read is fixed. But it does nothing to stop a brand-new row from being inserted that matches the predicate — that row was never locked because it didn't exist yet.

#How databases actually implement this

Two families of mechanism show up under the hood, and interviewers like to see you name them:

1. Lock-based (2PL — two-phase locking). Readers take shared locks, writers take exclusive locks, and Serializable adds predicate/range locks to stop phantoms. This is accurate but can create heavy blocking and deadlocks.
2. MVCC (multi-version concurrency control). Postgres?, MySQL/InnoDB, and Oracle keep multiple versions of a row and hand each transaction a consistent snapshot, so readers never block writers. Serializable under MVCC (Postgres calls this SSI, serializable snapshot isolation) detects conflicting patterns of reads and writes after the fact and aborts one of the transactions rather than blocking upfront.

#Picking a level in practice

Most teams default to Read Committed for OLTP workloads — it's the Postgres and Oracle default, it prevents the anomaly (dirty reads) that's genuinely dangerous for money-adjacent logic, and it doesn't pay for guarantees most endpoints don't need. Reach for Repeatable Read when a single transaction takes multiple reads of the same data and needs them to agree (a report generator, a multi-step calculation). Reach for Serializable when correctness genuinely depends on transactions not interleaving at all — inventory counters, double-booking prevention, anything where a phantom or a write skew would let two transactions both believe they safely made the last edit.

🧠 Check your understanding
0/1 · 0/1 answered
1. Which isolation level does the SQL standard still permit to have phantom reads?
Read CommittedRead Uncommitted onlyRepeatable ReadSerializable