What are transaction isolation levels, and what anomalies do they prevent?

Question

Accepted Answer

Isolation levels exist because perfect isolation is expensive, so the database lets me choose how strict to be, trading correctness guarantees against concurrency. The clearest way in is the anomalies. A dirty read is seeing another transaction's uncommitted changes, which might roll back. A non repeatable read is reading the same row twice inside one transaction and getting 2 different values because someone committed a change in between. A phantom read is re-running a query and finding new rows that another transaction inserted. The 4 standard levels map onto those. Read uncommitted allows dirty reads and is almost never used. Read committed prevents dirty reads and is the common default, for example in Postgres and in SQL Server. Repeatable read additionally prevents non repeatable reads. Serializable is full isolation, the outcome is as if transactions ran one at a time, so phantoms are gone too. The trade off is real. Higher isolation means more locking and contention, or in an MVCC database more serialization failures that I have to catch and retry, and on a hot table that can visibly cut throughput. So my approach is to pick the lowest level that is still correct for the workload, which is usually read committed, and escalate only the handful of transactions that genuinely need the stronger guarantee, a month end financial rollup for instance, to repeatable read or serializable, rather than cranking everything to serializable and paying for it on every query.

Answer

What is the difference between a non-repeatable read and a phantom read?

Non-repeatable: a row you already read changes value when you read it again. Phantom: re-running a range query returns new rows that were inserted. Repeatable read stops the former; serializable stops both.

Answer

Why not just use serializable everywhere?

It is the most expensive, more locking/contention, or more transaction aborts to retry in MVCC systems, which reduces throughput. Use the lowest level that is correct, usually read committed, and escalate only where needed.

What are transaction isolation levels, and what anomalies do they prevent?

What are transaction isolation levels, and what anomalies do they prevent?

The follow-ups they ask next

What the interviewer is listening for

What sinks the answer

If you genuinely do not know

Keep going with databases

Knowing the answer is not the same as recalling it under pressure