Non-Repeatable Reads in SQL
Introduction
A non-repeatable read occurs when one transaction reads the same row more than once and receives different committed values because another transaction updated or deleted that row between the reads. The first result was valid when observed, but it did not remain stable for the transaction.
This anomaly is common under statement-level Read Committed isolation and is distinct from a dirty read, which exposes uncommitted data. This tutorial builds controlled two-session examples, explains isolation and MVCC behavior, compares database products, and develops locking, optimistic concurrency, testing, and application strategies.
What Is a Non-Repeatable Read?
Suppose Transaction A reads an employee salary as 50,000. While A remains active, Transaction B changes the same salary to 55,000 and commits. When A reads that employee again, it sees 55,000. The row identity is the same, but the committed value has changed within A's transaction.
Both results can be individually valid. The anomaly arises because A assumed repeatability across statements without using a transaction guarantee that provides it.
Why Non-Repeatable Reads Occur
At Read Committed, many databases give each statement a fresh view of committed data. Transaction B's update is invisible to A's first query because it has not happened yet, but visible to A's second query after B commits.
This behavior improves concurrency and lets queries see recent commits. It becomes a correctness issue only when application logic assumes earlier values stay unchanged.
How the Anomaly Happens
- Transaction A begins and reads one row.
- Transaction A remains open without locking that row for stability.
- Transaction B updates or deletes the row.
- Transaction B commits successfully.
- Transaction A reads the same row again.
- Transaction A receives a different value or no row.
The intervening change is committed, unlike a dirty read. Rolling back Transaction A does not undo Transaction B's independent commit.
Non-Repeatable Read Timeline
| Time | Transaction A | Transaction B |
|---|---|---|
| T1 | Begins | Not started |
| T2 | Reads salary 50,000 | Not started |
| T3 | Remains open | Updates salary to 55,000 |
| T4 | Remains open | Commits |
| T5 | Reads salary 55,000 | Complete |
SQL Setup
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
salary DECIMAL(12, 2) NOT NULL
);
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (42, 'Ravi Kumar', 50000.00);
Run the setup once and commit it. Open two independent database connections for the demonstration.
Two-Session Example
-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT salary
FROM employees
WHERE employee_id = 42;
-- Result: 50000.00
-- Session B
BEGIN;
UPDATE employees
SET salary = 55000.00
WHERE employee_id = 42;
COMMIT;
-- Session A
SELECT salary
FROM employees
WHERE employee_id = 42;
-- Possible result: 55000.00
COMMIT;
Session A's exact result depends on the database and configured mode. Under common Read Committed semantics, the second read sees B's commit.
Making the Test Deterministic
Pause Session A after its first query, run Session B through commit, and only then execute A's second query. Automated tests should coordinate the sessions with barriers or latches rather than unreliable sleep durations.
Confirm that both commands in A use the same connection and transaction. If autocommit ends the first query's transaction, the test demonstrates two transactions rather than a non-repeatable read inside one transaction.
Which Isolation Levels Allow It?
| Level | Non-repeatable read | General behavior |
|---|---|---|
| Read Uncommitted | Possible | May also expose uncommitted data |
| Read Committed | Commonly possible | Each statement may see newer commits |
| Repeatable Read | Prevented for qualifying reads | Uses stable snapshot or retained locks |
| Serializable | Prevented | May block or abort unsafe schedules |
Product behavior remains authoritative. Isolation names alone do not describe every snapshot, locking, and current-read detail.
Read Uncommitted
Read Uncommitted can allow both dirty and non-repeatable reads. A transaction might see an uncommitted intermediate value first and a different committed value later, so neither validity nor stability is guaranteed.
Some databases implement stronger behavior than the name suggests. PostgreSQL maps Read Uncommitted to Read Committed and therefore prevents dirty reads while still allowing statement-to-statement changes.
Read Committed
Read Committed prevents ordinary dirty reads but usually does not promise that one row remains unchanged across statements. Each statement can observe data committed before that statement or snapshot begins.
This level is appropriate when each query can stand alone. Multi-step calculations or decisions requiring one stable view need an explicit lock, transaction snapshot, stronger isolation, or a single atomic SQL statement.
Repeatable Read
Repeatable Read prevents the standard non-repeatable-read anomaly. Lock-based implementations can retain shared locks on read rows until transaction end, blocking writers. MVCC implementations can keep a transaction snapshot and let writers proceed while the reader continues seeing older versions.
Phantom and write-skew behavior still varies. Stability of one row does not automatically imply full serializability.
Serializable
Serializable ensures that committed results are equivalent to a valid serial order. It prevents non-repeatable reads along with standard dirty and phantom anomalies. The database may use locks, predicate protection, or dependency validation.
Concurrent transactions may block, deadlock, or receive serialization failures. Applications must retry the complete transaction safely with bounded backoff and idempotency.
Read Committed vs Repeatable Read
| Aspect | Read Committed | Repeatable Read |
|---|---|---|
| Committed visibility | Fresh per statement in many engines | Stable row or transaction view |
| Non-repeatable reads | Usually allowed | Prevented |
| Concurrency cost | Often lower | More locks, versions, or conflicts |
| Best use | Independent short statements | Multi-query stable reads |
PostgreSQL Behavior
PostgreSQL Read Committed gives each command a new snapshot. A second select can therefore see another transaction's newly committed update. Repeatable Read uses one transaction snapshot and returns the earlier visible row version.
PostgreSQL Repeatable Read also prevents phantoms within its snapshot, but it is not equivalent to Serializable Snapshot Isolation. Serializable can reject transactions whose dependency pattern is unsafe.
MySQL InnoDB Behavior
InnoDB commonly defaults to Repeatable Read. Consistent nonlocking reads in one transaction use a snapshot established by the first such read, so repeated reads return the same visible version. At Read Committed, each consistent read gets a fresh snapshot.
Locking reads such as SELECT ... FOR UPDATE and modifying statements use current-read and lock semantics rather than behaving like an old snapshot. Mixing consistent and locking reads requires careful interpretation.
SQL Server Behavior
SQL Server's common lock-based Read Committed mode releases shared locks after the statement, allowing a committed update before the next read. Repeatable Read retains qualifying shared locks until transaction end and can block the writer.
With READ_COMMITTED_SNAPSHOT, Read Committed uses statement-level row versions and still permits different committed values across statements. Explicit Snapshot isolation can provide a transaction-level versioned view when enabled.
Oracle Behavior
Oracle Read Committed provides statement-level read consistency. Each query sees committed data as of its own start, so a later query can see a newer committed value. Oracle Serializable and Read Only transaction modes provide a transaction-level view.
Serializable updates can fail if relevant rows changed after the transaction began. Applications should classify and retry the documented error when appropriate.
SQLite Behavior
An explicit SQLite read transaction sees a snapshot of the database. In WAL mode, another connection can commit changes while the reader continues seeing its earlier snapshot. In rollback-journal mode, locking interactions may instead delay the writer.
Two autocommit selects are separate transactions and can naturally see different committed values. That is not a non-repeatable read inside one transaction, even though application output changes.
Database Comparison
| Database | Typical Read Committed behavior | Stable transaction option |
|---|---|---|
| PostgreSQL | Statement snapshots | Repeatable Read or Serializable |
| MySQL InnoDB | Fresh consistent-read snapshots | Repeatable Read snapshot |
| SQL Server | Statement locks or versions by configuration | Repeatable Read, Snapshot, or Serializable |
| Oracle | Statement-level read consistency | Serializable or Read Only |
| SQLite | Explicit read transaction snapshot | Transaction mode and journaling dependent |
Non-Repeatable Read vs Dirty Read
A dirty read observes another transaction's uncommitted value, which may disappear on rollback. A non-repeatable read observes different committed values at different times. Read Committed prevents the first but commonly allows the second.
Dirty reads concern validity; non-repeatable reads concern stability. Correct diagnosis determines the appropriate isolation fix.
Non-Repeatable Read vs Phantom Read
A non-repeatable read changes the value or existence of a row already read. A phantom changes the set returned by a predicate because another transaction inserts, deletes, or modifies matching rows.
One update can produce both effects depending on the queries. Product-specific Repeatable Read behavior toward phantoms varies.
Non-Repeatable Read vs Lost Update
A non-repeatable read is an observation anomaly. A lost update is a write anomaly where one transaction's effect is overwritten or omitted. Seeing a changed value can alert the application, but it does not itself prevent lost updates.
Use atomic updates, pessimistic row locks, optimistic version predicates, or isolation that detects write conflicts.
MVCC and Stable Versions
MVCC keeps multiple row versions and uses transaction metadata to determine which version a reader can see. At statement-level Read Committed, each statement can choose a newer committed version. At transaction-snapshot isolation, the same older version remains visible.
Long snapshots can retain old versions and delay cleanup. Stable reads therefore have storage and operational costs even when they do not block writers.
Lock-Based Prevention
A lock-based Repeatable Read implementation can hold shared locks on rows until transaction end. A writer requesting an incompatible lock waits, preventing the row from changing before the reader completes.
Locks provide direct coordination but can increase waits and deadlocks. Keep transactions short, use selective indexes, and acquire resources in a consistent order.
SELECT FOR UPDATE
BEGIN;
SELECT salary
FROM employees
WHERE employee_id = 42
FOR UPDATE;
-- Validate and perform the related update.
COMMIT;
This pessimistic pattern locks the selected row for an intended write in databases that support it. It prevents a competing writer from changing the row before the transaction completes.
It is not ideal for read-only reporting and does not automatically protect rows that do not yet exist. Predicate rules may require range or serializable protection.
Optimistic Locking
UPDATE employees
SET salary = 55000.00,
version = version + 1
WHERE employee_id = 42
AND version = 7;
If another transaction changed the row, zero rows are affected. The application reloads and rejects, merges, or retries the operation. Optimistic locking detects stale writes without requiring the original read to remain visually unchanged.
Single-Statement Alternatives
Many multi-step read-then-write sequences can become one conditional statement. An inventory decrement can include quantity >= requested in its predicate, and the application can check whether one row changed.
A single atomic statement reduces the interval between validation and write. It often provides simpler correctness than holding a broad transaction snapshot.
Banking Example
A transaction reads a balance to calculate eligibility, while another transaction posts a deposit or withdrawal. A second read changes the result, making the first decision inconsistent with the later state.
Use atomic balance updates, ledger entries, row locks, or serializable rules according to the invariant. Never assume a prior balance remains current without protection.
E-Commerce Pricing
A checkout transaction can read a product price, calculate tax, and later reread after another transaction changes the price. The customer-facing total can shift midway or disagree with stored line items.
Capture the accepted price in the order line under one transaction, use a version check, or define an explicit quote with an expiration. Product catalog prices and committed order prices serve different purposes.
Inventory and Ticket Booking
An availability value can change after the first read. A second read may show fewer items or a booked seat because another transaction committed. Stable reads alone do not reserve the resource.
Use conditional writes and uniqueness constraints for the final decision. A snapshot can keep a report stable while still being too old for allocation.
Payroll and Reporting
A payroll process reading salary and deductions in separate queries can combine values from different committed moments. A financial report can show totals that do not match details if each statement sees a new snapshot.
Use one transaction-level snapshot, an exported reporting snapshot, a replica with defined consistency, or a warehouse load representing one cut-off time.
Row Deletion Between Reads
The anomaly is not limited to updated values. Transaction A can read a row, Transaction B can delete and commit it, and A's second query can return no row. Application code must not assume that an object loaded earlier still exists.
A foreign-key relationship, row lock, optimistic delete version, or stable snapshot may be required depending on whether A only reports the row or intends to modify it.
Application Caching
A first-level ORM cache may return the same in-memory entity on the second lookup, hiding a database-level non-repeatable read. A shared cache can also serve an older value even after the database would return a newer commit.
Separate cache consistency from transaction isolation. Clear persistence contexts during database behavior tests and use explicit cache invalidation or versioning in production.
Stable Does Not Mean Current
Repeatable Read can keep returning the version that was valid when the transaction snapshot began, even after another transaction commits a newer value. That stability is useful for reports and multi-step reasoning, but it does not prove the value is still current at commit time.
A workflow that intends to update the row must still handle conflicts. It may lock the row, include the original version in the update predicate, or rely on a database rule that rejects incompatible writes. Snapshot stability and write authority are separate guarantees.
Cross-Table Read Skew
Related values can live in different tables. A transaction might read an order header before another transaction commits a cancellation, then read line items after that cancellation changes related rows. Each query returns committed data, but the combined application view can represent no single moment.
A transaction-level snapshot aligns these reads to one point in time. Alternatively, one joined query may reduce the gap, though its statement snapshot still describes only that query. Reporting requirements should state whether all sections need the same cut-off.
The ABA Change Problem
A value can change from A to B and back to A between two reads. Comparing only the final business value makes it appear unchanged even though another transaction modified it. This matters when the intermediate change had side effects or when the row was deleted and recreated.
A monotonically increasing version column, immutable event sequence, or updated timestamp can reveal the intervening change. Optimistic locking should compare a dedicated version rather than only the visible field being edited.
Pagination and Repeated Reads
Offset pagination issues multiple queries that can each see a newer committed state. Rows inserted, deleted, or reordered between pages may be duplicated or skipped. This is related to changing predicate results and is broader than one-row non-repeatability.
Keyset pagination provides more stable traversal for changing datasets, while a transaction snapshot can freeze the view when an export requires one consistent set. Long snapshots have operational costs, so large exports may be better served by a reporting copy or materialized dataset.
APIs, ETags, and Conditional Updates
An API client may fetch a resource, edit it for several minutes, and submit an update after someone else has changed the row. A database transaction cannot remain open across that user interaction. Instead, expose an ETag or version and require the client to send it with the update.
The server performs a conditional write and returns a conflict when the version no longer matches. This extends optimistic concurrency beyond one short database transaction and prevents silent overwrites from stale API representations.
Batch Processing
A batch job that repeatedly queries and updates records can observe changing values as online traffic commits. One giant Repeatable Read transaction may provide consistency but retain versions, increase rollback cost, and process a snapshot that becomes increasingly old.
Design resumable batches with explicit cut-off keys, status transitions, version checks, and small commits. Record progress durably so retries do not duplicate effects. The right design often combines controlled freshness with idempotency rather than demanding one enormous snapshot.
ORM Identity Maps
Many ORMs return the same managed object when a row is requested twice in one persistence context. The second application lookup may therefore show the old in-memory value even though a direct SQL query at Read Committed would see a newer commit. Refreshing or clearing the context changes that behavior.
This application-level repeatability should not be mistaken for database isolation. A later flush can still encounter an optimistic conflict or overwrite data if version checks are missing. Test both ORM behavior and generated SQL.
Migrating to Stronger Guarantees
Before changing isolation globally, inventory workflows that require stable reads and reproduce their anomalies. Strengthen those transaction boundaries first, add retry handling, and measure lock waits, deadlocks, serialization failures, and version-store growth under realistic load.
A transaction-specific setting is often safer than changing the database default. Where stronger isolation creates too much contention, redesign the operation with atomic SQL, a version predicate, a queued workflow, or a reporting snapshot. Migration should improve correctness without creating an unobserved availability problem.
Autocommit
With autocommit enabled, each select usually runs in its own transaction. Two queries can return different committed values, but technically there is no single transaction whose read failed to repeat.
If business logic requires one coherent multi-query view, disable autocommit through a managed transaction and choose the appropriate snapshot or lock behavior.
Transaction Duration
Long transactions increase the time during which concurrent changes can occur. At Read Committed this means more opportunities for changing values; under MVCC snapshots it means longer retention of old versions; under lock-based Repeatable Read it means longer blocking.
Keep the boundary only as long as correctness requires. Move user input, remote calls, and expensive non-database work outside it.
Non-Repeatable Reads in JDBC
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
connection.setTransactionIsolation(
Connection.TRANSACTION_REPEATABLE_READ
);
runConsistentBusinessReads(connection);
connection.commit();
}
Confirm driver and database support. Both reads must use the same connection. The pool should reset isolation before reuse so other requests receive their expected setting.
Connection Pools and Frameworks
Framework transaction annotations can set isolation, but internal method calls, asynchronous work, and propagation settings may create different boundaries than expected. A second repository call might use another connection if the transaction context is lost.
Test the complete service method and inspect actual database sessions. Always close or roll back before returning a connection to the pool.
Choosing a Prevention Strategy
Use a transaction snapshot for coherent multi-query reads, pessimistic locking when an imminent write must reserve a row, optimistic locking when conflicts are uncommon, and atomic SQL for straightforward state changes. Serializable isolation is appropriate when invariants span predicates or several rows.
Do not strengthen every transaction by default. Match the mechanism to the business rule and measure contention, aborts, version retention, and latency.
How to Test Non-Repeatable Reads
Use two independent connections and controlled synchronization. Session A starts, reads, and pauses. Session B updates the same row and commits. Session A reads again. Record the value, blocking behavior, and any database error.
Repeat at Read Committed, Repeatable Read, Snapshot where available, and Serializable. Use the same database product and configuration as production.
Test Cases
- Demonstrate a changing committed value at Read Committed.
- Confirm a stable value at Repeatable Read.
- Repeat with a committed row deletion.
- Observe whether the writer blocks under lock-based Repeatable Read.
- Observe old-version retention under snapshot behavior.
- Test a locking read followed by a write.
- Verify an optimistic version conflict affects zero rows.
- Confirm connection pools reset isolation state.
- Compare two autocommit queries with one explicit transaction.
- Test retry behavior after a serialization failure.
Monitoring and Diagnosis
Monitor transaction isolation, age, lock waits, deadlocks, serialization failures, optimistic conflicts, and version-store growth. Trace repeated reads and writes with a request identifier without logging sensitive values.
A changing value is not automatically a database defect. Diagnosis begins by confirming the transaction boundary and promised isolation, then deciding whether the application assumption was valid.
Common Mistakes
- Confusing committed changes with dirty reads.
- Assuming Read Committed gives one transaction snapshot.
- Believing Repeatable Read is identical across databases.
- Using a stable snapshot as if it reserved inventory.
- Ignoring row deletion as a non-repeatable result.
- Testing with autocommit or one connection accidentally.
- Mixing InnoDB consistent reads and current locking reads without understanding them.
- Changing the global isolation level for one workflow.
- Forgetting ORM and shared-cache effects.
- Retrying only one statement after a transaction-wide conflict.
Prevention Best Practices
- Define whether a workflow requires stable values or fresh values.
- Use explicit transactions for multi-query assumptions.
- Select isolation from concrete anomalies and vendor behavior.
- Prefer atomic conditional statements where possible.
- Use row locks only when an imminent write needs reservation.
- Use version columns to detect stale writes.
- Keep transactions short and indexes selective.
- Handle deadlocks and serialization failures with bounded retries.
- Reset connection state and test framework boundaries.
- Run deterministic two-session integration tests.
Interview Questions and Answers
What is a non-repeatable read?
It occurs when one transaction reads the same row twice and gets different committed values because another transaction changed it between reads.
Which level commonly permits it?
Read Committed commonly permits non-repeatable reads because each statement may see a newer committed view.
How is it different from a dirty read?
A dirty read sees uncommitted data; a non-repeatable read sees different committed data.
Does Repeatable Read prevent phantoms?
That depends on the database. Standard and product-specific behavior differ.
Does a snapshot reserve the row?
No. It stabilizes visibility but does not necessarily prevent another transaction from committing a change.
Practical Exercises
- Create the employee table and reproduce the two-session timeline.
- Repeat at Read Committed and Repeatable Read.
- Replace the update with a delete and compare results.
- Use
SELECT FOR UPDATEand observe writer blocking. - Add a version column and implement optimistic conflict detection.
- Convert a read-then-write inventory flow into one conditional update.
- Compare InnoDB consistent and locking reads.
- Test SQL Server Read Committed with and without row versioning.
- Verify SQLite behavior in WAL and rollback-journal modes.
- Document the stable-read requirement of one real workflow.
Quick Reference
| Concept | Meaning |
|---|---|
| Non-repeatable read | Same row returns different committed values |
| Read Committed | Often gives a fresh committed view per statement |
| Repeatable Read | Stabilizes qualifying reads by locks or versions |
| Snapshot | Provides a versioned point-in-time view |
FOR UPDATE | Pessimistically reserves selected rows |
| Version column | Detects a stale optimistic write |
| Current read | Reads latest lockable data for modification |
| Serializable | Produces an outcome equivalent to serial execution |
Review Workflow
- Identify values read more than once in one business operation.
- Confirm whether all reads share one connection and transaction.
- Determine whether values must be stable, fresh, or reserved.
- Reproduce the competing committed update with two sessions.
- Document the database's actual isolation behavior.
- Choose a snapshot, lock, version check, or atomic statement.
- Handle blocking, deadlocks, and retryable conflicts.
- Keep the final transaction appropriately short.
- Verify pool and framework transaction settings.
- Add integration tests and production metrics.
- Measure performance under realistic contention.
- Revisit the choice when workflow assumptions change.
Key Takeaway
A non-repeatable read is a change in committed visibility within one transaction. It is not invalid data in the way a dirty read is; it is a stability problem that matters when application logic assumes the first value remains current.
Prevent it only where the business needs stable reads. Use transaction snapshots for coherent reporting, locks for imminent protected writes, version columns for optimistic conflict detection, and atomic SQL for simple state changes. Combined with vendor-aware isolation, short transactions, correct indexing, retries, and deterministic concurrency tests, these techniques protect correctness without imposing unnecessary contention.