A non-repeatable read is a concurrency anomaly in which a transaction reads the same row twice and gets different values, because another transaction modified or deleted that row and committed between the two reads. In database transactions, it is one of the three read phenomena defined by the SQL standard, alongside the dirty read and the phantom read, and it marks the boundary between the Read Committed and Repeatable Read isolation levels.
The anomaly does not involve uncommitted data: every value the transaction sees was legitimately committed. The problem is inconsistency over time inside a single transaction, which can break calculations, validations and decisions that assume the data stays stable until the transaction ends.
How a non-repeatable read happens
The anomaly requires two transactions interleaving on the same row. Transaction A reads a row, transaction B updates that row and commits, and transaction A reads the row again within its still-open transaction. Under an isolation level that takes a fresh view for each statement, the second read reflects B’s committed change.
The sequence below shows the classic timeline with an account balance, which makes the moment of divergence easy to see.
| Step | Transaction A | Transaction B |
|---|---|---|
| 1 | Begins and reads balance as 500 | Idle |
| 2 | Performs other work | Begins and sets balance to 300 |
| 3 | Still open | Commits |
| 4 | Reads balance again and gets 300 | Finished |
| 5 | Acts on inconsistent values | Finished |
The same effect occurs when transaction B deletes the row instead of updating it: the second read in transaction A then finds nothing. What distinguishes this from a phantom read is the scope: a non-repeatable read concerns rows already read, whereas a phantom read concerns new rows appearing in a repeated range query.
Isolation levels and where the anomaly is prevented
The ANSI SQL-92 standard defines four isolation levels by which read phenomena they must exclude. Non-repeatable reads are allowed at the two weakest levels and forbidden from Repeatable Read upward, as the table summarizes.
| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible in the standard |
| Serializable | Prevented | Prevented | Prevented |
Because Read Committed is the default in PostgreSQL, Oracle Database and SQL Server, applications on these systems are exposed to non-repeatable reads unless they raise the isolation level or lock rows explicitly. MySQL with InnoDB defaults to Repeatable Read, so the same application code can behave differently after a migration between products.
How databases prevent non-repeatable reads
Database engines use two broad mechanisms to stop a row from changing underneath a reader. Lock-based systems keep the reader’s shared locks until commit, so writers must wait. Systems based on MVCC instead give each transaction a consistent snapshot, so the reader keeps seeing the old version while the writer creates a new one.
| Mechanism | How it works | Trade-off |
|---|---|---|
| Shared locks held to commit | Writers wait until the reader finishes | Lower concurrency, deadlock risk |
| Transaction-level snapshot | Reader sees data as of its first query | Old row versions must be retained |
| `SELECT … FOR UPDATE` | Row is locked for the reader’s transaction | Blocks other writers on that row |
| Optimistic version check | Update fails if the version changed | Requires retry logic in the application |
In SQL Server, Repeatable Read uses locks by default, while the optional snapshot isolation uses row versioning in tempdb. In PostgreSQL, Repeatable Read is implemented as snapshot isolation and aborts a transaction with a serialization error if it tries to update a row that another transaction has changed since the snapshot was taken.
Choosing a strategy in application code
Not every non-repeatable read is harmful. A report that reads each row once, or a single-statement update, is unaffected. The anomaly matters when a transaction reads a value, makes a decision based on it and later relies on it again, for example when checking stock before reserving it.
| Situation | Recommended approach | Reason |
|---|---|---|
| Read once, then update the same row | Single atomic UPDATE with a condition | No gap between read and write |
| Several reads must agree | Repeatable Read or snapshot isolation | Stable view for the whole transaction |
| High contention on a few rows | `SELECT … FOR UPDATE` | Serializes access explicitly |
| Long user think time between steps | Optimistic locking with a version column | No locks held during user interaction |
| Complex invariants across many rows | Serializable isolation with retries | Prevents all read anomalies |
Raising the isolation level is not free. Snapshot-based levels retain old row versions longer, and lock-based levels reduce concurrency. In both cases the application must be prepared to retry transactions that fail with serialization or deadlock errors.
Common misconceptions
Several misunderstandings around the term lead to bugs that appear only under load, when concurrent transactions actually overlap. The most frequent ones are listed below.
- “Read Committed means consistent reads”: it guarantees only that each statement sees committed data, not that two statements agree.
- “Repeatable Read prevents everything”: it still permits write skew in snapshot implementations and phantoms in the standard definition.
- “The anomaly needs uncommitted data”: that describes a dirty read; non-repeatable reads involve only committed changes.
- “Testing will reveal it”: single-user tests rarely produce the required interleaving, so the problem surfaces in production.
Understanding it simply
Imagine checking the price of a concert ticket, then going to fetch a wallet, and seeing a different price when coming back to pay. Nobody made a mistake: someone simply changed the price in between.
A non-repeatable read is the database version of that experience. A program looks at the same piece of information twice during one task and gets two different answers, because another user saved a change in the meantime. Stricter database settings can keep the first answer frozen until the task is finished.
Applied in practice
A warehouse system running on PostgreSQL with the default Read Committed level validates an order in one step and reserves stock in the next, inside the same transaction. The first query reads 10 units of an item, the code confirms that the requested 8 units are available and performs other checks for about 200 milliseconds. Meanwhile, a second order commits and reduces the stock to 4.
When the first transaction reads the stock again to write a shipping note, it sees 4 units and records contradictory figures in the same document. The developers fix it by replacing the separate read and update with a single statement, UPDATE stock SET qty = qty - 8 WHERE item_id = 17 AND qty >= 8, and checking the affected row count. Overselling and inconsistent notes stop, without raising the isolation level for the whole system.