Non-Repeatable Read

## Definition A **non-repeatable read** (or *fuzzy read*) occurs when a transaction reads a row, and a later read of that same row inside the *same* transaction returns different data — because another transaction updated (or deleted) the row and committed in between. The name says what the guarantee is: reading is not *repeatable*. If you ask the same question twice, you should get the same answer for the duration of your transaction; here you do not. ## The interleaving ``` time T1 T2 1 BEGIN 2 SELECT price WHERE id=5 -> 100 3 BEGIN 4 UPDATE products SET price=120 WHERE id=5 5 COMMIT 6 SELECT price WHERE id=5 -> 120 7 COMMIT ``` At step 6 T1 sees 120 where it saw 100 at step 2, and both values were correct committed values at the moment they were read. A deleted row is the same anomaly in its extreme form: the second read returns nothing at all. ## What it is not - **It is not reading uncommitted data.** In the interleaving above, T2 had committed before T1's second read. The anomaly where you read data that was never committed (and may be rolled back) is a different, strictly worse one, prevented at READ COMMITTED and above. A non-repeatable read shows you only real, committed values — just two different ones. - **It is not about rows appearing or disappearing from a range.** Here the *same row* changes value. An anomaly where a repeated range query returns a different *set* of rows because someone inserted or deleted matching rows is a different phenomenon with a different fix. - **It is not a lost write.** T1 in the example wrote nothing. The anomaly is purely about read stability. ## Why it happens by default Most engines default to READ COMMITTED. That level makes exactly one promise about reads: *you will never see uncommitted data*. It makes no promise that a value stays put. Concretely, a multiversion engine takes a **fresh snapshot for every statement** at that level, so each `SELECT` sees whatever was committed at the instant it started; a lock-based engine takes a shared read lock only for the duration of the statement and releases it immediately. Either way, between two statements the row is free to change. This is a deliberate trade. Per-statement freshness means long transactions do not read stale data and readers hold nothing that blocks writers — good for throughput, at the cost of intra-transaction consistency. ## When it actually hurts 1. **Check-then-act across statements.** Read a credit limit, do work, re-read or rely on the earlier value to authorize — the limit moved, and the decision is based on data that no longer holds. 2. **Multi-query reports.** A transaction issues ten `SELECT`s to build one document; each sees a slightly different instant, so the subtotals do not add up to the total. The report is internally inconsistent even though every number in it was true at some moment. 3. **Reads that feed derived writes.** Read a value, compute something from it in the application, and write a derived row elsewhere. The derived row is now consistent with a version of the source that no longer exists. 4. **Iterating a result set while others write.** Long cursors are especially exposed, since the transaction spans a long wall-clock window. When it does *not* hurt: a transaction that reads each row once, or a single-statement query (a single statement is evaluated against one consistent view at any isolation level — the anomaly needs at least two reads). ## How it is prevented The fix is to give the transaction a **stable view of data for its whole lifetime**, which is what REPEATABLE READ and SERIALIZABLE promise. - A **lock-based** engine holds the shared read lock from the first read until commit, so no one can update the row while you might read it again. Readers block writers; the data you see is always the latest committed. - A **multiversion** engine takes **one snapshot for the whole transaction** (at its first statement) and answers every read from it. Nobody is blocked; you may be reading data that is now slightly old, but it is internally consistent. Both prevent the anomaly, with opposite costs — blocking versus staleness. SERIALIZABLE prevents it too, along with everything else. Application-level alternatives exist and often beat raising the isolation level: express the whole read as **one statement** (joins, common table expressions, aggregation) so there is only one read; or, if the transaction will act on the value, take a lock at read time so the value cannot move (`SELECT ... FOR UPDATE`) — which also converts the read into a serialization point. ## The one-line summary READ COMMITTED guarantees *the value you read was committed*; REPEATABLE READ additionally guarantees *the value you read stays the same for you*. A non-repeatable read is the gap between those two sentences.