What are isolation levels and read anomalies?
Understand the concept
Isolation defines how one transaction can observe and interfere with concurrent transactions. Different levels allow different anomalies to balance correctness, concurrency and retry cost. PostgreSQL uses snapshots under MVCC, but even a stable snapshot does not automatically make every business decision safe against concurrent writes.
Important ideas
- Dirty read: uncommitted data; non-repeatable read: a row changes; phantom: matching rows change.
- PostgreSQL Read Committed uses a new snapshot per statement; Repeatable Read uses a stable transaction snapshot.
- Serializable prevents serialization anomalies, but applications must retry failed transactions; PostgreSQL treats Read Uncommitted as Read Committed.
Reference: PostgreSQL isolation.
- Repeatable Read can still allow write-skew anomalies; Serializable detects incompatible concurrent histories and may abort a transaction.
Comparison — PostgreSQL:
| Level | Typical read view | Important limitation |
|---|---|---|
| Read Committed | New snapshot per statement | A later read may see a changed result |
| Repeatable Read | Stable transaction snapshot, plus own writes | Some cross-row business anomalies remain |
| Serializable | Successful transactions fit a serial order | Conflicting work may need a full retry |
- PostgreSQL maps Read Uncommitted to Read Committed and prevents phantom reads at Repeatable Read. PostgreSQL isolation rules.
Can you explain it without the notes?
What are isolation levels and read anomalies?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
Isolation defines how one transaction can observe and interfere with concurrent transactions. Different levels allow different anomalies to balance correctness, concurrency and retry cost. PostgreSQL uses snapshots under MVCC, but even a stable snapshot does not automatically make every business decision safe against concurrent writes.
Turn this concept into a clear answer.
- Explain a real anomaly and the chosen protection, then mention whole-transaction retries where the database can reject conflicting work.
Adapted from 05_SQL_POSTGRESQL.md · “What are isolation levels and read anomalies?”. Examples are study fragments; framework behavior can depend on your version.