Phantom Read
ELI5 — The Vibe Check
A phantom read is when you run the same query twice in a transaction and get different rows back because another transaction inserted or deleted matching rows in between. It's like counting people in a room, turning around, and counting again only to find more people showed up. Spooky.
Real Talk
A phantom read occurs when a transaction re-executes a query returning a set of rows and finds that the set has changed due to another committed transaction's inserts or deletes. It's allowed under Read Committed and Repeatable Read (in some databases) but prevented by Serializable isolation. PostgreSQL's Repeatable Read actually prevents phantoms through snapshot isolation.
When You'll Hear This
"Phantom reads are why your row count changed between two identical queries." / "PostgreSQL's Repeatable Read prevents phantom reads, unlike MySQL's version."
Related Terms
Dirty Read
A dirty read happens when you read data from another transaction that hasn't committed yet.
Read Committed
Read Committed is the default isolation level in PostgreSQL.
Serializable Isolation
Serializable isolation is the strictest mode where the database pretends all transactions run one after another, even though they're actually concurrent.
Snapshot Isolation
Snapshot isolation gives each transaction a frozen-in-time photo of the database.