Dirty, Non-Repeatable and Phantom Reads

Isolation levels exist to prevent specific concurrency bugs called read anomalies. Interviewers expect you to define all three precisely and map each to the level that stops it.

• Dirty read - reading uncommitted data
• Non-repeatable read - a row changes between two reads
• Phantom read - new rows appear between two reads

The trick is distinguishing non-repeatable from phantom, because both involve re-querying and getting different results.

A dirty read happens when transaction T1 reads a row that transaction T2 has modified but not yet committed. If T2 then rolls back, T1 has acted on data that never truly existed.

Only READ UNCOMMITTED permits dirty reads. Every higher level forbids them.

Real-world danger: approving a loan based on a deposit that gets rolled back seconds later.

Read the two columns as a timeline. T1 is running at READ UNCOMMITTED.

T1 sees balance 700, but T2 never commits. The 700 was a phantom of T2's in-progress work. After T2 rolls back, the real value is still 500. T1 made a decision on garbage data.

A non-repeatable read happens when T1 reads a row, T2 commits an update or delete to that same row, and T1 reads it again and sees a different value.

Note the key difference from a dirty read: here T2 has committed. The data is real, but it changed underneath T1 within a single transaction.

READ COMMITTED still allows this. REPEATABLE READ and higher prevent it by reading from a stable snapshot.

T1 runs at READ COMMITTED and reads the same row twice. Between the reads, T2 commits a change.

The same primary key returns two different values inside one transaction. That inconsistency can break multi-step logic that assumes the row is stable.

A phantom read happens when T1 runs a query with a search condition, T2 commits an INSERT (or DELETE) of rows matching that condition, and T1 re-runs the query and sees a different set of rows.

The distinction from a non-repeatable read: a non-repeatable read is about an existing row's value changing; a phantom is about the number of rows matching a predicate changing.

Only SERIALIZABLE is guaranteed by the standard to prevent phantoms.

T1 counts high-value accounts twice. Between the counts, T2 inserts a new qualifying row and commits.

No existing row changed, yet the COUNT differs. The new row is the "phantom" that appeared in T1's result set.

This mapping is the heart of the topic. The lowest level that prevents each anomaly:

• Dirty read prevented from READ COMMITTED upward.
• Non-repeatable read prevented from REPEATABLE READ upward.
• Phantom read prevented from SERIALIZABLE (per the standard).

Notice the names line up: REPEATABLE READ makes reads repeatable; the levels are named after the anomaly they newly fix.

The most common interview mix-up. Hold onto this one sentence:

Non-repeatable read = an existing row's value changed. Phantom read = the set of matching rows changed (rows added or removed).

Test yourself: T2 runs UPDATE ... WHERE id = 5 then commits, and T1 re-reads row 5. That is non-repeatable. T2 runs INSERT of a new row matching T1's WHERE, and T1 re-runs the query. That is a phantom.

Senior interviews may push past the three standard anomalies to write skew: two transactions each read an overlapping set, make disjoint writes based on what they read, and both commit, leaving a state neither would have allowed alone.

Classic example: two doctors are on call; each checks that another doctor is on call and then takes themselves off shift. Both succeed, leaving zero coverage.

Snapshot isolation (Postgres REPEATABLE READ) permits write skew; only SERIALIZABLE stops it. Mentioning this signals depth.

Interviewers sometimes slip in lost update, which is not in the standard's anomaly list but appears constantly in practice. Two transactions read the same value, both compute a new value from it, and both write back. The second write silently overwrites the first.

Example: two transfers each read balance 500, each subtract an amount, and each write their result. One subtraction is lost.

The fix is not just a higher isolation level but explicit locking with SELECT ... FOR UPDATE, or an atomic update that computes in the database rather than in the application.

Identify the anomaly from its behavior.

Three read anomalies, each cured by a higher isolation level:

• Dirty read (uncommitted data) - cured at READ COMMITTED.
• Non-repeatable read (existing row value changes) - cured at REPEATABLE READ.
• Phantom read (matching row set changes) - cured at SERIALIZABLE.

Keep the sharp line between non-repeatable and phantom, and drop write skew if the interviewer wants more. Next we look at how engines actually enforce isolation: locking, deadlocks, and MVCC.