Snapshot Isolation vs. Serializable Snapshot Isolation: Closing the Last Gap

1. The Basic Principle of Snapshot Isolation

Under snapshot isolation, every transaction sees a consistent snapshot of the database on its first read, exactly as it looked at the moment the transaction started, regardless of which other transactions are writing concurrently in the meantime. Changes made by other, simultaneously running transactions stay invisible to a given transaction until that transaction itself commits and starts a new snapshot.

On writes, the first-committer-wins rule applies: if two transactions try to change the same row, whichever commits first wins, while the second is rejected with an explicit conflict and needs to retry. This mechanism reliably prevents lost updates, as long as the conflict genuinely involves the same physical row.

-- PostgreSQL: explicitly operating under snapshot isolation
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE account_id = 1;
-- The snapshot is fixed on the first read, later concurrent
-- changes to other rows stay invisible to this transaction
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

2. Which Anomalies Snapshot Isolation Reliably Prevents

Dirty reads are fundamentally ruled out under snapshot isolation, because a transaction never sees data from an uncommitted, concurrent transaction. Non-repeatable reads do not occur either, since the same snapshot stays stable for the entire duration of the transaction, so a second SELECT against the same row is guaranteed to return the same value as the first.

Phantom reads are also largely prevented by the fixed snapshot: a range query returns the same consistent set of rows within a given transaction, even if new rows matching the filter are inserted concurrently. Snapshot isolation therefore fully covers the three classic anomalies from the SQL standard, which already makes it robust enough for most practical use cases.

3. The Write Skew Anomaly: the Classic On-Call Doctors Example

The standard example for write skew comes from a hospital on-call rotation: there is a business rule that at least one doctor must be on call at all times. Two on-call doctors, Doctor A and Doctor B, independently decide to call in sick. Both first read how many people are currently on call, both see two, and both correctly conclude that one of them can safely sign off without dropping below the minimum staffing requirement.

Both transactions therefore read the same starting situation, each makes a plausible, rule-compliant decision on its own, and each then writes a different row, namely their own sign-off. Because the two rows are different, the first-committer-wins rule never kicks in, both commits succeed, and in the end both doctors are signed off, even though the business rule demands at least one on call.

-- Transaction A (Doctor A calls in sick)
BEGIN;
SELECT count(*) FROM on_call WHERE active = true;  -- returns 2
UPDATE on_call SET active = false WHERE person = 'Doctor A';
COMMIT;

-- Transaction B (Doctor B calls in sick), started concurrently
BEGIN;
SELECT count(*) FROM on_call WHERE active = true;  -- also returns 2
UPDATE on_call SET active = false WHERE person = 'Doctor B';
COMMIT;
-- Result: 0 people on call, business rule violated

4. Why Write Skew Is Possible at All Under Plain Snapshot Isolation

The first-committer-wins mechanism of snapshot isolation only detects direct write conflicts on the same physical row. In the on-call doctors example, however, each transaction writes a different row; the consistency violation does not come from a write conflict at all, but from the logical dependency between what was read and what another transaction later writes.

This kind of dependency is called a read-write antidependency in the literature: transaction A reads a set of rows that transaction B subsequently modifies, and conversely transaction B reads a set that transaction A modifies. Only when both directions of that dependency occur at the same time does the classic write skew anomaly emerge, and a plain row-level conflict check can, by definition, never detect that pattern.

5. Serializable Snapshot Isolation: the Basic Idea

Serializable snapshot isolation builds on the same snapshot mechanism but adds runtime monitoring that specifically looks for the read-write antidependencies described above between concurrently running transactions. When the database detects a pattern that could lead to a non-serializable execution, it deliberately aborts one of the involved transactions with a serialization failure error, instead of letting both commit.

The key conceptual difference from classic, lock-based serializable isolation is that SSI stays optimistic: no locks are held for reads, dependencies are merely tracked and resolved after the fact in case of conflict, which produces noticeably less overhead than pervasive pessimistic locking under low to moderate conflict rates.

6. Implementations: PostgreSQL SERIALIZABLE Compared to Classic Locking

PostgreSQL has implemented serializable snapshot isolation since version 9.1, directly behind the SERIALIZABLE isolation level, selectable through a simple transaction setting with no change to the actual SQL required. Other systems, such as classic, older SERIALIZABLE implementations in various databases, instead rely on strict two-phase locking, where reads take out real locks that actively block other transactions, rather than merely detecting conflicts after the fact.

The practical difference shows up mostly under load: lock-based serializable isolation can cause noticeable blocking, and in the worst case deadlocks, when many concurrent reads target overlapping data ranges, while SSI never blocks reads at all, but in a conflict case rejects an already running transaction with an abort that the application has to catch and retry.

7. The On-Call Doctors Example Replayed Under SSI

Under SERIALIZABLE in PostgreSQL, both transactions from the example above initially run identically: both read a count of 2, both decide to sign off. When the second transaction attempts to commit, however, PostgreSQL detects the read-write antidependency between the two transactions and rejects that commit with an explicit serialization failure error, while the first transaction commits normally.

The application has to be prepared for this error and retry the affected transaction. On the second attempt, the retried transaction reads the already reduced count of 1 and correctly rejects the sign-off itself, so the minimum staffing business rule actually holds, without ever needing an explicit lock on the counting table.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE active = true;
UPDATE on_call SET active = false WHERE person = 'Doctor A';
COMMIT;
-- The second, concurrent transaction gets on COMMIT:
-- ERROR: could not serialize access due to read/write dependencies
-- Application must re-run the transaction with retry logic

8. The Cost of SSI: Overhead and a Higher Abort Rate

SSI is not free: the database has to track, for every transaction, which sets of rows were read and written, just to be able to detect potential read-write antidependencies at all, which means additional memory and CPU cost compared to plain snapshot isolation. For workloads with many, heavily contending transactions on overlapping data ranges, the abort rate from serialization failures also rises noticeably.

Any application using SERIALIZABLE therefore has to implement retry logic for exactly this error case, typically with a limited number of retry attempts and a short, randomized backoff in between, to make repeated collisions between the same transactions less likely.

9. Practical Recommendation: When Snapshot Isolation Is Enough and When SSI Is Needed

Plain snapshot isolation is sufficient for the vast majority of use cases, especially when business rules can always be expressed against a single row, such as account balances or inventory counts, where the first-committer-wins mechanism reliably catches conflicts. In those cases SSI offers no additional benefit, yet still incurs the extra overhead of dependency tracking.

SSI becomes relevant once a business rule spans several independent rows and several concurrent transactions can each make an individually consistent but jointly contradictory decision, as in the on-call doctors example. Capacity limits, mutually exclusive states across multiple entities, and similar constraint-like rules are typical candidates where switching to SERIALIZABLE is worth the extra cost despite the overhead.

10. Summary

11. FAQ: Snapshot Isolation vs. SSI at a Glance