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.

Recall

These cards are also in your review deck, scheduled with spaced repetition.