A nightly report runs a dozen separate SELECT statements inside one transaction at READ COMMITTED, and its section subtotals do not add up to its grand total. What is the likely cause, and what are your options for fixing it?

## Diagnosing it The symptom — parts that do not sum to the whole, or a detail listing that disagrees with a summary line — is the classic signature of statements reading different instants. Before changing anything, confirm the diagnosis: - **Does the discrepancy scale with concurrent write volume?** If the report is clean at 03:00 and wrong at 20:00, that is the tell. - **Is it reproducible on a quiescent copy of the data?** If the same input data always yields consistent output, the queries themselves are fine and the problem is concurrency, not logic. - **How long does the report take?** The exposure window is the wall-clock time between the first and last statement. A ten-minute report at a busy hour has a ten-minute window. - **Rule out the boring causes first**: different filter predicates between sections, rounding, `NULL` handling, a section that excludes a category the total includes. Concurrency is the interesting answer, not always the right one. ## Why READ COMMITTED does this READ COMMITTED makes exactly one promise about reads: you never see uncommitted data. It does **not** promise a stable view across statements. In a multiversion engine the implementation is literal — a **new snapshot is taken at the start of every statement**, so each `SELECT` sees everything committed up to that moment, including commits that happened after the previous statement in the same transaction. In a lock-based engine the shared read lock is released as soon as the statement finishes, leaving the data free to change. Crucially, wrapping the twelve queries in `BEGIN ... COMMIT` does not help at this level. The transaction gives atomicity for writes and a unit of work; it does not, by itself, give a stable read view. Many teams discover this exactly this way. One thing that *is* safe: a **single statement** is evaluated against one view from start to finish. A `SELECT` scanning for two minutes never sees half of a concurrent transaction's work. That fact is the basis for the cheapest fix. ## The fixes, with their trade-offs ### 1. One statement Rewrite the report so all sections come from one query — a common table expression computing the base set once and deriving every section and the total from it, or a single grouped aggregate the application pivots. Because it is one statement, it uses one view, and the totals cannot disagree. *Pros*: no isolation change, no long snapshot, usually faster because the base data is scanned once. *Cons*: a bigger, harder-to-read query; may need a temporary result or materialization if the base set is large; not always expressible when sections come from genuinely different sources. ### 2. A transaction-wide snapshot Run the report's transaction at REPEATABLE READ (or the engine's snapshot level). All statements then read from the snapshot established by the first statement, so the twelve queries describe one instant. *Pros*: minimal code change — a session or transaction setting. *Cons*: the snapshot must be retained for the report's whole duration, which prevents the engine from reclaiming superseded row versions **database-wide**, causing table and index bloat and, in some engines, an error if the retained history is exceeded. A ten-minute report is usually fine; an hour-long one on a write-heavy system is a known way to bloat production. Also, if the reporting transaction writes anything (a run log, a materialized result), it can now hit serialization failures and needs a retry. If you take this route, also consider explicitly declaring the transaction read-only, which lets the engine skip write-related bookkeeping and makes the intent obvious to operators. ### 3. A point-in-time copy Run the report against a read replica or an extract loaded into staging. The replica gives a consistent view without competing with the primary's write workload, and a staged extract removes the concurrency question entirely. *Pros*: the primary is untouched, the report can be as slow as it likes, and repeat runs are reproducible. *Cons*: the data is as of the copy, replication lag must be understood and stated on the report, and you now own a pipeline. ### 4. Lock the data Technically you could hold locks over everything the report reads, but for a reporting workload this blocks writers for the duration and is almost always the wrong answer. Mention it only to dismiss it. ## What does not work - **Re-running a query until two runs agree.** You are sampling a moving target; agreement is luck. - **Sorting or ordering the statements differently.** The window remains. - **Adding a transaction around the queries at READ COMMITTED.** As above, that is precisely what already failed. - **Rounding or reconciling the difference in the application.** This hides a correctness problem and makes the next investigation harder. ## Choosing For a report of a few seconds, the snapshot level is the simplest correct answer. For a long or heavy report, move it off the primary — a consistent view for an hour is a storage-retention problem, not a query problem. And whenever the whole answer can be expressed as one statement, prefer that: it is the only option that costs nothing extra and cannot be undone by someone changing a session setting later. Whichever you choose, record the intended semantics on the report itself — "as of 02:00", or "consistent snapshot of the run start" — because a report that is internally consistent but silently stale causes a different argument next quarter.