our nightly report shows inconsistent totals — phantom reads and choosing the right isolation

Question

Accepted Answer

106
   Your nightly report shows inconsistent totals — phantom reads & the right isolation level
  Level: Very Hard — A real reporting bug where numbers "don't add up" because data changes mid-report.

  
     Scenario: A financial report runs two queries in one transaction: (1) SUM of all orders, and (2) COUNT of all orders. But the report shows a total that doesn't match the count × average — because new orders were inserted between the two queries. The books don't balance. How do you guarantee a consistent snapshot?
  

   Sample Data — orders (at report start)
  
    
      
        order_idamount
      
      
        1100
        2200
        3300
      
    
  

   The Phantom Read in Action
  
    
      
        TimeReport TransactionAnother UserRows seen
      
      
        t1Query 1: SUM(amount) = 6003 rows
        t2INSERT order (id 4, 400); COMMIT
        t3Query 2: COUNT(*) = 44 rows!
      
    
  
  SUM says 600 (3 orders) but COUNT says 4 — a "phantom" row appeared mid-transaction. The report is internally inconsistent.

  The root cause: Under READ COMMITTED, each query sees the latest committed data — so the second query sees the newly inserted "phantom" row that the first didn't. The two queries ran against different states of the table.

  
     The fix — a consistent snapshot: Run the whole report under REPEATABLE READ (InnoDB's default). It takes a snapshot at the first read, and every subsequent query in that transaction sees the same frozen state — new inserts are invisible until the transaction ends. Both queries then agree.
  

   The Fix — Snapshot Isolation for the Report
-- Ensure the whole report sees ONE consistent point-in-time snapshot
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;   -- snapshot established at first read (MVCC)

-- Query 1: sum
SELECT SUM(amount) AS total_revenue FROM orders;   -- 600

-- ... even if others INSERT/COMMIT new orders here ...

-- Query 2: count -> still sees the SAME 3 rows as Query 1
SELECT COUNT(*) AS total_orders FROM orders;        -- 3 (consistent!)

COMMIT;   -- snapshot released

   Even Stronger — Explicit Consistent Snapshot
-- Forces the snapshot to be taken immediately at BEGIN,
-- not lazily at the first SELECT
START TRANSACTION WITH CONSISTENT SNAPSHOT;

SELECT SUM(amount) FROM orders;
SELECT COUNT(*)    FROM orders;   -- guaranteed same snapshot

COMMIT;

   Isolation Level for Reports — Trade-offs
  
    
      
        LevelReport consistencyConcurrency impact
      
      
        READ COMMITTEDPhantoms possibleHigh (good)
        REPEATABLE READConsistent snapshotHigh (MVCC, no read locks)
        SERIALIZABLEFully consistentLow (locks reads)
      
    
  

  
     Why REPEATABLE READ is the sweet spot: Thanks to MVCC, it gives a consistent snapshot without taking read locks — so your long report doesn't block writers, and writers don't block your report. SERIALIZABLE would also work but adds locking that hurts concurrency.
  

  
     Interviewer follow-up: "The report is read-only and runs for 30 minutes — any concern with the long snapshot?" → Yes: a long-running REPEATABLE READ transaction forces InnoDB to retain old row versions in the undo log (to serve the snapshot), which can bloat storage and slow purge. Best practice: run heavy reports on a read replica so they don't hold long snapshots on the primary.

our nightly report shows inconsistent totals — phantom reads and choosing the right isolation

Answer with Explanation

Your nightly report shows inconsistent totals — phantom reads & the right isolation level

Sample Data — `orders` (at report start)

The Phantom Read in Action

The Fix — Snapshot Isolation for the Report

Even Stronger — Explicit Consistent Snapshot

Isolation Level for Reports — Trade-offs

Time	Report Transaction	Another User	Rows seen
t1	Query 1: SUM(amount) = 600		3 rows
t2		INSERT order (id 4, 400); COMMIT
t3	Query 2: COUNT(*) = 4		4 rows!

Level	Report consistency	Concurrency impact
READ COMMITTED	Phantoms possible	High (good)
REPEATABLE READ	Consistent snapshot	High (MVCC, no read locks)
SERIALIZABLE	Fully consistent	Low (locks reads)

order_id	amount
1	100
2	200
3	300