Chapter 13 · Transactions and Concurrency
Dirty Reads, Non-Repeatable Reads, and Phantom Reads
Concurrency anomalies are easiest to understand as histories: two correct transactions interleave, yet one observes a state that would not appear in a clean serial execution.
Learning outcomes
Diagnose what concurrent transactions can observe
Define dirty reads, non-repeatable reads, and phantom reads using transaction schedules.
Distinguish a changed value from a changed qualifying set of rows.
Explain why preventing dirty reads does not automatically guarantee repeatable results.
Understand SQLite rollback-journal and WAL visibility at a conceptual level.
Choose verification and isolation strategies based on the anomaly that matters.
Serial execution is the reference model
If transaction T₁ completes before T₂ begins, the history is serial. Concurrent execution is correct when its committed effect is equivalent to some accepted serial order, subject to the selected isolation level.
Lower isolation levels intentionally accept more histories for throughput or compatibility.
The three classic read anomalies
| Anomaly | What transaction T₁ observes | Concurrent action by T₂ |
|---|---|---|
| Dirty read | A value written but not committed, which may later disappear. | T₂ writes, T₁ reads it, then T₂ rolls back. |
| Non-repeatable read | The same identified row has a different committed value when reread. | T₂ updates or deletes that row and commits between T₁ reads. |
| Phantom read | Repeating a predicate returns a different qualifying set. | T₂ inserts, deletes, or changes rows so they enter or leave the predicate. |
Dirty-read schedule
Initial: account 1 balance = 12,500T1: BEGINT2: BEGINT2: UPDATE account 1 SET balance = 0 -- not committedT1: SELECT balance FROM account 1 -- reads 0T2: ROLLBACK -- 0 never becomes committedT1: acts on a value that never existed in committed historyNormal SQLite connections do not expose uncommitted changes from another connection. A special shared-cache configuration combined with PRAGMA read_uncommitted = ON is an exception and should not be treated as the default model.
Non-repeatable-read schedule
Initial: product SQL-TXN price = 45.00T1: BEGINT1: SELECT price WHERE sku = 'SQL-TXN' -- 45.00T2: BEGINT2: UPDATE product SET price = 49.00T2: COMMITT1: SELECT price WHERE sku = 'SQL-TXN' -- 49.00T1: COMMITThe row identity is the same; its visible committed value changes between reads. At read-committed isolation this is generally allowed because each statement may receive a newer snapshot.
Phantom-read schedule
Initial: 3 orders have total_cents >= 5000T1: BEGINT1: SELECT COUNT(*) WHERE total_cents >= 5000 -- 3T2: BEGINT2: INSERT order with total_cents = 9000T2: COMMITT1: SELECT COUNT(*) WHERE total_cents >= 5000 -- 4T1: COMMITThe original rows may be unchanged. The result set changes because a new row satisfies the predicate. This distinction matters when reserving capacity, enforcing limits, or producing consistent reports.
Prepare a visibility laboratory
DROP TABLE IF EXISTS product_price;DROP TABLE IF EXISTS purchase_order;CREATE TABLE product_price ( sku TEXT PRIMARY KEY, price_cents INTEGER NOT NULL CHECK (price_cents >= 0)) STRICT;CREATE TABLE purchase_order ( order_id INTEGER PRIMARY KEY, total_cents INTEGER NOT NULL CHECK (total_cents >= 0), status TEXT NOT NULL CHECK (status IN ('open','paid','cancelled'))) STRICT;INSERT INTO product_price VALUES ('SQL-TXN', 4500), ('SQL-MVCC', 5200);INSERT INTO purchase_order VALUES (1, 4900, 'paid'), (2, 6500, 'open'), (3, 8200, 'paid'), (4, 3000, 'cancelled');SQLite rollback journal versus WAL
| Mode | Reader view | Writer interaction |
|---|---|---|
| Rollback journal | Readers are kept away while database changes are being copied into the main file for commit. | A writer eventually requires exclusive access; readers can delay the commit phase. |
| WAL | A reader continues from its snapshot while a writer appends newer pages to the WAL. | Readers and one writer can overlap; a reader must end and restart to see a newer snapshot. |
| Both | Uncommitted writes are not normally visible to other connections. | SQLite permits only one writer at a time for a database file. |
PRAGMA journal_mode = WAL;PRAGMA busy_timeout = 5000;BEGIN;SELECT price_centsFROM product_priceWHERE sku = 'SQL-TXN';-- Keep this read transaction open while another connection commits.-- This connection continues to read its established snapshot.COMMIT;Snapshot age and stale decisions
A stable snapshot prevents non-repeatable and phantom reads inside that snapshot, but stability is not the same as freshness. A long-running transaction can make a decision from an old committed state.
Repeatable snapshot
Repeated reads are internally consistent.
Older than current state
Other transactions may have committed newer facts.
Write after stale read
The database may block, reject, or serialize the attempted write.
End and restart
A new transaction obtains a new snapshot.
Validate at write time
Use predicates, versions, uniqueness, or serializable isolation to protect the decision.
Do not confuse read anomalies with lost updates
A lost update occurs when two transactions read the same value and later write derived values so one overwrites the other. It is a write-write correctness problem. Protect it with atomic updates, row locks, version predicates, or serializable execution.
UPDATE accountSET balance_cents = balance_cents + 500, version_no = version_no + 1WHERE account_id = 3;-- Optimistic alternative when the client previously read version 4:UPDATE accountSET balance_cents = 5500, version_no = version_no + 1WHERE account_id = 3 AND version_no = 4;SELECT changes() AS update_won;Checkpoint
Identify the anomaly
- T₁ reads a value written by T₂, then T₂ rolls back. Which anomaly occurred?
- T₁ rereads the same row after T₂ commits an update. Which anomaly occurred?
- T₁ repeats a range query and finds a newly committed row. Which anomaly occurred?
- Does a stable snapshot guarantee that the data is current?
- Why is a lost update not simply a phantom read?
Review the answers
The first is a dirty read, the second a non-repeatable read, and the third a phantom read. A snapshot can be stable yet stale. A lost update concerns competing writes derived from earlier reads; a phantom concerns a changed predicate result set.
Summary and references
- Dirty reads expose uncommitted state.
- Non-repeatable reads change a previously read row.
- Phantoms change the set selected by a predicate.
- Snapshots improve repeatability but can become stale.
- Write correctness still requires guards, locks, atomic statements, or stronger isolation.