Phantom Reads From Misconfigured Isolation Levels Caused Intermittent Revenue Mismatches

An engineering team found that category revenue totals sometimes disagreed with logs recorded moments earlier, but only during peak production batch runs. Investigation traced the cause to a mismatch between database transaction isolation levels and the default session behavior of their SQLAlchemy ORM. Under Read Committed isolation, a reader transaction that repeated a range query could see rows newly committed by a concurrent writer, causing sums and counts to shift mid-transaction. To study the problem, the team built a deterministic Python simulation that runs concurrent readers and writers across different isolation levels and logs phantom read occurrences. The write-up presents the simulation as a way to show how stricter isolation settings such as Serializable prevent the anomaly.