Problem Statement
A report reads SUM(amount) WHERE date BETWEEN d1 AND d2 twice and gets different totals. Explain the cause and two fixes.
Explanation
You are seeing phantoms or concurrent updates changing rows in the range. Between scans, other units inserted or updated values that match the predicate, so the second read changed.
Fix by using a higher isolation level. Serializable prevents phantoms by locking the predicate. Or design the report to read a stable snapshot, such as Repeatable Read in MVCC, or use a time-boxed ETL snapshot table for consistent reads.
Code Solution
SolutionRead Only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT SUM(amount) FROM sales WHERE dt BETWEEN :d1 AND :d2;
Practice Sets
This question appears in the following practice sets:
