skip to content

Explain dirty read, non-repeatable read, and phantom read, and which isolation level prevents each.

level: middleimportance: must knowfreq 70%

answer

  1. Dirty = uncommitted, may roll back
  2. Non-repeatable = same row, value changed (UPDATE)
  3. Phantom = same predicate, rows appear/vanish (INSERT/DELETE)
  4. Staircase: RC stops dirty, RR stops non-repeatable, SER stops phantom
  5. Standard = minimum; real DBs may prevent more

basics

~20 s

Dirty read = seeing another transaction's uncommitted data. Non-repeatable read = re-reading a row and getting a changed value. Phantom read = re-running a query and getting new rows. READ_COMMITTED stops dirty, REPEATABLE_READ stops non-repeatable, SERIALIZABLE stops phantoms.

solid answer

~40 s

These are the three read anomalies from the SQL standard. A **dirty read** is reading a row another transaction has modified but not yet committed — that data may be rolled back. A **non-repeatable read** is reading the same row twice in one transaction and getting different values because another transaction committed an UPDATE in between. A **phantom read** is running the same range query twice and seeing new rows because another transaction committed an INSERT (or DELETE) matching the predicate. The standard prevention ladder: READ_UNCOMMITTED prevents nothing; READ_COMMITTED prevents dirty reads; REPEATABLE_READ additionally prevents non-repeatable reads; SERIALIZABLE additionally prevents phantom reads. Each higher level prevents everything the lower one does plus one more anomaly, at the cost of more locking or serialization failures. In Spring you pick the level with `@Transactional(isolation = ...)`.

code

java · 10 lines
java
// Guarantee that re-reading rows inside this method stays stable
// (no dirty and no non-repeatable reads), while accepting phantoms.
@Transactional(isolation = Isolation.REPEATABLE_READ)
public Report buildReport(long customerId) {
    var first  = orders.findByCustomer(customerId);   // read #1
    // ... other work; a concurrent committed UPDATE to these
    // rows will NOT change what we see on re-read ...
    var second = orders.findByCustomer(customerId);   // same values as read #1
    return Report.of(first, second);
}

go deeper

for a junior

Can define the three anomalies with a simple example each.

for a middle

Reproduces the full prevention ladder and distinguishes non-repeatable from phantom precisely.

for a senior

Explains the locking/snapshot mechanisms behind each level and why cost rises.

for a principal

Notes the standard is a minimum and that engine-specific behavior (Postgres snapshots, InnoDB next-key locks) often exceeds it.

## The three anomalies The SQL standard defines isolation levels by which of three read phenomena they allow. ### 1. Dirty read Transaction A updates a row but has **not committed**. Transaction B reads that updated value. If A then rolls back, B has read data that never officially existed. ``` A: UPDATE account SET balance = 0 WHERE id = 1; -- not committed B: SELECT balance FROM account WHERE id = 1; -- reads 0 (dirty!) A: ROLLBACK; -- the 0 never happened ``` ### 2. Non-repeatable read Transaction B reads a row, then reads the **same row again** later in the same transaction and gets a **different value**, because Transaction A committed an UPDATE in between. ``` B: SELECT balance FROM account WHERE id = 1; -- 100 A: UPDATE account SET balance = 50 WHERE id = 1; COMMIT; B: SELECT balance FROM account WHERE id = 1; -- 50 (non-repeatable!) ``` The distinction from a dirty read: here A **committed**, and the problem is that the same query on the *same row* is inconsistent within B. ### 3. Phantom read Transaction B runs a query over a **range/predicate**, then re-runs it and sees **new or vanished rows**, because Transaction A committed an INSERT or DELETE matching that predicate. ``` B: SELECT count(*) FROM account WHERE balance > 100; -- 3 rows A: INSERT INTO account(balance) VALUES(500); COMMIT; B: SELECT count(*) FROM account WHERE balance > 100; -- 4 rows (phantom!) ``` The distinction from non-repeatable read: non-repeatable is about an existing row's value changing; phantom is about the **set of rows matching a condition** changing. ## The prevention ladder (SQL standard) | Level | Dirty read | Non-repeatable read | Phantom read | |---|---|---|---| | READ_UNCOMMITTED | Possible | Possible | Possible | | READ_COMMITTED | **Prevented** | Possible | Possible | | REPEATABLE_READ | Prevented | **Prevented** | Possible | | SERIALIZABLE | Prevented | Prevented | **Prevented** | Each step up prevents one additional anomaly. Memorize the staircase: dirty falls at READ_COMMITTED, non-repeatable at REPEATABLE_READ, phantom at SERIALIZABLE. ## Why the cost rises - READ_COMMITTED typically holds read locks only for the instant of the read (or uses row versions). - REPEATABLE_READ must keep the rows it read stable — via longer-held read locks or a consistent snapshot. - SERIALIZABLE must also stop new rows from appearing — via range/predicate (next-key) locks or serialization-conflict detection that aborts transactions. So higher levels mean more blocking, more deadlocks, or more retryable serialization failures. ## Real-world caveat The standard is a **minimum** guarantee. Real engines often prevent *more* than the table says. PostgreSQL's snapshot-based REPEATABLE_READ also blocks phantoms; MySQL InnoDB's REPEATABLE_READ uses next-key locking to block many phantoms too. So the SQL-standard table describes what a level is *allowed* to permit, not necessarily what a given DB actually permits.

  • What is the difference between a non-repeatable read and a phantom read?
    A non-repeatable read is when an already-read row's value changes on re-read (caused by a committed UPDATE to that row). A phantom read is when the set of rows matching a query predicate changes (caused by a committed INSERT or DELETE). One is about a row's value, the other about which rows match.
  • Does REPEATABLE_READ ever prevent phantom reads in practice?
    Per the SQL standard it is not required to, but real engines often do: PostgreSQL's snapshot-based REPEATABLE_READ prevents phantoms, and MySQL InnoDB uses next-key locks to prevent most phantoms at REPEATABLE_READ. So actual behavior can exceed the standard's minimum.

saying these in an interview costs you the question

  • Confusing dirty read (uncommitted) with non-repeatable read (committed UPDATE)
  • Saying non-repeatable read and phantom read are the same thing
  • Claiming READ_COMMITTED prevents non-repeatable reads
  • Stating the standard table is exactly what every DB does

context

resolving links…