Isolation levels
DBMS · 10 interview questions
Perfect isolation would mean running transactions one at a time, which is far too slow, so databases offer weaker levels that permit specific anomalies in exchange for concurrency.
The SQL standard defines four levels by which anomalies they allow. Read uncommitted allows dirty reads. Read committed prevents dirty reads but allows non-repeatable reads. Repeatable read prevents those but allows phantoms. Serializable allows none.
The important caveat is that the standard describes anomalies, not implementations, and real databases don't line up neatly. MySQL's InnoDB prevents phantoms at repeatable read using next-key locks, and PostgreSQL's repeatable read is snapshot isolation, which also avoids phantoms. Naming the level is half an answer; naming the anomaly you're avoiding is the whole one.
Isolation levels interview questions
- What is a dirty read?
- Reading data written by another transaction that hasn't committed yet. If that transaction rolls back, you acted on a value that never officially existed.
- What is a non-repeatable read?
- Reading the same row twice in one transaction and getting different values, because another transaction committed an update in between.
- What is a phantom read?
- Running the same range query twice and getting a different set of rows, because another transaction inserted or deleted rows matching the condition. It's about the set of rows, not the values in one row.
- Why they ask: The distinction from non-repeatable read is the point — rows appearing versus values changing.
- List the four isolation levels weakest to strongest.
- Read uncommitted, read committed, repeatable read, serializable. Each one forbids everything the weaker levels forbid, plus one more anomaly.
- Which anomalies does each level allow?
- Read uncommitted: dirty, non-repeatable, phantom. Read committed: non-repeatable, phantom. Repeatable read: phantom. Serializable: none.
- What are the common defaults?
- PostgreSQL, Oracle and SQL Server default to read committed. MySQL's InnoDB defaults to repeatable read. Assuming your database's default applies everywhere is a reliable way to get caught out.
- Does MySQL allow phantom reads at repeatable read?
- In practice no — InnoDB uses next-key locking, which locks index ranges as well as rows, so inserts into a scanned range are blocked. The SQL standard permits phantoms at this level; InnoDB is stricter than required.
- Why they ask: A strong answer: it shows you know the standard and the implementation are different things.
- What is snapshot isolation?
- Each transaction reads from a consistent snapshot taken at its start, so readers never block writers and see no other transaction's concurrent changes. PostgreSQL's repeatable read is snapshot isolation.
- What anomaly can snapshot isolation still allow?
- Write skew. Two transactions read overlapping data, each checks a condition that's still true in its own snapshot, and both write — leaving a combined state that violates the rule neither broke alone. Classic case: two doctors both cancelling an on-call shift that required at least one to remain.
- Why not always use serializable?
- Throughput. It requires either heavy locking or aborting-and-retrying conflicting transactions, so it reduces concurrency and pushes retry handling into application code.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days