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

A non-repeatable read happens when a transaction reads the same row twice and sees different values because another transaction updated it in between. A phantom read happens when a transaction re-runs a query with a search condition and sees new rows that another transaction inserted. Repeatable read prevents the former; only serializable reliably prevents phantoms.

Is snapshot isolation the same as serializable?

No. Snapshot isolation gives each transaction a consistent snapshot and prevents dirty and non-repeatable reads, but it is not fully serializable: it permits write-skew anomalies where two transactions read overlapping data and make disjoint updates that together violate a constraint. Serializable snapshot isolation extends it to eliminate such anomalies.

Isolation Levels

Isolation levels define how much a transaction is shielded from the effects of other concurrent transactions, trading away some isolation guarantees in exchange for higher concurrency and throughput.

Trova un argomento con PaperMindIn arrivoFind papers & topics

Tools & resources

Scarica le diapositive

Learn & explore

VideoIn arrivo

Definition

An isolation level is a specification of which concurrency anomalies a transaction may observe; higher levels prevent more anomalies (up to full serializability) at the cost of reduced concurrency, while lower levels permit certain anomalies in exchange for performance.

Scope

This topic covers the standard isolation levels — read uncommitted, read committed, repeatable read, and serializable — defined in terms of the phenomena (dirty read, non-repeatable read, phantom) each permits or prevents, the influential critique that reframed them in terms of allowed anomalies, and the practically important snapshot isolation and its anomalies such as write skew. It treats the trade-off between consistency and concurrency. It excludes the underlying locking and multiversion mechanisms that implement these levels.

Core questions

What anomalies — dirty read, non-repeatable read, phantom — does each isolation level permit?
How does the SQL standard define the four isolation levels?
Why did researchers critique the anomaly-based definitions as ambiguous?
Where does snapshot isolation fit, and what anomalies (such as write skew) can it allow?
How do applications choose an isolation level for a given workload?

Key concepts

read uncommitted
read committed
repeatable read
serializable
dirty read
non-repeatable read
phantom read
snapshot isolation and write skew

Key theories

Anomaly-based isolation levels: The SQL standard defines isolation levels by which of three phenomena — dirty reads, non-repeatable reads, and phantoms — they allow, ranging from read uncommitted (all permitted) to serializable (none permitted).
Critique of ANSI isolation levels: Berenson and colleagues showed the standard's phenomenon-based definitions are ambiguous and do not cleanly characterize multiversion systems, and they introduced snapshot isolation as a distinct, widely implemented level not captured by the original definitions.
Snapshot isolation and its anomalies: Under snapshot isolation each transaction reads a consistent snapshot as of its start, giving strong read behavior without read locks, but it can permit non-serializable anomalies such as write skew, motivating serializable snapshot isolation.

Clinical relevance

Isolation level is a routine but consequential configuration choice for application developers: picking too weak a level can silently corrupt data through subtle anomalies, while always using serializable can throttle throughput, so understanding the levels is essential for correct, performant transactional applications.

History

The ANSI/ISO SQL standard defined isolation levels in terms of permitted phenomena. The 1995 critique by Berenson, Bernstein, Gray, and the O'Neils exposed ambiguities in those definitions and characterized snapshot isolation, which mainstream multiversion databases adopted. Later work defined serializable snapshot isolation to close the remaining anomaly gap.

Debates

Defining isolation by anomalies versus by serializability: The standard's anomaly-list definitions are easy to state but ambiguous and do not fit multiversion systems; an alternative is to define correctness directly via serializability, which is cleaner but harder to map onto the levels developers configure in practice.

Key figures

Jim Gray
Philip Bernstein
Hal Berenson

Seminal works

berenson1995
gray1992

Frequently asked questions

What is the difference between a non-repeatable read and a phantom read?: A non-repeatable read happens when a transaction reads the same row twice and sees different values because another transaction updated it in between. A phantom read happens when a transaction re-runs a query with a search condition and sees new rows that another transaction inserted. Repeatable read prevents the former; only serializable reliably prevents phantoms.
Is snapshot isolation the same as serializable?: No. Snapshot isolation gives each transaction a consistent snapshot and prevents dirty and non-repeatable reads, but it is not fully serializable: it permits write-skew anomalies where two transactions read overlapping data and make disjoint updates that together violate a constraint. Serializable snapshot isolation extends it to eliminate such anomalies.