Notestransactions🌱 seedling

Serializable Snapshot Isolation

Also: SSI · Serializable SI · SIRead Lock · Dangerous Structure · rw-antidependency

Snapshot isolation plus a runtime check for the one pattern it misses: two consecutive read-write dependencies through one transaction. Reads still never block writes; instead, one transaction of a dangerous pair is aborted at commit.

Why it exists

Snapshot Isolation is fast but allows Write Skew; Two-Phase Locking is serializable but makes readers and writers block each other. SSI gets serializability at close to snapshot-isolation cost.

How it works

  • Every non-serializable history under snapshot isolation contains a dangerous structure: T1 →rw T2 →rw T3 (T1 and T3 may be the same), where T3 commits first.
  • Reads leave SIRead locks on tuples, pages, relations and index ranges. They never block; a write that hits one records an rw-dependency.
  • If a transaction acquires an incoming and an outgoing rw-dependency in a dangerous order, one transaction is aborted (Postgres: SQLSTATE 40001).
  • SIRead locks outlive the reader's commit, because a dependency can form afterwards.
  • Read-only optimisation: a SERIALIZABLE READ ONLY DEFERRABLE transaction waits for a safe snapshot, then runs with no tracking and no abort risk.

Trade-offs

Gains Costs
serializable without read locks false-positive aborts: not every dangerous structure is a cycle
long readers don't block writers memory for SIRead locks; promotion to coarser locks raises aborts
predictable latency clients need a retry loop

In practice

  • Postgres has used SSI for SERIALIZABLE since 9.1 (2011), the first production implementation; its benchmarks showed under 7% cost versus snapshot isolation.
  • Jepsen (2020) found a bug, present since 2011, that let Postgres Serializable allow G2-item (write skew); it was fixed in the August 2020 minor releases.

Pitfalls

  • Sequential scans take relation-level SIRead locks, raising the false-abort rate; good indexes help.
  • Only transactions running at Serializable are tracked. A Read Committed writer can still break the invariant.
  • Data read by a non-deferrable transaction isn't trustworthy until it commits.

Recall

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