TLDR: 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.

📖 The Financial Audit That Filed a Contradictory Report

It is 3 a.m. on a Monday. 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. The value is logged, annotated, and carried forward into the pre-tax calculation engine. The job continues processing the next 50,000 records. Fifteen minutes later, it loops back to perform a mandatory secondary validation on the same record — a cross-check required by the company's internal compliance policy. This time, the query returns $950.

Same row. Same transaction. Different value. The difference is $250.

The report is already half-assembled. The first pass fed $1,200 into the pre-tax balance. The second pass fed $950 into the net-settlement reconciliation. The resulting document is internally contradictory — two authoritative-looking figures for the same payment, derived from the same transaction, are irreconcilable. The report is auto-submitted to the compliance system at 4 a.m. Three days later, a regulatory auditor flags the discrepancy. FinTrack's engineering team spends a week tracing it to the database layer.

What happened was not a bug in the business logic. It was not an application race condition. It was a database anomaly called a non-repeatable read: a concurrent transaction had updated and committed that payment record between FinTrack's two reads — and the audit transaction, running under the database's default Read Committed isolation level, had no protection against it.

---

🔍 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. The distinction matters because they are prevented by different isolation levels, and assuming one implies the other is a common source of production data corruption.

A dirty read occurs when a transaction reads data that another transaction has modified but not yet committed. If the writing transaction later rolls back, the reading transaction has consumed data that never officially existed. Most production databases prevent dirty reads by default at Read Committed and above — so this anomaly is rarely encountered in well-configured systems today.

A non-repeatable read involves no uncommitted data at all. Every write involved has been fully committed and durable. The problem is that a second committed write occurs between two reads within the same transaction. The reading transaction is doing nothing wrong — it is reading only committed data — yet it sees inconsistent state because the world changed between its two observations.

This table makes the cascade visible. A system that prevents dirty reads — which every modern production database does — still allows non-repeatable reads, phantom reads, and write skew unless a stricter isolation level is explicitly configured. "We use Read Committed" is not a data consistency guarantee for any transaction that reads the same data more than once.

---

⚙️ 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. T2 commits cleanly and correctly — it does nothing wrong. The anomaly arises entirely from timing: T2 commits after T1's first read but before T1's second read of the same row.

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

What the diagram makes unmistakable is that T2 performs a completely valid committed write. There is no error, no rollback, no transaction conflict. T1 performs two syntactically identical reads. Both reads succeed. The database is functioning exactly as designed under Read Committed isolation — each statement in T1 sees the latest committed value at the moment that statement executes. The inconsistency is not a failure of the database engine. It is a consequence of the isolation boundary choice.

The takeaway from this diagram: under Read Committed, the database makes a per-statement promise — "you will read committed data" — but makes no per-transaction promise — "you will read the same committed data twice."

---

🧠 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. When a row is updated, MVCC writes a new version of the row and marks the previous version with an expiry transaction ID. Readers are routed to whichever row version was current at their visibility boundary.

Every row in PostgreSQL carries two hidden system columns: xmin (the transaction ID that created this version) and xmax (the transaction ID that deleted or superseded it, or zero if the version is still current). When a transaction runs a SELECT, PostgreSQL's visibility rules evaluate whether each row version's xmin is committed and visible, and whether its xmax is not yet committed — all relative to the reading transaction's snapshot.

The critical design parameter that determines non-repeatable read behavior is: when is the snapshot boundary established, and for how long does it hold?

Under Read Committed, the snapshot boundary is set at the start of every individual SQL statement and discarded when that statement completes. T1's first SELECT sees all commits that existed at the moment statement 1 began. T1's second SELECT sees all commits that existed at the moment statement 2 began — which is later, and which now includes T2's committed update.

Under Repeatable Read, the snapshot boundary is set once at the start of the transaction and held frozen for the entire transaction's duration. T1's first SELECT and second SELECT both use the same snapshot — the one established when T1 first began. T2's committed update carries an xmin transaction ID newer than T1's snapshot boundary. PostgreSQL's visibility rules exclude it: T1 cannot see any row version with an xmin newer than its snapshot, regardless of whether that version is committed.

The contrast is worth laying out explicitly:

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

MVCC is not the problem here. MVCC is precisely the technology that makes per-transaction snapshots possible without blocking concurrent writers. The question is only whether the database is instructed to give you a per-statement or per-transaction snapshot. The engine is identical; the boundary configuration is what changes.

Performance Analysis: The Cost of Stricter Isolation at Repeatable Read

Repeatable Read does not come free. Understanding the performance profile helps you make the right trade-off rather than applying it to every transaction by default.

Memory pressure from long-lived snapshots: Under Repeatable Read, PostgreSQL must retain all row versions that were visible at the transaction's snapshot boundary for as long as the transaction remains open. If T1 opened a snapshot at 2 a.m. and is still running at 3 a.m., autovacuum cannot reclaim any row version committed after 2 a.m. — even if that version has since been superseded by further updates. On high-write tables such as a payments ledger with thousands of updates per second, a single long Repeatable Read transaction can cause significant table bloat by preventing vacuum from advancing its cleanup horizon.

No serialization overhead compared to Serializable: Unlike Serializable isolation, which uses Serializable Snapshot Isolation (SSI) and tracks read-write dependencies to detect dangerous transaction cycles, Repeatable Read in PostgreSQL adds no locking overhead, no conflict detection, and no transaction retry logic. It is strictly a snapshot boundary configuration. For audit workloads that predominantly read within the transaction, the throughput impact of upgrading from Read Committed to Repeatable Read is typically negligible.

The performance sweet spot for mixed workloads: For databases serving both short OLTP transactions and long audit transactions, configure Read Committed as the session default and declare Repeatable Read explicitly only for transactions that need it. This avoids paying snapshot-retention costs on short, single-read OLTP paths while guaranteeing consistency for the audit workloads that require it.

---

📊 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. MySQL InnoDB uses gap locks on indexed ranges to prevent phantoms at Repeatable Read. Oracle and SQL Server do not provide this guarantee under their Repeatable Read equivalents.

** Write Skew: Write skew occurs when two concurrent transactions each read a consistent view of shared data and independently make decisions that, combined, violate a business invariant. Classic example: two doctors simultaneously agree to go off-call when the rule requires at least one must always be on-call. Each transaction sees one on-call doctor and concludes it is safe to go off-call. Together they break the invariant. Write skew requires Serializable to prevent and is not addressed by Repeatable Read.

Understanding this table prevents two common over-generalizations: "Read Committed is good enough" (it is not, for multi-read transactions) and "Serializable is always required" (Repeatable Read handles non-repeatable reads and phantom reads in PostgreSQL at significantly lower cost).

---

🏗️ 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.

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]

The critical observation from this flow is that the inconsistency is introduced silently at node E — the second read. No exception is thrown. No rollback occurs. 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. The contamination travels downstream and exits the system as a signed compliance document.

This is what makes non-repeatable reads operationally dangerous compared to, say, a deadlock: a deadlock throws an error the application must handle and retry. A non-repeatable read produces a successful transaction whose output is silently incorrect. By the time the corruption is detected — if it is detected at all — it may have propagated into financial reports, regulatory filings, or downstream systems with no obvious trail back to its origin.

---

🌍 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. This snapshot prevents not only non-repeatable reads but also phantom reads — a stronger guarantee than the SQL standard requires at Repeatable Read. PostgreSQL achieves this without additional locking overhead, making Repeatable Read an attractive choice for audit and analytical workloads.

MySQL InnoDB implements Repeatable Read using a combination of MVCC snapshots and gap locks. The MVCC snapshot handles non-repeatable reads on individual rows. Gap locks prevent phantom rows from appearing in indexed range scans within the same transaction. For single-row point lookups, InnoDB's Repeatable Read provides equivalent protection to PostgreSQL's. For complex range queries, gap lock behavior can cause unexpected lock contention under high-write workloads.

Oracle Database defaults to Read Committed and does not offer a direct SQL-standard Repeatable Read isolation level. Oracle's Read Committed provides statement-level read consistency via MVCC, which prevents dirty reads but — as the standard predicts — does not prevent non-repeatable reads across statements. To prevent non-repeatable reads in Oracle, engineers must use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, which carries higher overhead and can produce ORA-08177: cannot serialize access errors under write contention.

SQL Server defaults to Read Committed using lock-based concurrency in its standard configuration, not MVCC. Under lock-based Read Committed, shared locks are acquired and released immediately after each read — making non-repeatable reads possible exactly as the standard defines. SQL Server provides two paths to prevention: REPEATABLE READ isolation (which holds shared locks for the transaction duration) and READ COMMITTED SNAPSHOT ISOLATION (RCSI), which opts the database into MVCC-based Read Committed semantics without requiring transaction isolation level changes. RCSI is a common SQL Server configuration for high-concurrency OLTP workloads.

---

🔭 Detecting Non-Repeatable Reads in a Running Production System

Non-repeatable reads leave no error trail. Each query succeeds individually; no exception is thrown; the transaction commits. Detection requires deliberate instrumentation at multiple levels.

Long-transaction monitoring via pg_stat_activity: The primary risk surface is any transaction that remains open long enough for a concurrent write to commit between two of its reads. Query pg_stat_activity for connections where state = 'idle in transaction' and now() - xact_start > interval '30 seconds'. These are open transactions with a gap between statements — precisely the window in which a non-repeatable read can occur. Alert on these proactively; they indicate either an application bug (forgetting to commit) or a legitimate long-running job that needs a stronger isolation level.

Isolation level configuration audit: Many ORMs, connection pools, and database wrappers do not set an explicit isolation level, deferring entirely to the database default. Search your configuration files, connection pool settings, and data access layer code for SET TRANSACTION ISOLATION LEVEL or equivalent ORM configuration. If no explicit level is set, the transaction behavior is governed by the database default — which is Read Committed in PostgreSQL, MySQL, and Oracle. Treat "no explicit isolation level" as a flag requiring review for any transaction that reads data more than once.

Automated consistency checks in pipeline output: The FinTrack failure was ultimately detected by a human auditor, days after the fact. A more robust approach is to embed automated cross-validation into the pipeline itself: if two reads of the same row or aggregate within a single report produce different values, the job should fail with an explicit error rather than submitting contradictory data downstream. This defensive check should be standard practice for any audit or reconciliation pipeline.

Row version introspection in PostgreSQL: PostgreSQL exposes internal row version metadata via xmin and xmax system columns. If you suspect a non-repeatable read caused a specific data anomaly, you can query the row's version history by examining pg_stat_user_tables and correlating transaction IDs with wall-clock timestamps from pg_stat_bgwriter and pg_stat_activity logs. This post-hoc forensics approach is most useful when reconstructing the timeline of a known incident.

---

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

Non-repeatable reads are not theoretical edge cases reserved for adversarial workloads. They are a latent risk in any system that meets three conditions simultaneously: uses Read Committed isolation (the default in most databases), runs transactions that read the same data more than once, and operates in a domain where data consistency carries regulatory or financial weight.

Regulatory compliance failures are the highest-stakes outcome. Financial institutions operating under SOX, PCI-DSS, Basel III, or banking regulations are required to produce accurate, internally consistent transaction records. A compliance report generated from a non-repeatable read contains factually contradictory data — not because of fraud or hardware failure, but because of a transaction isolation misconfiguration. Regulators may treat data integrity failures as compliance violations regardless of the technical cause.

Financial reporting errors cascade multiplicatively. An audit job that reads a payment amount at two different points in time will produce an incorrect pre-tax balance, an incorrect net-settlement figure, and potentially an incorrect tax liability calculation. Each downstream consumer of the corrupted report amplifies the original discrepancy. Reversing the damage requires identifying every report that consumed the corrupted input, recomputing all affected figures, and re-filing any regulatory submissions — a process that typically takes days and leaves an audit trail of corrections that itself invites scrutiny.

Silent failures outlast loud failures. A deadlock throws an exception that forces the application to retry or surface an error to operations. A non-repeatable read succeeds quietly with wrong data. In a system where correctness is assumed unless an error is thrown, non-repeatable reads accumulate undetected — each one a small, invisible divergence from reality that compounds over time.

---

🧭 When to Reach for Repeatable Read Isolation

Not every transaction benefits from Repeatable Read. The performance cost is real — longer snapshot retention means older row versions must be retained by MVCC's vacuum process, and long transactions can accumulate query planning overhead. The right isolation level depends on the transaction's read pattern and the consequences of inconsistency.

The default Read Committed is the right choice for the majority of short, single-round-trip OLTP transactions. It becomes a liability the moment a transaction enters the pattern: "read value A, do some work, read value A again to verify it is still what we computed." Any transaction containing that pattern needs at least Repeatable Read.

---

🧪 Two Scenarios That Reveal Whether Your Transaction Is at Risk

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. A checkout service opens a transaction, reads the current account balance for user 9933 to verify funds are available, decrements the balance by the purchase amount, and commits. This transaction reads the balance exactly once. Even if a concurrent transaction modifies the balance between the read and the write, PostgreSQL's lock contention (or optimistic concurrency in some frameworks) will surface the conflict at write time. The read pattern is read-once-write-once, and Read Committed is sufficient. Non-repeatable reads cannot occur because the row is never read a second time.

Scenario 2 — Requires Repeatable Read: The cross-validating audit job. A compliance job opens a transaction and reads the recorded amount for payment 7421 as part of a batch pre-tax calculation: $1,200. It processes another 50,000 records. Later, as part of its cross-validation pass, it reads the same payment again to confirm the value has not changed since the first read. Under Read Committed, a concurrent correction committed between the two reads will cause the second read to return $950. The job now holds two different values for the same payment in the same transaction. There is no way for the job to detect this internally — both values arrived from the database without error.

The discriminating question for any transaction is: does the transaction's correctness depend on the same data being consistent across more than one read? If yes, use Repeatable Read. If no — if each piece of data is read exactly once and the transaction makes a single decision based on it — Read Committed is safe and sufficient.

A third practical pattern worth noting: when upgrading an entire connection pool to Repeatable Read is impractical, SELECT ... FOR SHARE on the specific rows that must remain consistent provides targeted per-row repeatable-read semantics without changing the session isolation level. This is useful as a surgical fix in codebases where the isolation level is not easily configurable per transaction.

---

🛠️ PostgreSQL: Configuring Repeatable Read to Prevent the Anomaly

PostgreSQL makes it straightforward to configure Repeatable Read at the transaction, session, or database level. No application-layer changes to business logic are required — only the isolation level declaration must be in place before the transaction's first statement executes.

Set Repeatable Read for a specific transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Both of these reads now use the same per-transaction MVCC snapshot.
-- A concurrent committed UPDATE between them will not change T1's view.
SELECT amount FROM payments WHERE id = 7421;

-- ... 50,000 other records processed ...

SELECT amount FROM payments WHERE id = 7421;
-- Returns the same value as the first read, regardless of concurrent commits.

COMMIT;

Set Repeatable Read as the default for an entire session (connection-pool level):

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

This is the appropriate setting for a dedicated audit connection pool. Every transaction on connections in that pool automatically uses Repeatable Read without requiring per-transaction configuration. The session setting persists until it is explicitly reset or the connection is closed.

Set the database-wide default in postgresql.conf:

# postgresql.conf
default_transaction_isolation = 'repeatable read'

Use this setting only when all workloads on the database benefit from Repeatable Read, or when the database is dedicated to audit and analytical use cases. For mixed OLTP and analytical workloads on the same database, per-session or per-transaction configuration is preferable — the overhead of Repeatable Read on short single-read OLTP transactions is unnecessary.

Apply a row-level share lock when a full isolation upgrade is not an option:

SELECT amount FROM payments WHERE id = 7421 FOR SHARE;

FOR SHARE acquires a shared lock on the selected row and holds it until the transaction commits or rolls back. Any concurrent transaction attempting to update that row will block until T1 completes. This provides repeatable-read semantics for the specific row even under Read Committed session isolation — useful as a targeted, surgical safeguard when upgrading the full transaction isolation level would affect too many concurrent workloads.

Monitor long-running Repeatable Read transactions:

-- Find transactions using Repeatable Read that have been open more than 60 seconds
SELECT pid, usename, application_name, state, isolation_level,
       now() - xact_start AS duration
FROM pg_stat_activity
WHERE isolation_level = 'repeatable read'
  AND state != 'idle'
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '60 seconds';

Long Repeatable Read transactions hold their MVCC snapshot open, which prevents PostgreSQL's autovacuum from reclaiming old row versions. Monitor and alert on transactions exceeding your expected audit job duration to avoid table bloat in high-write tables.

For a full exploration of all PostgreSQL isolation levels and their behavioral trade-offs, see Isolation Levels in Databases.

---

📚 Lessons Learned

1. 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. Any transaction that reads a row more than once is exposed to non-repeatable reads under Read Committed.

2. MVCC is a tool, not a guarantee — the snapshot boundary is what matters. Multi-version concurrency control makes Repeatable Read possible without blocking writers. But whether the snapshot is per-statement or per-transaction is a configuration decision, not an automatic property of MVCC. The same engine that supports both behaviors cannot protect you if it is configured for the weaker one.

3. Silent failures are more dangerous than loud failures. Deadlocks throw exceptions. Constraint violations roll back transactions. Non-repeatable reads succeed silently with inconsistent data. Systems that assume correctness unless an error fires will not detect this anomaly. Build explicit consistency checks into audit pipelines — fail loudly on mismatched cross-validated values.

4. Isolation level configuration lives in deployment, not in code. Most application developers set business logic in code and forget that the transaction isolation level is typically configured in the connection pool, ORM session factory, or postgresql.conf. Audit your entire deployment configuration — not just the application code — for missing isolation level declarations.

5. PostgreSQL's Repeatable Read is stronger than the SQL standard requires. PostgreSQL's per-transaction snapshot prevents phantom reads as well as non-repeatable reads — a correctness bonus over the standard. This is not portable: Oracle, SQL Server, and MySQL InnoDB implement the anomaly-prevention spectrum differently. Do not assume PostgreSQL's Repeatable Read behavior when designing for a multi-database or future-migration scenario.

6. Long-running transactions amplify the risk window. The longer a transaction stays open, the wider the window in which a concurrent write can commit between two reads. Audit jobs, reconciliation pipelines, and analytical transactions that span minutes or hours are significantly more exposed than the typical 10-millisecond OLTP transaction. Treat long transaction duration as a non-repeatable read risk factor, not just a performance concern.

7. Oracle engineers face an extra hurdle — plan for serialization failures. Oracle's lack of a standard Repeatable Read level means preventing non-repeatable reads requires Serializable isolation. Serializable can throw ORA-08177: cannot serialize access under write contention. Oracle-based audit pipelines must implement retry logic for this error class, making prevention more operationally complex than in PostgreSQL.

---

📌 Summary & Key Takeaways: Preventing Non-Repeatable Read Corruption in Production Systems

• 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. The database is functioning correctly; the anomaly arises from the isolation level's snapshot boundary design.
• Read Committed — the production default in PostgreSQL, MySQL, and Oracle — does not prevent non-repeatable reads. Its per-statement snapshots guarantee each read sees committed data, not that two reads in the same transaction see the same committed data.
• Repeatable Read prevents non-repeatable reads by establishing a per-transaction snapshot that does not advance when concurrent writes commit. PostgreSQL's implementation also prevents phantom reads as a bonus.
• Oracle has no equivalent Repeatable Read level; use Serializable and implement serialization failure retry logic.
• SQL Server's default lock-based Read Committed exposes non-repeatable reads; use REPEATABLE READ isolation or enable READ_COMMITTED_SNAPSHOT at the database level.
• Silent corruption is the defining danger. No exception is thrown. The transaction commits successfully. Output is quietly wrong. Downstream consumers treat the contaminated data as authoritative.
• Audit pipelines, compliance jobs, and any transaction that cross-validates values across multiple reads must be run under Repeatable Read minimum.
• Detection strategies: pg_stat_activity long-transaction monitoring, isolation-level configuration audits, and automated cross-validation checks in report generation pipelines.

One-liner to remember: Read Committed gives each statement a fresh snapshot — which is exactly wrong for any transaction that needs to see the same row the same way twice.

---

📝 Practice Quiz

1. What is the defining characteristic of a non-repeatable read?

   • A) A transaction reads uncommitted data from a concurrent transaction that later rolls back
   • B) A transaction reads the same row twice within one transaction and receives different committed values
   • C) A new row appears in a range scan because a concurrent transaction inserted it
   • D) Two transactions simultaneously update the same row and one overwrites the other Correct Answer: B

2. Under which isolation level does a non-repeatable read most commonly occur in production systems?

   • A) Read Uncommitted
   • B) Read Committed
   • C) Repeatable Read
   • D) Serializable Correct Answer: B

3. Under PostgreSQL's MVCC model at Read Committed isolation, when is the visibility snapshot boundary established?

   • A) When the database server process starts
   • B) When the client connection is opened
   • C) At the start of each individual SQL statement
   • D) At the start of the transaction and held until commit Correct Answer: C

4. Which isolation level is the minimum standard SQL level required to prevent a non-repeatable read?

   • A) Read Uncommitted
   • B) Read Committed
   • C) Repeatable Read
   • D) Serializable is the only option that prevents it Correct Answer: C

5. A financial audit job reads the same payment row at the start and end of a 15-minute transaction and gets two different committed amounts. Which of the following best describes what occurred?

   • A) A dirty read — a concurrent transaction wrote an uncommitted value the audit job read
   • B) A phantom read — a new row was inserted into the payments table mid-transaction
   • C) A non-repeatable read — a concurrent transaction committed an update between the two reads
   • D) A write skew — two transactions jointly violated a business invariant by reading consistent state Correct Answer: C

6. Which statement best describes the behavioral difference between PostgreSQL's Repeatable Read and Oracle's isolation model for preventing non-repeatable reads?

   • A) Both use per-transaction snapshots; Oracle and PostgreSQL are equivalent at Repeatable Read
   • B) PostgreSQL's Repeatable Read uses a per-transaction MVCC snapshot; Oracle has no direct Repeatable Read level and requires Serializable
   • C) Oracle's Read Committed uses per-transaction snapshots; PostgreSQL requires Serializable for the same protection
   • D) Both databases require application-level row locking to prevent non-repeatable reads Correct Answer: B

7. Which PostgreSQL command correctly sets Repeatable Read isolation for a single transaction?

   • A) SET ISOLATION LEVEL = 'REPEATABLE READ';
   • B) ALTER SESSION SET ISOLATION = REPEATABLE;
   • C) SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   • D) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Correct Answer: D

8. Why are non-repeatable reads considered more operationally dangerous than deadlocks in audit pipelines?

   • A) Non-repeatable reads cause significantly more performance overhead than deadlocks under the same load
   • B) Deadlocks are silent failures; non-repeatable reads throw exceptions that interrupt the pipeline
   • C) Non-repeatable reads complete successfully with silently incorrect output — no error is raised, so no retry or alert fires
   • D) Deadlocks only affect write transactions; non-repeatable reads affect all read-only queries Correct Answer: C

9. Which pg_stat_activity query condition is the best early indicator that a transaction is at elevated risk of producing a non-repeatable read?

   • A) state = 'active' with query_start in the last 100ms
   • B) state = 'idle in transaction' and now() - xact_start > interval '30 seconds'
   • C) wait_event_type = 'Lock' and wait_event = 'relation'
   • D) backend_type = 'autovacuum worker' with high n_dead_tup Correct Answer: B

10. Open-ended challenge: Your team operates a nightly financial reconciliation job that reads account balance rows at the start and end of a single long-running transaction to verify no values changed, then submits the verified totals to a regulatory reporting API. The job runs under Read Committed isolation on PostgreSQL and has produced a contradictory report once in the past quarter. Describe at least two concrete changes you would make — one at the database configuration level and one at the pipeline design level — to prevent non-repeatable reads in this job. For each change, explain the operational trade-off it introduces and how you would monitor for it in production.

---

🔗 Related Posts

• Key Terms in Distributed Systems
• Isolation Levels in Databases: Read Committed, Repeatable Read, Snapshot, and Serializable Explained
• BASE vs ACID: Choosing the Right Consistency Model
• Data Anomalies in Distributed Systems: Split Brain, Clock Skew, Stale Reads, and More
• Database Anomalies: SQL vs NoSQL Consistency Trade-offs
• Consistency Patterns in Distributed Systems
• ACID Transactions in DynamoDB, CosmosDB, and Spanner