Snapshot Isolation — MVCC Concepts

How do you let multiple transactions read and write the same data without conflicts? The answer is MVCC (Multi-Version Concurrency Control) — keeping multiple versions of each row.

The Problem

Without isolation, concurrent transactions interfere:

Transaction A:                Transaction B:
SELECT balance FROM acct;
→ 1000                        UPDATE acct SET balance = 500;
SELECT balance FROM acct;
→ 500 (changed mid-transaction!)

Transaction A sees different values for the same query. This is called a non-repeatable read.

Snapshot Isolation

With snapshot isolation, each transaction sees a frozen snapshot of the database as it existed when the transaction began:

Transaction A (started at T=1):    Transaction B (started at T=2):
SELECT balance FROM acct;           UPDATE acct SET balance = 500;
→ 1000                              COMMIT;
SELECT balance FROM acct;
→ 1000 (still sees T=1 snapshot!)

Transaction A always sees the data from time T=1, regardless of what B does.

How MVCC Works

Instead of modifying rows in-place, MVCC creates new versions:

Row (id=1):
  Version 1: balance=1000, created_by=Txn10, valid T=1..∞
  Version 2: balance=500,  created_by=Txn20, valid T=2..∞

When Transaction A (snapshot at T=1) reads: sees Version 1 (balance=1000) When Transaction C (snapshot at T=3) reads: sees Version 2 (balance=500)

Visibility Rules

A row version is visible to a transaction if:

1. The version was created by a committed transaction
2. The creating transaction committed before the reading transaction's snapshot

Is Version V visible to Transaction T?
  if V.creator == T: yes (own changes)
  if V.creator committed before T.snapshot: yes
  otherwise: no

Write Conflicts

What if two transactions try to modify the same row?

Transaction A: UPDATE acct SET balance = 900 WHERE id = 1
Transaction B: UPDATE acct SET balance = 800 WHERE id = 1

Under snapshot isolation, the first writer wins. If A commits first, B gets a "serialization failure" and must retry.

How PostgreSQL Implements MVCC

PostgreSQL stores all row versions in the same table (heap). Each tuple has xmin (creating transaction) and xmax (deleting transaction) headers. Visibility is checked on every read by comparing these with the current transaction's snapshot. Old versions are cleaned up by VACUUM.

How SQLite Handles Isolation

In WAL mode, SQLite readers see a snapshot of the database at the time they started reading. Writers append to the WAL. Readers ignore WAL entries newer than their snapshot. This provides snapshot isolation without the complexity of per-row versioning.

Your Task

Implement SNAPSHOT (freeze current state) and SNAPSHOT RELEASE. Queries after SNAPSHOT see the frozen state even if data changes.