Non-Repeatable Read Explained: When the Same Query Returns Different Results

Scenario context

A non-repeatable read happens when the same SELECT returns different results within a single transaction because a concurrent transaction committed an update between the two reads. Read Committed isolation — the default in PostgreSQL, MySQL, and Oracle — does not prevent this. Repeatable Read or Serializable isolation is required to guarantee a consistent view of any row a transaction reads more than once.

Th…

This walkthrough focuses on Two Scenarios That Reveal Whether Your Transaction Is at Risk.

Before making a design choice, clarify:

• These two walkthroughs illustrate the practical difference between transaction patterns that are safe under Read Committed and patterns that require Repeatable Read.
• Understanding which category your workload falls into is the core skill for isolation level decision-making.
• Scenario 1 — Safe under Read Committed: The single-read payment processor.

Concepts in play: database-isolation-levels-anomalies, jvm-internals-and-garbage-collection, consistency-replication

Stage 1 — The Financial Audit That Filed a Contradictory Report

• A scheduled compliance job kicks off at FinTrack, a payments platform processing $4 billion in transactions annually.
• The job's purpose is routine: cross-validate every payment record against the settlement ledger and produce a daily audit report for the financial controller.
• The job opens a database transaction and begins reading payment records in sequence.
• For payment id = 7421 , the first pass returns an amount of $1,200 .

Observable outcome: For payment id = 7421 , the first pass returns an amount of $1,200 .

Stage 2 — How a Non-Repeatable Read Unfolds Step by Step

• The mechanics are precise and reproducible.
• Below is the exact event timeline that produced the FinTrack audit failure.
• The sequence diagram shows two transactions running concurrently under Read Committed isolation.
• T1 is the audit job; T2 is a legitimate payment correction committed by a customer service agent correcting a data entry error.

sequenceDiagram
    participant T1 as Audit Job T1
    participant DB as Payment DB
    participant T2 as Correction T2

    T1->>DB: BEGIN
    T1->>DB: SELECT amount FROM payments WHERE id=7421
    DB-->>T1: 1200

    T2->>DB: BEGIN
    T2->>DB: UPDATE payments SET amount=950 WHERE id=7421
    T2->>DB: COMMIT

    T1->>DB: SELECT amount FROM payments WHERE id=7421
    DB-->>T1: 950
    Note over T1,DB: Same transaction, same row, two different committed values

    T1->>DB: COMMIT

Observable outcome: T1 is the audit job; T2 is a legitimate payment correction committed by a customer service agent correcting a data entry error.

Stage 3 — Deep Dive: How MVCC Snapshot Boundaries Determine Non-Repeatable Read Risk

• The root cause of non-repeatable reads is not a bug in the database engine — it is a deliberate design decision about when the multi-version snapshot boundary is established and for how long it is held.
• Understanding this distinction is the key to choosing the right isolation level and knowing exactly what guarantee you are purchasing.
• The Internals of MVCC Snapshot Mechanics at Read Committed vs.
• Repeatable Read Multi-Version Concurrency Control (MVCC) is the mechanism that PostgreSQL, MySQL InnoDB, and most modern databases use to serve concurrent readers without blocking them with locks.

Read Committed (per-statement snapshot):
  T1 SELECT 1 → snapshot at t=0 → sees amount = 1200
  T2 COMMIT   → new committed version arrives at t=1
  T1 SELECT 2 → snapshot at t=1 → sees amount = 950   <- INCONSISTENCY

Repeatable Read (per-transaction snapshot):
  T1 BEGIN    → snapshot established at t=0
  T1 SELECT 1 → snapshot at t=0 → sees amount = 1200
  T2 COMMIT   → new committed version arrives at t=1 (T1 snapshot is frozen)
  T1 SELECT 2 → snapshot at t=0 → sees amount = 1200  <- CONSISTENT

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: Read X (value=100)
    T2->>DB: Read X (value=100)
    T1->>DB: Write X = 50
    T2->>DB: Write X = 75
    DB->>DB: Conflict!

Observable outcome: Repeatable Read Multi-Version Concurrency Control (MVCC) is the mechanism that PostgreSQL, MySQL InnoDB, and most modern databases use to serve concurrent readers without blocking them with locks.

Stage 4 — Which Isolation Level Prevents Which Anomaly

• The SQL standard defines four isolation levels.
• The table below maps each level to the anomalies it prevents, with database-specific notes where behavior diverges from the standard.
• \ Phantom Read under Repeatable Read: The SQL standard allows phantom reads at Repeatable Read.
• PostgreSQL's implementation prevents them anyway because its true per-transaction MVCC snapshot freezes the entire visible row set, not just individual rows.

Observable outcome: PostgreSQL's implementation prevents them anyway because its true per-transaction MVCC snapshot freezes the entire visible row set, not just individual rows.

Stage 5 — How the Anomaly Propagates Through a Compliance Pipeline

• A non-repeatable read does not stay isolated inside the database connection.
• The diagram below traces how the FinTrack inconsistency propagated from the database read boundary all the way to the regulatory filing — without triggering a single exception or error log at any layer.
• The critical observation from this flow is that the inconsistency is introduced silently at node E — the second read.
• No log line says "warning: value changed." Every system from the cross-validation engine onward treats both values as equally authoritative because they both arrived from the database via the same transaction.

graph TD
    A[Audit service opens transaction under Read Committed] --> B[Read 1 of payment row 7421 returns 1200]
    B --> C[Job processes 50000 other records over 15 minutes]
    C --> D[Concurrent update by T2 commits - row 7421 now 950]
    D --> E[Read 2 of payment row 7421 returns 950]
    E --> F[Cross-validation engine receives 1200 and 950 for same row]
    F --> G[Report generator writes both values to audit document]
    G --> H[Compliance submission filed with contradictory figures]
    H --> I[Regulatory auditor flags discrepancy 3 days later]

Observable outcome: No log line says "warning: value changed." Every system from the cross-validation engine onward treats both values as equally authoritative because they both arrived from the database via the same transaction.

Stage 6 — Database-Specific Behavior: PostgreSQL, MySQL, Oracle, and SQL Server

• Not all databases implement isolation levels identically.
• Engineers migrating between databases or operating multi-database stacks must understand where "Repeatable Read" means the same thing and where it diverges.
• PostgreSQL implements Repeatable Read using a true per-transaction MVCC snapshot.
• The transaction's visibility boundary is established when the first statement executes and remains frozen for the duration.

Observable outcome: The transaction's visibility boundary is established when the first statement executes and remains frozen for the duration.

End-to-end outcome

After working through the stages, you should be able to explain:

• Read Committed does not mean "safe for transactions that read data twice." The name is misleading.
• Read Committed means each statement reads committed data — not that the same data will be committed to the same value across two statements.
• • A non-repeatable read occurs when a transaction reads the same row twice and receives different committed values — because a concurrent transaction updated and committed that row between the two reads.
• • It is categorically different from a dirty read: only fully committed data is involved.

Alternatives considered

Non-Repeatable Read vs. Dirty Read — Why the Distinction Changes Everything

Engineers often conflate two related but distinct transaction anomalies: the dirty read and the non-repeatable read.

Failure and degraded modes

The Real-World Cost: Compliance Failures and Financial Reporting Errors

• Detection: Non-repeatable reads are not theoretical edge cases reserved for adversarial workloads.
• User or operator impact: Non-repeatable reads are not theoretical edge cases reserved for adversarial workloads.