Non-repeatable read
A non-repeatable read is reading the same row twice inside one transaction and getting different data, because another transaction committed a change in between. Nothing you read was uncommitted (every value was real), yet your transaction can no longer trust its own earlier reads.
Formally Adya's P2. It is the flagship anomaly of READ COMMITTED, where every statement gets a fresh snapshot: each statement is internally consistent, but two statements may disagree with each other.
Read skew
Non-repeatable reads have a nastier multi-row cousin, read skew (formally Adya's G-single): read row 1, let someone move money to row 2, read row 2. Every row you saw was committed and correct, yet the combination existed at no point in time:
A backup taken this way is corrupt; a report computed this way is wrong, even though no single read misbehaved.
Who prevents it
| Level | SQL standard | PostgreSQL | MySQL (InnoDB) |
|---|---|---|---|
| READ COMMITTED | permitted | happens (proof) | happens (proof) |
| REPEATABLE READ | prevented | prevented (proof) | prevented for plain SELECTs (proof); UPDATE/DELETE are current reads that bypass the snapshot |
| SERIALIZABLE | prevented | prevented | prevented |
The cure is a per-transaction snapshot: at REPEATABLE READ, both engines give your SELECTs one frozen view for the whole transaction. MySQL's asterisk matters, though: its writes and locking reads see the current data regardless of the snapshot, which is exactly how lost updates survive there.
Related anomalies
- Phantom read, the same effect on a set of rows: the rows you saw don't change, but new ones appear.
- Lost update: what happens when you write back through a stale read instead of merely looking at it.
See it happen
- PostgreSQL: Read Committed, non-repeatable read, phantom, and read skew in one lesson
- MySQL: Read Committed (the same anomalies on InnoDB)