CONCEPT Cited by 1 source

Non-repeatable read¶

A non-repeatable read is a read-anomaly class that occurs when a transaction issues the same SELECT twice and gets different values for the same row between the two reads — because another transaction committed a modification to the row between the first read and the second. Unlike dirty reads (which observe uncommitted data), both reads in a non-repeatable-read scenario observe committed data — just committed by different transactions at different points in time.

Canonical worked example¶

From PlanetScale's Brian Morrison II (Source: sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work):

A store owner wants to check inventory. Their transaction runs two identical queries:

START TRANSACTION;

-- Returns 15
SELECT quantity FROM unit_status
  WHERE product_id = 20 AND status = 'available';

-- Now returns 13
SELECT quantity FROM unit_status
  WHERE product_id = 20 AND status = 'available';

COMMIT;

Between the two SELECTs, a concurrent transaction commits a purchase that moves 2 units from available to sold_not_shipped:

START TRANSACTION;
UPDATE unit_status SET quantity = quantity + 2
  WHERE product_id = 20 AND status = 'sold_not_shipped';
UPDATE unit_status SET quantity = quantity - 2
  WHERE product_id = 20 AND status = 'available';
COMMIT;

Under READ COMMITTED, both of the owner's selects return committed data — but the committed state has changed between them. The owner's "inventory report" is now internally inconsistent: the first select saw 15 units, the second saw 13, and there is no single point in time at which the database actually held both values simultaneously.

Which isolation levels permit it¶

Level	Non-repeatable read
`READ UNCOMMITTED`	allowed
`READ COMMITTED`	allowed
`REPEATABLE READ`	prevented
`SERIALIZABLE`	prevented

Non-repeatable reads are the characteristic violation prevented by REPEATABLE READ and above. READ COMMITTED prevents dirty reads but still permits non-repeatable reads — because its snapshot is fresh per-statement, not per-transaction.

Mechanism in InnoDB¶

The critical difference between READ COMMITTED and REPEATABLE READ in InnoDB is the snapshot scope:

READ COMMITTED: "Each select within that transaction will create and use a fresh snapshot (a version of the read rows at that point in time). This means that if a row is modified and has been comitted by another transaction, the next select will create a new snapshot which now represents the latest committed version of that data."
REPEATABLE READ: "queries will use a consistent snapshot of the data, pinning the reads to a single transaction ID throughout the transaction."

The mechanism is MVCC: InnoDB maintains multiple row versions tagged by the transaction ID that wrote them. Under REPEATABLE READ, the reader pins itself to a specific transaction ID at the first read and ignores all newer versions for the lifetime of the transaction — making the read repeatable.

Under READ COMMITTED, the reader looks up the latest committed version at each SELECT, so concurrent commits change what the reader sees.

When non-repeatable reads matter¶

Any analytical workload that runs multiple queries against the same data and expects internal consistency:

Financial reports — running three SELECT SUM(...) over the same table should produce numbers that balance.
Inventory audits — comparing stock-on-hand to pending-orders requires both reads to reflect the same moment in time.
Reconciliation jobs — comparing source and destination tables requires a consistent view.
Multi-table joins done in application code — reading a parent table, then reading child rows, then computing an aggregate; all three phases must see the same committed state.

Any of these run under READ COMMITTED can produce internally-inconsistent results even with no data actually being corrupted at the database level.

Workarounds without switching isolation levels¶

If the application is stuck on READ COMMITTED (e.g., it shares a database pool with workloads where READ COMMITTED's lower lock overhead matters), three mitigations exist:

Combine queries — use a single SELECT with UNION, joins, or subqueries so all data comes from one snapshot atomically.
LOCK TABLES READ (MySQL) — crude but effective; makes the relevant tables immutable while the transaction reads them.
Locking reads — SELECT ... FOR UPDATE acquires an exclusive lock on each row, preventing concurrent modifications until the transaction commits. See concepts/shared-lock-vs-exclusive-lock.

Switching to REPEATABLE READ solves the problem at the transaction boundary instead of per-query.

Dirty read — reads uncommitted data. Non-repeatable reads are both reads of committed data; the problem is the committed state changed in between.
Phantom read — different row counts for the same predicate. Non-repeatable reads are about value changes on the same rows. Phantom reads specifically concern row existence; non-repeatable reads specifically concern row-value stability.
Write skew — two transactions read-then-write disjoint rows based on an invariant that's broken after both commit. Permitted by snapshot isolation; neither classical nor named in the three-violation framing.

Seen in¶

sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work — Brian Morrison II's PlanetScale pedagogical post canonicalises non-repeatable reads as the second of the three violations, with the CD-store inventory audit worked example and the READ COMMITTED vs REPEATABLE READ snapshot-scope contrast. Verbatim framing: "Non-repeatable reads occur when a transaction with multiple select queries reads the same rows within that transaction, but the data within those rows is different between selects since another transaction has modified the data in that time." The key mechanism distinction: READ COMMITTED creates fresh snapshot per SELECT vs REPEATABLE READ pinning to a single transaction ID for the transaction lifetime.