What isolation levels really guarantee, and where they leak

Two transactions read the same account balance of $100, both add $50, both commit, and the balance ends at $150 instead of $200. If you can explain why that happens and name the isolation level that would have caught it, you’ve answered the question most interviewers are circling when they bring up ACID.

The I in ACID is the letter people rush past on the way to atomicity and durability, and it’s the one that produces the hard follow-ups. Atomicity and durability have tidy definitions. Isolation is a dial with four settings, and every setting trades correctness against concurrency in a way you need to reason about on the spot.

The anomalies the standard is defined against

The ANSI SQL isolation levels aren’t defined by how they work internally. They’re defined by which read anomalies they forbid, so you have to know the anomalies first.

A dirty read is when your transaction reads a row another transaction has written but not yet committed. If that other transaction rolls back, you acted on data that never existed. A non-repeatable read is when you read the same row twice in one transaction and get two different values, because someone committed an update in between. A phantom read is subtler: you run the same range query twice (say, all orders over $1,000) and the second run returns extra rows, because another transaction inserted matching records. The first two are about a row changing under you. The phantom is about the set of rows changing.

This is the grid interviewers expect you to reproduce from memory:

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

Memorize the shape, not the individual cells: each level down the list forbids one more anomaly, and Serializable forbids everything. The catch is that this table describes the 1992 standard, and almost no production database matches it exactly.

What the levels are really called in the databases you’ll be asked about

PostgreSQL defaults to Read Committed. It accepts Read Uncommitted as a setting but treats it as Read Committed, because its MVCC design never exposes uncommitted rows in the first place. So Postgres has three real levels, not four. Its Repeatable Read is actually snapshot isolation: every statement in the transaction sees a frozen snapshot taken at the transaction’s start, which happens to prevent phantom reads too, going past what the standard requires.

MySQL’s InnoDB engine defaults to Repeatable Read, which trips people up because it’s a stricter default than Postgres. InnoDB uses next-key locking (gap locks) to stop phantoms on locking reads, so its Repeatable Read is closer to the standard’s Serializable for a lot of workloads. Oracle offers only Read Committed and Serializable, and its Serializable is snapshot isolation under the hood. SQL Server defaults to Read Committed with locking, but can run Read Committed Snapshot Isolation, which changes the behavior without changing the name.

The point an interviewer wants: the level names are a shared vocabulary, but the guarantees behind them vary by engine. If you say “we’ll use Repeatable Read,” a good interviewer asks “on which database, and do you mean the standard’s version or snapshot isolation?” Knowing that distinction exists is most of the points.

Write skew, the anomaly that outlives snapshot isolation

Snapshot isolation feels bulletproof until you meet write skew, and this is where senior candidates separate from the rest. Picture an on-call system with one rule: at least one doctor must stay on call. Two doctors are on call. Each opens a transaction, each reads the count and sees two on call, each concludes it’s safe to remove themselves, and each commits. No row they wrote overlaps, so snapshot isolation is happy. Now zero doctors are on call and the invariant is broken.

Snapshot isolation prevents anomalies on the same rows. Write skew happens across different rows that share a constraint, and only true serializability catches it. PostgreSQL’s Serializable level uses Serializable Snapshot Isolation (SSI), which tracks read/write dependencies between transactions and aborts one when it detects a dangerous cycle. That’s why code running at Serializable has to be ready to retry on a serialization failure. If a candidate designing a booking or ledger system never mentions retries, that’s a gap worth probing.

Fixing a lost update without turning the whole system serializable

The bank-balance bug at the top is a lost update, and you rarely fix it by raising the global isolation level, which would slow every unrelated query. You fix the specific write.

-- the naive version that loses updates
SELECT balance FROM accounts WHERE id = 42;   -- app reads 100
-- ... app computes 100 + 50 in memory ...
UPDATE accounts SET balance = 150 WHERE id = 42;

-- do the arithmetic in the database, in one atomic statement
UPDATE accounts SET balance = balance + 50 WHERE id = 42;

-- or hold a row lock for the length of the transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance + 50 WHERE id = 42;
COMMIT;

The single-statement UPDATE is atomic at the row level regardless of isolation level. SELECT ... FOR UPDATE is pessimistic locking: you grab the row so no one else can write it until you commit, at the cost of blocking. The third option, which interviewers like to hear, is optimistic concurrency: add a version column, read it, and write with WHERE version = :old_version, so a conflicting commit makes your update touch zero rows and you retry. Pessimistic locking wins under heavy contention; optimistic wins when conflicts are rare and you don’t want readers blocking each other.

What they actually ask

The questions tend to arrive in a predictable order, from definition to design:

• “Walk me through the difference between a non-repeatable read and a phantom read.” (They want the row-versus-set distinction.)
• “Postgres and MySQL ship with different default isolation levels. Which are they, and why does it matter?”
• “Two users check out the last item in stock at the same moment. How do you stop overselling?”
• “You’re at Repeatable Read and still hit a concurrency bug. What class of bug is still possible?”
• “When would you reach for Serializable, and what does it cost you?”

The last-item-in-stock question is the one that most often turns into a real design discussion. The clean answer is a conditional update: UPDATE inventory SET qty = qty - 1 WHERE item_id = :id AND qty > 0, then check whether one row was affected. No explicit lock, no raised isolation level, and the database enforces the invariant for you. If you jump straight to “set the transaction to Serializable,” you’re technically right and practically slow, and a sharp interviewer will ask what that does to throughput.

Reasoning out loud without memorizing everything

You won’t recall every engine’s quirks under pressure, and you don’t need to. The move that reads as senior is to name three things in sequence: the anomaly you’re worried about, the isolation level or lock that prevents it, and the concurrency you give up to get it. “Overselling is a lost update, I’d guard it with a conditional update or a row lock rather than Serializable, because Serializable would serialize unrelated checkouts and hurt throughput.” That sentence shows you understand the mechanism and the tradeoff, which is the whole point of asking.

The engineers who struggle with this treat isolation as a correctness switch you flip to maximum and forget. The ones who do well treat it as a budget: they buy exactly the isolation each operation needs, and they can name both the bug they’re preventing and what it costs.