Case Study 2 — The Report That Didn't Add Up

Isolation isn't pedantry — the wrong level produces internally inconsistent results. A financial report computed several figures in one transaction under the default isolation level, and concurrent writes between the reads made the figures disagree with each other. Raising the isolation level fixed it.

Background

A finance dashboard ran an end-of-day report inside a single transaction. It computed several related figures with separate queries: total revenue, total refunds, and net revenue (revenue − refunds), plus a count of transactions. Under PostgreSQL's default Read Committed isolation, each statement sees a fresh snapshot of data committed before that statement began.

The report ran while the system was still live (transactions committing constantly). The figures came back internally inconsistent: net revenue didn't equal revenue minus refunds; the transaction count didn't match the rows the totals were computed from. The numbers were each individually "correct" for the moment they ran — but they were computed against different snapshots, so they didn't agree with each other. Executives lost trust in the dashboard.

What went wrong: non-repeatable reads across a transaction

Under Read Committed, every statement gets its own snapshot. So within the report's transaction:

   T (report), Read Committed:
     SELECT SUM(amount) ...     → snapshot at 17:00:00 → revenue = $1,000,000
        [meanwhile other transactions commit new sales and refunds]
     SELECT SUM(refund) ...     → snapshot at 17:00:03 → refunds = $60,000  (includes sales the revenue query didn't)
     SELECT net ...             → snapshot at 17:00:05 → yet another moment
     SELECT count(*) ...        → snapshot at 17:00:07 → yet another

Each query saw a different, later state of the database, because new sales and refunds committed between them. This is the non-repeatable read phenomenon (Chapter 26): reading data multiple times in one transaction and getting different underlying values. The report's figures were snapshots of a moving system at different instants, so they couldn't be reconciled with each other. Nothing was "wrong" with any single query — the problem was that they didn't share a consistent view.

The fix: a consistent snapshot for the whole transaction

The report needs all its queries to see the same state of the database — one consistent point in time. That's exactly what Repeatable Read provides in PostgreSQL: the entire transaction sees a single snapshot taken at its start.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(amount)  FROM ...;   -- all four queries see the SAME snapshot
SELECT SUM(refund)  FROM ...;
SELECT ...net...;
SELECT count(*)     FROM ...;
COMMIT;

Now every query in the report reads from the snapshot as of the transaction's start, so the figures are mutually consistent: net = revenue − refunds, and the count matches the totals — because they're all describing the same instant. The report became trustworthy again. (Crucially, Repeatable Read didn't block the live traffic — thanks to MVCC, the report reads its frozen snapshot while sales keep committing on newer versions.)

The analysis

  1. Isolation level is a correctness decision, not a detail. The default (Read Committed) is right for most individual operations, but a multi-query report that must be internally consistent needs a transaction-wide snapshot — Repeatable Read. Choosing the level is part of getting the right answer.

  2. Read Committed gives each statement its own snapshot. That's fine when statements are independent, but for several reads that must agree with each other, it allows non-repeatable reads: the world moves between your queries. The figures describe different moments.

  3. Repeatable Read = one snapshot for the whole transaction. It's the standard tool for "give me a consistent point-in-time view across multiple queries" — reports, exports, reconciliations, multi-step calculations.

  4. MVCC makes this cheap. Repeatable Read doesn't lock the live tables; the report reads its frozen snapshot while writers continue on newer row versions. Strong consistency and high concurrency — the MVCC payoff (Chapter 26).

  5. "Each query was correct" can still be wrong. Correctness of a report is about the figures being consistent with each other, which requires a shared snapshot. Per-statement correctness isn't enough when the outputs must reconcile.

Discussion questions

  1. Under Read Committed, why did the report's figures fail to reconcile?
  2. Which isolation phenomenon is this, and what exactly does it allow?
  3. How does Repeatable Read make the figures mutually consistent?
  4. Why doesn't Repeatable Read freeze out the live traffic (what's MVCC doing)?
  5. ⭐ Would Serializable have been necessary here? When would a report need Serializable rather than Repeatable Read?