Isolation Levels

Hook: Interviewers love this topic because isolation levels are where a database stops being a simple spreadsheet and starts acting like a busy restaurant kitchen.

Question: What are isolation levels in SQL transactions?

Answer: Isolation levels decide how much one transaction can see of another transaction’s uncommitted or recently committed work. In simple words, they control whether readers see a stable view of the data or a more current but less protected view. Higher isolation gives stronger correctness but usually less concurrency, which means more blocking or more retries.

Interview-Ready Answer: I’d say isolation levels are the rules that control what one transaction can observe while other transactions are running. The main trade-off is correctness versus throughput: low isolation allows more concurrency but can expose anomalies like dirty reads or non-repeatable reads, while strong isolation like serializable gives the safest behavior but can block more or force transaction retries. A good practical detail is that many databases default to READ COMMITTED, while MySQL InnoDB often defaults to REPEATABLE READ.

What isolation levels protect you from

Think of each transaction as a person reading and writing notes in a shared notebook. Isolation decides whether they can see someone else’s half-written notes, whether the page can change between two reads, and whether a summary query can suddenly include new rows.

The classic anomalies are:

• Dirty read: reading data another transaction wrote but has not committed yet.
• Non-repeatable read: reading the same row twice and getting a different value because another transaction committed a change in between.
• Phantom read: rerunning a range query and seeing new rows appear or disappear.
• Write skew: two transactions each read a rule, both make different writes, and together they break the rule. This is a subtle one that surprises many candidates.

How it works under the hood

1. The database starts a transaction with an isolation level, such as READ COMMITTED or SERIALIZABLE.
2. For each read, the engine decides whether to use locks, a snapshot, or both. MVCC means multi-version concurrency control: the database keeps multiple row versions so readers can see a consistent picture without always blocking writers.
3. At READ COMMITTED, each statement usually sees the latest committed data at the moment that statement begins.
4. At REPEATABLE READ, the whole transaction typically sees one stable snapshot, so repeated reads return the same committed version.
5. At SERIALIZABLE, the database tries to make concurrent transactions behave as if they ran one after another. If that is not possible, it may abort one transaction with a serialization error so you can retry safely.
6. When the transaction commits or rolls back, locks are released and temporary state is cleaned up, though MVCC version cleanup may happen later in the background.

Common levels and what they mean

Important dialect gotcha: PostgreSQL accepts READ UNCOMMITTED, but it behaves like READ COMMITTED. Also, MySQL InnoDB’s REPEATABLE READ is stronger than many people expect because it uses extra locking to reduce phantoms.

When to use each one

• Use READ COMMITTED for most OLTP work, like user profiles or order history, where fresh data matters and occasional re-reading is acceptable.
• Use REPEATABLE READ when a transaction must work from one stable snapshot, such as building a monthly report.
• Use SERIALIZABLE when correctness is critical, such as money movement, inventory reservation, or enforcing a business rule that spans multiple rows.

Performance and retry cost

There is no meaningful Big-O complexity number for isolation levels. In practice, lower isolation usually means fewer locks and fewer retries, so it scales better under contention. Higher isolation can increase lock waits, row-version checks, memory use for snapshots, and aborted transactions that must be retried. A useful interview phrase is: stronger isolation shifts cost from wrong answers to slower or retried work.

Edge cases that matter in interviews

• SERIALIZABLE does not mean zero failures; it often means the database may reject a transaction and ask you to retry.
• Snapshot-based levels can still allow write skew unless the engine truly enforces serializability.
• Long transactions are expensive because they keep old versions alive longer and can delay vacuum or cleanup in MVCC systems.
• Isolation is separate from durability: a transaction can be fully isolated but still rolled back if the server crashes before commit is made durable.

Real-world story

Scenario: a flash-sale checkout service for an e-commerce app.

Two users click Buy at almost the same time for the last item in stock. If the checkout transaction reads stock, then writes a decrement, and the isolation is too weak, both requests can see stock 1 and both try to reserve it. The result is overselling, angry users, and a support flood.

What goes wrong: the database log may show lock waits, deadlocks, or serialization failures; the application may show a confusing mix of succeeded payments and failed shipments. Users see messages like item sold out after payment, or the order count in admin dashboards does not match the warehouse count. In a stronger isolation design, the app either blocks one checkout briefly or retries a transaction cleanly instead of silently corrupting inventory.

Mental model: the database is the bouncer at the door; isolation levels decide how tightly it checks who can peek at the guest list while changes are happening.

Follow-up & Tricky Questions:

• What anomalies does each isolation level prevent? A strong answer names dirty read, non-repeatable read, phantom read, and write skew, then maps them to the level that blocks or reduces them.
• What is the difference between isolation and locking? Locks are one mechanism; isolation is the guarantee. Some engines also use MVCC snapshots, so isolation can be provided without every read blocking every write.
• Why might serializable transactions fail even when the SQL is correct? Because the database may detect a dangerous concurrent pattern and abort one transaction to preserve a serial order. The app must retry idempotently.
• What isolation level does your database use by default? You should answer by database: PostgreSQL is usually READ COMMITTED, MySQL InnoDB often REPEATABLE READ, and SQL Server commonly READ COMMITTED.
• How do you choose an isolation level for a money transfer? Usually you use the strongest practical setting, plus explicit constraints and retries, because correctness matters more than raw throughput.
• Tricky: does REPEATABLE READ always stop phantom reads? Not universally. It depends on the database engine; some use extra locks, some use snapshots, and some still need serializable to fully prevent phantoms and write skew.
• Tricky: is READ UNCOMMITTED always faster? Not necessarily. It can still contend on writes, and many systems do not truly expose dirty reads in the way people assume.
• Tricky: does higher isolation mean fewer bugs and zero retries? No. It often means fewer logical anomalies, but more blocking or retryable failures under load.

Common Mistakes:

• Mixing up isolation with durability. Fix: isolation is about what concurrent transactions can see; durability is about surviving crashes after commit.
• Saying serializable means no aborts. Fix: serializable often allows aborts so the database can preserve correctness.
• Forgetting write skew. Fix: mention that snapshot-style isolation can still violate business rules across multiple rows unless the engine truly enforces serializability.
• Assuming every database implements the ANSI levels identically. Fix: always name the engine, because PostgreSQL, MySQL, SQL Server, and Oracle differ in details and defaults.

Memory Hook: Isolation is the restaurant rule for shared plates: do you see half-cooked food, the final plated dish, or only a single frozen photo of the kitchen? That picture helps you remember READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE from weakest to strongest.

Cheat Sheet:

• Isolation controls what one transaction can observe from another.
• Weak isolation = more concurrency, more anomalies.
• Strong isolation = fewer anomalies, more blocking or retries.
• Know the four names: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Remember the tricky ones: phantom read and write skew.
• Always mention database-specific defaults and behavior.

Practice Tasks:

• Write a transaction that reads a balance twice and explain which isolation level keeps the value stable.
• Look up the default isolation level for PostgreSQL, MySQL InnoDB, and SQL Server, then memorize the differences.
• Design an order-reservation flow and decide whether you would use READ COMMITTED plus locks or SERIALIZABLE plus retries.