Data consistency
Transaction isolation levels: interview questions and how to answer them
An isolation level defines which effects concurrent transactions may observe, trading anomalies against blocking, aborts, and database overhead.
Written and reviewed by Sahil Srivastav
What it actually is
The standard names are read uncommitted, read committed, repeatable read, and serialisable, but implementations differ. Isolation is about concurrent transactions, not how long a transaction remains open.
The useful design starts from an anomaly: dirty reads, non-repeatable reads, phantoms, lost updates, or write skew. Choose the weakest level that prevents the anomaly, then add explicit locks or predicates where needed.
Why it matters in production
Choosing “the strongest” everywhere can turn normal traffic into lock waits or serialisation failures. Choosing a weak level because it is faster can permit an oversell or inconsistent report.
The database’s default is not a business guarantee. A read committed transaction can still make a stale decision if the application reads and writes in separate transactions.
How it works
Read committed
Each statement generally sees data committed before that statement begins. Two statements in one transaction can observe different committed versions.
Repeatable read
A transaction keeps a stable snapshot for reads in systems such as PostgreSQL, while writes that conflict may abort. MySQL’s locking behaviour differs.
Serialisable
The result must be equivalent to some serial order. Engines may block or abort a transaction when they detect a dangerous structure.
Explicit locks
FOR UPDATE, predicate locks, and unique constraints supplement isolation when the invariant needs a specific row or range protected.
Detailed boundary
anomaly-driven level selection
Operational consequence
lock and abort costs
Implementing it
Write the anomaly the transaction must exclude before selecting a level.
Treat deadlock and serialisation errors as expected retryable outcomes with bounded retry.
Use `EXPLAIN` and lock/transaction views to diagnose waits rather than raising isolation blindly.
Use a two-sided test for this boundary: drive the normal path and the failure path concurrently, then inspect the state that survives the race. For transaction isolation levels, the useful assertion is the invariant after recovery, not merely a successful response from one caller.
Document the limit and the signal that tells an operator to change it. A production review of transaction isolation levels should name the protected resource, the caller deadline, the expected overload decision, and the evidence that would distinguish a local bug from downstream saturation.
A focused review of transaction isolation levels should separate the mechanism from its policy. Reproduce one normal request, one boundary case, and one concurrent failure; record the state transition, the resource consumed, and the signal an operator would see. Then state what the caller is allowed to retry and what must be reconciled manually. This makes transaction isolation levels testable in a repository rather than a vocabulary answer.
Interview questions and how to answer them
What does read committed allow?
A transaction cannot read another transaction’s uncommitted data, but separate statements may see different committed versions.
Why can serialisable abort?
Preventing an anomaly may require rejecting one transaction rather than blocking every dependency indefinitely. The application must retry safely.
Does repeatable read prevent lost updates?
It depends on the engine and access pattern. A read-modify-write should still use an atomic predicate, row lock, or version check.
How do you choose a level?
Start with the invariant and anomaly, then pick the minimum mechanism that proves it, measuring contention and aborts under load.
What evidence would you inspect for transaction isolation levels?
Measure the boundary named in the design, compare it with the caller deadline and resource budget, and reproduce the contention or failure with more than one concurrent worker.
What is the tempting fix for this problem?
Changing a timeout, pool, or retry count alone usually moves the queue. First establish the invariant, then make the bounded mechanism and its failure outcome explicit.
Answers that lose the round
- Assuming repeatable read prevents every write skew
- Calling read committed “no dirty reads, therefore safe”
- Retrying serialisation failures without an idempotent request
- Leaving transactions open while waiting for user input
- Assuming all engines implement names identically
- Using serialisable to hide a missing database constraint
- Treating the local mechanism as a complete production guarantee
- Changing the limit without measuring the resource it protects
FAQ
Is serialisable always slow?
It can be efficient for short transactions, but contention creates waits or aborts. The workload shape matters more than the label.
Are isolation and consistency synonyms?
No. Isolation is one ACID property; consistency is the validity of the state after a transaction.
Can a lock replace isolation?
A lock can protect a particular invariant, but range and predicate interactions may still require an appropriate isolation model.