Snapshot Isolation Primer

1. The snapshot contract

When a transaction begins, the database hands it a logical clock tick. Every read inside that transaction returns the most recent row version committed strictly before the tick. Writes by concurrent transactions are invisible until the reader commits and starts a new snapshot. This is what gives you repeatable reads without explicit locks.

2. Write conflicts and the first-committer-wins rule

Two transactions can read the same row inside their respective snapshots and both attempt to update it. Snapshot isolation resolves this with first-committer-wins: the later writer is aborted with a serialization failure. Your Meridian retry loop should catch this and replay the transaction against a fresh snapshot rather than surfacing the error to the caller.

3. Write skew and when to upgrade

Snapshot isolation does not prevent write skew: two transactions read an overlapping set, each writes to a disjoint subset, and both commit even though the combined result violates a global invariant. If your workload has cross-row invariants such as shift coverage or balance floors, upgrade those specific transactions to serializable, or take an advisory lock on a sentinel row that all participants must touch.

Example transaction

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Reader sees a consistent snapshot
SELECT balance FROM accounts WHERE id = 42;

-- Writer in another tx commits between these statements
-- Snapshot isolation: we still see the OLD value
SELECT balance FROM accounts WHERE id = 42;

COMMIT;