Notestransactions🌱 seedling
Snapshot Isolation
Also: SI · Repeatable Read · Snapshot Read · Read Skew · Non-Repeatable Read · First-Committer-Wins
Each transaction reads from a consistent snapshot of everything committed when it started, and ignores later commits. Readers never block writers and writers never block readers, but [[Write Skew]] is still possible.
Why it exists
Under Read Committed a transaction can read some data before a concurrent commit and some after it (read skew, or a non-repeatable read). That's fatal for backups, analytics and integrity checks, which need one point in time.
How it works
- Implemented with MVCC: the snapshot decides which version of each row is visible.
- One snapshot for the whole transaction (vs one per statement under Read Committed).
- Write-write conflicts: if two concurrent transactions update the same row, the first committer wins and the other is aborted. Postgres: "could not serialize access due to concurrent update" (SQLSTATE 40001); Oracle: ORA-08177.
- That rule prevents Lost Updates, but not conflicts where the two transactions write different rows.
Trade-offs
| Gains | Costs |
|---|---|
| consistent reads without read locks | old versions must be stored and cleaned up |
| long read-only queries don't block writers | aborts on write-write conflict; retries needed |
| prevents read skew and most lost updates | allows write skew and phantom-driven write skew |
In practice
- Postgres Repeatable Read, Oracle Serializable, SQL Server
SNAPSHOT, and MongoDB multi-document transactions are snapshot isolation. - MySQL Repeatable Read is not: plain reads use a snapshot, but writes and locking reads see the latest data, so lost updates slip through.
- Replicas may weaken it: Jepsen found RDS for PostgreSQL Multi-AZ clusters (2025) occasionally showing a long fork, which snapshot isolation forbids.
Pitfalls
- Calling it serializable. It's what Oracle calls Serializable, and it still lets two transactions break a multi-row invariant.
- Long-running snapshots hold back version cleanup and bloat tables.
Connections
- Part of
- Isolation Level
- Contains
- MVCC
- Builds on this
- Serializable Snapshot Isolation
- Contrasts with
- Read CommittedSerializability
- In real systems
- PostgreSQL
Recall
These cards are also in your review deck, scheduled with spaced repetition.