Comparison
Non-repeatable readvsPhantom read
Non-repeatable read
you read the row at the start of the transaction, read it again at the end, and it has a different value because somebody committed in between.
The same row returning different values within one transaction. It is permitted at read committed and prevented at repeatable read. It bites in report generation and in any multi-step calculation, where two halves of the same answer are computed against different states of the world.
Full entry →Phantom read
you run the same range query twice in one transaction and the second time there is a new row matching it.
New rows appearing in a range that a repeated query covers, because another transaction inserted them. It is distinct from a non-repeatable read: nothing you already saw changed, the set grew. It is exactly the anomaly that breaks "check nothing exists, then insert", which is why unique constraints are a better answer than a SELECT.
Full entry →