What are isolation levels and read anomalies?
Practice this questionWANT TO UNDERSTAND THE CONCEPT FIRST?Learn What are isolation levels and read anomalies?
Definition:
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.
Key points:
- 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.
Interview point:
- 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?”. Code examples are study fragments. Difficulty and core labels are editorial estimates; version-specific behavior should be checked against your project.