Snapshot Isolation

Snapshot Isolation (SI) is a concurrency control mechanism where each transaction reads from a consistent snapshot of the database taken at the transaction’s start time. All reads see the same committed state, regardless of concurrent modifications. Implemented via MVCC.

How It Works

1. Transaction T starts → gets snapshot timestamp ts
2. All reads by T see data as of ts (ignoring later commits)
3. Writes create new versions visible only to T until commit
4. At commit: if no other transaction modified the same rows → commit succeeds. Otherwise → first-committer-wins — T aborts.

SI vs Serializability

Snapshot Isolation prevents dirty reads, non-repeatable reads, and phantom reads — but it is not serializable. It allows write skew:

-- Two doctors on call, constraint: at least one must remain
T1 reads: both Alice and Bob are on call
T2 reads: both Alice and Bob are on call
T1: UPDATE SET on_call = false WHERE name = 'Alice'  -- Bob still on call (in T1's snapshot)
T2: UPDATE SET on_call = false WHERE name = 'Bob'    -- Alice still on call (in T2's snapshot)
-- Both commit → neither is on call → constraint violated

Serializable Snapshot Isolation (SSI)

PostgreSQL’s Serializable level adds conflict detection on top of SI. It tracks read/write dependencies and aborts transactions that could produce non-serializable results. This gives full Serializability without the performance cost of lock-based serializability.