When writing SQL queries against a large PostgreSQL database, I am seeing phantom reads despite setting the transaction isolation level to READ COMMITTED. Can you provide a concrete example of a multi-statement transaction where READ COMMITTED allows for phantom reads, and explain the precise scenario where REPEATABLE READ would be required?
-
2You're probably going to have to explain more which bit of the official manuals isn't clear on transaction isolation behaviour. postgresql.org/docs/18/transaction-iso.htmlRichard Huxton– Richard Huxton2025-11-16 09:46:13 +00:00Commented Nov 16 at 9:46
Add a comment
|
1 Answer
The READ COMMITTED isolation level allows phantom reads — all it guarantees is that you won't get dirty reads. You can get a phantom read whenever a concurrent transaction committed a change to one of the data you previously read. Reading the data again will return the latest committed version.
Use REPEATABLE READ if you want to prevent phantom reads.
