Isolation is the "I" in ACID, but it is not a yes-or-no property. It is a dial: the database lets you choose how much of the concurrent world a transaction is allowed to see, and every notch toward "safer" costs concurrency, blocking or retries. Interviewers probe it because it separates people who memorised four names from people who know what actually breaks at each level, and because the names mean different things in different engines.
For a Backend or AI engineer this is the knob behind real bugs: an oversold inventory row, a balance that goes negative, a report that does not add up, a retry storm after a serialization failure. This page assumes you know the anomalies themselves (dirty read, non-repeatable read, phantom, lost update); they are drawn out in Concurrency Control, and the machinery that makes snapshots possible is in MVCC Internals. Here the focus is what each level promises and what it costs.
SQL-standard definition (defined by which anomalies are ALLOWED):
Level Dirty read Non-repeatable read Phantom read
----------------- ----------- -------------------- ------------
READ UNCOMMITTED possible possible possible
READ COMMITTED no possible possible
REPEATABLE READ no no possible
SERIALIZABLE no no no
Two anomalies the standard's table does NOT list, but that bite in practice:
lost update - two read-modify-write cycles overwrite each other
write skew - two transactions read overlapping data, each writes a
DIFFERENT row, and together they break an invariant
What real engines do (generally - check your version):
Dirty Non-rep. Phantom Lost update* Write skew
PG READ COMMITTED no possible possible possible possible
PG REPEATABLE READ no no no (snapshot) error (40001) possible
PG SERIALIZABLE no no no error (40001) error (40001)
InnoDB REPEATABLE READ no no (plain mostly no; locking possible for possible
SELECT) reads use gap locks stale writes
InnoDB SERIALIZABLE no no no (reads lock) no no
* "lost update" here means: read a value, compute in app code, write back an absolute value.The standard defines levels by anomalies because that was the easiest way to describe them in the 1980s and 90s, when databases were lock-based. Later work showed that list is incomplete: snapshot-based engines can satisfy "no dirty read, no non-repeatable read, no phantom" and still allow write skew. That is why "Repeatable Read prevents everything except phantoms" is only a rough textbook rule, and why you should always answer with the engine name attached.
PostgreSQL accepts READ UNCOMMITTED but treats it as READ COMMITTED: MVCC gives it no reason to expose uncommitted data. Only a few engines (for example SQL Server with NOLOCK or InnoDB at Read Uncommitted) really let you read dirty data, and doing so is usually a reporting hack, not a design.
-- PostgreSQL. Table: accounts(id, balance); row id=1 has balance 100.
-- Session A -- Session B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id=1; -- 100
BEGIN;
UPDATE accounts SET balance = 150 WHERE id=1;
COMMIT;
SELECT balance FROM accounts WHERE id=1; -- 150 (new snapshot per STATEMENT)
COMMIT;
-- Same script, but Session A used:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id=1; -- 100 (snapshot taken at this first statement)
... B updates and commits 150 ...
SELECT balance FROM accounts WHERE id=1; -- 100 (same snapshot for the whole transaction)
COMMIT;Under Read Committed each statement gets a fresh view of committed data, so two SELECTs in one transaction can disagree. Under Repeatable Read the view is frozen once, so they agree. In PostgreSQL the snapshot is taken when the first query runs, not at BEGIN; in InnoDB it is established by the first consistent (non-locking) read.
Freezing the view has a price for writers. Suppose Session A, still holding the old snapshot, now tries UPDATE accounts SET balance = balance + 10 WHERE id=1 after B committed a change to that row:
-- PostgreSQL, Session A at REPEATABLE READ (snapshot says balance=100), B has committed 150:
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)
-- A must ROLLBACK and retry the whole transaction.
-- Same UPDATE at READ COMMITTED: A waits if B is still uncommitted; once B commits,
-- PostgreSQL re-evaluates the WHERE clause against the NEW row version and applies
-- the increment to 150, giving 160. No error, no retry needed.This is the core trade-off in one example. Read Committed hides conflicts by silently working on the latest data, which is convenient but means your application logic may have been based on stale reads. Repeatable Read refuses to let you build on a stale snapshot, surfaces the conflict as an error, and hands the retry burden to your application.
-- Rule: at least one doctor must stay on call. Alice and Bob are both on call.
-- table on_call(doctor, on_duty BOOLEAN) rows: ('alice', true), ('bob', true)
-- PostgreSQL at REPEATABLE READ (snapshot isolation)
-- Session A (Alice) -- Session B (Bob)
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call
WHERE on_duty; -- 2 SELECT count(*) FROM on_call
WHERE on_duty; -- 2
UPDATE on_call SET on_duty=false
WHERE doctor='alice'; -- ok UPDATE on_call SET on_duty=false
WHERE doctor='bob'; -- ok (different row: no conflict)
COMMIT; -- ok COMMIT; -- ok
-- Result: nobody is on call. Each transaction was individually correct against its snapshot.
-- Same timeline at SERIALIZABLE (PostgreSQL SSI):
-- one of the two COMMITs (or an earlier statement) fails with
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- The application retries it, sees count = 1, and refuses to go off duty.Write skew happens because the two transactions write different rows, so first-committer-wins conflict detection never fires, yet each one's decision depended on a read that the other invalidated. This is precisely the gap between snapshot isolation and true serializability. PostgreSQL closes it with Serializable Snapshot Isolation (SSI): it tracks read-write dependencies between concurrent transactions and aborts one when it sees a dangerous cycle. It does this without making readers block writers; the cost is that some transactions fail and must be retried.
Without SERIALIZABLE, you fix write skew explicitly: lock the rows you depend on (SELECT ... FOR UPDATE on all on-call rows), lock a parent row that represents the invariant, or enforce it with a constraint or trigger where the engine can. All of these are you doing by hand what SERIALIZABLE does for you.
PostgreSQL Default READ COMMITTED. READ UNCOMMITTED == READ COMMITTED.
REPEATABLE READ = snapshot isolation (no phantoms; write skew possible;
concurrent-update conflicts abort with 40001).
SERIALIZABLE = SSI: no blocking on read/write, but aborts + retry.
MySQL/InnoDB Default REPEATABLE READ.
Plain SELECT = consistent read from a snapshot (MVCC).
Locking reads (FOR UPDATE / FOR SHARE), UPDATE, DELETE = "current read"
of the LATEST committed row + next-key locks (record + gap) to block phantoms.
READ COMMITTED: fresh snapshot per statement, far fewer gap locks
(needs row-based binary logging).
SERIALIZABLE: plain SELECTs become locking reads (when autocommit is off)
-> more blocking and more deadlocks.
SQL Server Default READ COMMITTED using shared locks (readers block writers) unless
READ_COMMITTED_SNAPSHOT is ON (then it is row-versioned, closer to PG's RC).
Separate SNAPSHOT level. SERIALIZABLE uses key-range locks.
READ UNCOMMITTED == the NOLOCK hint: genuine dirty reads.The InnoDB split between consistent reads and current reads produces a well-known surprise. In Repeatable Read, a plain SELECT sees your snapshot, but an UPDATE or SELECT ... FOR UPDATE sees the newest committed rows. So you can read a row that "does not exist" in your snapshot, update it, and then your next plain SELECT shows it (because you modified it yourself). It is not a bug; it is what happens when a snapshot is used for reads and latest data is used for writes.
-- MySQL/InnoDB at REPEATABLE READ
-- Session A -- Session B
BEGIN;
SELECT * FROM t WHERE k = 5; -- 0 rows
INSERT INTO t (k) VALUES (5); COMMIT;
SELECT * FROM t WHERE k = 5; -- still 0 rows (snapshot)
UPDATE t SET note='x' WHERE k = 5; -- affects 1 row (current read sees B's row)
SELECT * FROM t WHERE k = 5; -- now 1 row (A's own modification)
COMMIT;Consequently, the sentence "Repeatable Read has phantoms" is true in the standard, not true for PostgreSQL, and only partly true for InnoDB (plain reads are stable, locking reads are protected by gap locks, but the mixed pattern above shows the seam). Never present one engine's behaviour as universal in an interview.
Cost of SERIALIZABLE by implementation:
Lock-based (InnoDB, SQL Server):
readers take shared / range locks -> readers and writers block each other
-> lower throughput, more lock waits, more deadlocks
Snapshot-based (PostgreSQL SSI):
no read blocking, but tracks read/write dependencies (predicate locks in memory)
-> some transactions abort with 40001 -> application MUST retry
-> false positives are possible (aborts that a perfect scheduler would avoid)
Either way: the correctness guarantee is bought with contention or retries,
and long / wide transactions pay the most.SERIALIZABLE is not "free correctness". It is a promise that the outcome equals some serial order, and the database keeps that promise by making you wait or by making you retry. If your code is not written to retry on 40001 (and on InnoDB deadlock errors), moving to SERIALIZABLE turns silent anomalies into visible errors, which is safer but not automatically better for users. It is the right tool when an invariant spans multiple rows and is hard to express as a constraint or a single lock, and the wrong default for a high-throughput OLTP path where a narrower fix would do.
Inventory shows the narrower fixes. Selling the last unit twice is a race on one row, and you rarely need SERIALIZABLE to prevent it:
-- 1. Atomic conditional UPDATE (usually best): the check and the write are ONE statement
UPDATE product SET stock = stock - :qty WHERE id = :id AND stock >= :qty;
-- rows affected = 1 -> reserved; 0 -> out of stock. Any level, no explicit lock,
-- a CHECK (stock >= 0) constraint as a safety net.
-- 2. Pessimistic lock, then decide in application code
BEGIN;
SELECT stock FROM product WHERE id = :id FOR UPDATE; -- others queue on this row
-- if stock >= :qty: UPDATE product SET stock = stock - :qty WHERE id = :id;
COMMIT;
-- 3. Optimistic: version column, retry on 0 rows updated
UPDATE product SET stock = :new_stock, version = version + 1
WHERE id = :id AND version = :seen_version;
-- 4. SERIALIZABLE (or PG REPEATABLE READ) + retry loop on 40001
-- correct without hand-written locks, but every hot-row conflict becomes a retry.Option 1 is the default answer because it keeps the transaction tiny and moves the "is there enough?" decision inside the database. For a flash sale where thousands of buyers hit one row, all four options serialise on that row anyway; the next step is to shard the counter, pre-allocate stock into buckets, or accept orders into a queue and confirm asynchronously. Do not hold a transaction open while calling a payment API; reserve stock first (with an expiry), pay outside the transaction, then confirm or release.
40001); Serializable uses SSI.READ_COMMITTED_SNAPSHOT is on; NOLOCK is Read Uncommitted.UPDATE ... WHERE stock >= qty over raising the isolation level.BEGIN ISOLATION LEVEL ..., SET TRANSACTION ISOLATION LEVEL ...) or relies on the engine default.40001 / a deadlock error, rolls back, and reruns the whole transaction from the start.# Runnable locally with sqlite3 and python3 (two connections, deliberately interleaved).
# SQLite is used only to show the inventory race and its fix; it has no per-statement isolation level menu.
import sqlite3, os
path = "inv.db"
if os.path.exists(path): os.remove(path)
setup = sqlite3.connect(path, isolation_level=None)
setup.execute("CREATE TABLE product (id INTEGER PRIMARY KEY, stock INTEGER NOT NULL CHECK (stock >= 0))")
setup.execute("INSERT INTO product VALUES (1, 1)") # only ONE unit left
a = sqlite3.connect(path, isolation_level=None)
b = sqlite3.connect(path, isolation_level=None)
sa = a.execute("SELECT stock FROM product WHERE id = 1").fetchone()[0]
sb = b.execute("SELECT stock FROM product WHERE id = 1").fetchone()[0]
if sa >= 1: a.execute("UPDATE product SET stock = ? WHERE id = 1", (sa - 1,))
if sb >= 1: b.execute("UPDATE product SET stock = ? WHERE id = 1", (sb - 1,))
print("read-modify-write: A saw", sa, "B saw", sb, "-> stock =",
setup.execute("SELECT stock FROM product").fetchone()[0], "(two orders accepted, one unit existed)")
setup.execute("UPDATE product SET stock = 1 WHERE id = 1")
ra = a.execute("UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock >= 1").rowcount
rb = b.execute("UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock >= 1").rowcount
print("atomic UPDATE: A rows =", ra, "B rows =", rb, "-> stock =",
setup.execute("SELECT stock FROM product").fetchone()[0])
# Actual output:
# read-modify-write: A saw 1 B saw 1 -> stock = 0 (two orders accepted, one unit existed)
# atomic UPDATE: A rows = 1 B rows = 0 -> stock = 0
-- The CHECK constraint as a safety net (sqlite3 :memory:)
CREATE TABLE product (id INTEGER PRIMARY KEY, stock INTEGER NOT NULL CHECK (stock >= 0));
INSERT INTO product VALUES (1, 1);
UPDATE product SET stock = stock - 1 WHERE id = 1;
SELECT changes(), stock FROM product; -- 1|0
UPDATE product SET stock = stock - 1 WHERE id = 1;
-- Runtime error: CHECK constraint failed: stock >= 0
-- PostgreSQL (needs two psql sessions; no output shown because it cannot be run here)
-- Compare READ COMMITTED and REPEATABLE READ using the timeline in the deep dive above:
-- CREATE TABLE accounts (id INT PRIMARY KEY, balance INT);
-- INSERT INTO accounts VALUES (1, 100);
-- Session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id=1;
-- Session B: UPDATE accounts SET balance = 150 WHERE id=1; (autocommits)
-- Session A: SELECT balance ... -- expected: still 100
-- Session A: UPDATE accounts SET balance = balance + 10 WHERE id=1;
-- -- expected: ERROR could not serialize access due to concurrent update
-- Repeat with READ COMMITTED: second SELECT shows 150 and the UPDATE succeeds (160).
-- Check the current level: SHOW transaction_isolation;
Q: Explain isolation levels.
A: Isolation controls how much one transaction can see of another's in-flight and recently committed work. The SQL standard lists four levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) by which anomalies they permit: dirty reads, non-repeatable reads, phantoms. The trade-off is that stronger isolation means more blocking or more aborted-and-retried transactions, so you pick the weakest level that still protects your invariants.
Q: Why not always use SERIALIZABLE?
A: Because the guarantee is paid for at runtime. Lock-based engines (InnoDB, SQL Server) make readers and writers wait for each other and produce more deadlocks; PostgreSQL's SSI avoids read blocking but aborts some transactions with a serialization failure that the application must retry. It is the right choice for multi-row invariants that are hard to express otherwise, and the wrong default for a hot, high-throughput path where an atomic UPDATE or a single row lock does the job.
Q: Which anomalies can occur at different levels?
A: By the standard table: Read Uncommitted allows dirty reads, non-repeatable reads and phantoms; Read Committed allows the last two; Repeatable Read allows only phantoms; Serializable allows none. Real engines deviate: PostgreSQL has no dirty reads at any level and no phantoms at Repeatable Read, but Repeatable Read still allows write skew; InnoDB's Repeatable Read protects plain reads via snapshots and locking reads via gap locks. Lost update and write skew are not in the standard's three-anomaly table, which is why you name the engine when answering.
Q: How would you handle concurrent updates to inventory?
A: Push the check into the write: UPDATE product SET stock = stock - :qty WHERE id = :id AND stock >= :qty and treat "0 rows affected" as out of stock, backed by a CHECK (stock >= 0). Alternatives are SELECT ... FOR UPDATE (simple, but the row becomes a queue), an optimistic version column with retry, or SERIALIZABLE with a retry loop. For a very hot item, no isolation level removes the single-row contention; you shard the counter or accept orders asynchronously.
Follow-up: Does Repeatable Read prevent a lost update?
A: It depends on the engine and on how you wrote the update. In PostgreSQL, a conflicting update to a row changed since your snapshot aborts with 40001, so the lost update becomes an error. In InnoDB, SET stock = stock - 1 is safe because UPDATE reads the latest row, but "read a value into the app, then write an absolute value" can still overwrite a concurrent change, since the plain read came from an older snapshot.
Follow-up: Your service moved to SERIALIZABLE and now sees random failures. What do you do?
A: Those are serialization failures (40001 in PostgreSQL) or deadlock victims (InnoDB), and they are the mechanism working as designed. Wrap the whole transaction in a bounded retry loop with jittered backoff, make the transaction idempotent, keep it short and read only what it needs, and log the retry rate. If the rate stays high, the design is generating hot conflicts and you should narrow the contended data or lock explicitly instead.
Follow-up: Is PostgreSQL's Read Uncommitted a real dirty-read level?
A: No. PostgreSQL accepts the syntax but runs it as Read Committed, because MVCC lets it serve committed versions without any locking cost, so there is no benefit to exposing uncommitted rows. Engines that do allow dirty reads (SQL Server with NOLOCK, InnoDB) do so to avoid lock waits, and the price is reading data that may later vanish.
In a design interview, isolation is a per-path decision rather than a global setting: an order-placement path that decrements stock and writes a payment intent needs a targeted fix (atomic conditional update, a row lock, or a unique-constraint-backed idempotency key), a dashboard query can run at Read Committed or even against a read replica, and a rare multi-row invariant such as "at least one on-call engineer" is a good candidate for SERIALIZABLE with a retry wrapper. The retry wrapper is itself a system-design element: it must re-run the whole transaction including any reads, so anything with external side effects (sending an email, calling a payment provider) must live outside it or be idempotent. Long-running reports should not sit in a Repeatable Read transaction on the primary, because holding an old snapshot open delays cleanup of old row versions; route them to a replica instead. If you say "we will use SERIALIZABLE for safety" without mentioning retries, throughput and hot rows, you have described the switch, not the design.
The decision is how much anomaly protection to buy at the isolation level versus at the query level. Raising the level is a blunt, global change: it covers cases you did not foresee, at the cost of blocking (lock-based engines) or aborts and retry code (snapshot-based engines), plus behaviour that differs between PostgreSQL, InnoDB and SQL Server. Query-level fixes such as atomic updates, FOR UPDATE, version columns and constraints are precise and cheap, but each must be recognised and written correctly by someone, and one missed spot reintroduces the bug. SERIALIZABLE is the wrong choice when transactions are long, touch many rows, or hit hot rows, since abort rates or lock waits grow with contention, and when the application has no reliable retry path. Read Committed is the wrong choice when a transaction makes a decision from several reads that must be mutually consistent (reports, invariants across rows) unless you add explicit locks. There is no universally best level; the honest answer names the invariant, the engine, and the failure mode you are choosing to accept.