CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 3 of 5 · 1 min read

Non-repeatable reads and phantom reads

Reading the same query twice within one transaction and getting a different answer, in two different ways.

Think of checking a shelf's item count twice during one shopping trip, only to get two different numbers because someone else quietly restocked it in between the two checks.

In plain words: a non-repeatable read happens when a transaction reads the same specific row twice, and gets a different value the second time, because another transaction committed a change to that row in between. A phantom read is a related but distinct anomaly: a transaction re-runs the same filtering query twice, and the set of rows matching the condition has changed, because another transaction inserted or deleted a row that newly matches or stops matching in between. The difference is a non-repeatable read concerns one specific row's value changing, while a phantom concerns the set of rows itself changing.

A worked example. Reading orders twice for order_id = 501 and getting a different status value each time is a non-repeatable read; running WHERE status = 'placed' twice and getting a different number of rows each time, because a new order was inserted in between, is a phantom read.

The key lines. No new SQL; both anomalies describe what can happen across two reads within the same transaction under weaker isolation levels.

Non-repeatable read, phantom read

A non-repeatable read is getting a different value for the same row on a second read within one transaction; a phantom read is getting a different set of matching rows on a second identical query within one transaction.

Example: a shelf's single item count changing between two checks is a non-repeatable read; the set of items matching a search changing between two searches is a phantom read.