Learn VisualLearn DB

Choose Which Concurrent Effects Are Visible

Isolation levels decide how much of a concurrent transaction's changes become visible to another. A non-repeatable read is when reading the same row twice returns two different values because another transaction committed an update in between. A phantom read is when running the same range condition twice returns a different set of matching rows because another transaction inserted or deleted rows in between. PostgreSQL maps Read Uncommitted to Read Committed. For example, if a price is updated from 100 to 120, Read Committed lets a second read within the same transaction see the new 120, while Repeatable Read uses a stable snapshot instead, keeps seeing 100, and prevents both non-repeatable and phantom reads. A serialization anomaly — a result that could not happen under any one-at-a-time ordering of the transactions, even though each looks correct alone — can still occur. Serializable also detects serialization anomalies, so applications must retry on failure.

GOAL

Relate Read Committed, Repeatable Read, and Serializable to observable anomalies.

What must an application do after a serialization failure?