MVCC and Snapshot Isolation

Isolation — Snapshot Reads

Isolation is the "I" in ACID: it defines what a transaction is allowed to see while other transactions are running concurrently. The strongest (and most expensive) isolation level is serializable, where transactions behave as if run one at a time. A much cheaper and very common alternative — used by Postgres, MySQL/InnoDB, Oracle and SQLite's WAL mode — is snapshot isolation, and it's the mechanism behind MVCC (multi-version concurrency control).

The core idea

When a transaction takes a snapshot, it freezes its view of the database at that instant. Any reads issued afterward see the data exactly as it was at snapshot time — even if other writers (or the same session, outside the transaction) mutate the underlying tables in the meantime. This gives readers a consistent, non-blocking view without needing to lock anything: writers keep writing, and readers keep reading an unchanging copy.

Think of it like a photograph: once you press the shutter, the picture doesn't change even if the room keeps moving.

SNAPSHOT
SELECT * FROM users        -- sees data as of SNAPSHOT
...other session inserts a row...
SELECT * FROM users        -- still sees the OLD data, same snapshot
SNAPSHOT RELEASE
SELECT * FROM users        -- now sees LIVE data, including the new row

What you need to build

Your engine needs to track two things:

1. A saved copy (or reference) of the table state taken the moment SNAPSHOT runs.
2. A flag for "is a snapshot currently active" that routes SELECT to either the frozen copy or the live table.

The simplest correct implementation is a deep copy of the relevant table(s) at snapshot time. In a real MVCC engine, rather than copying whole tables, each row keeps multiple versions tagged with the transaction/commit timestamp that created them, and a snapshot is really just a cutoff timestamp — "give me the newest version of each row that existed before I started." Copying the table is a simplification that produces the same observable behavior for this exercise.

The debugging-command exception

Notice the spec calls out that .count and other REPL introspection commands must always show the live table, snapshot or not. These are meta-commands for you (the operator) to sanity-check the real state of the database — they are not part of any transaction's read view, so they must bypass the snapshot redirection entirely. This is an important distinction to get right: it's easy to accidentally route all reads through the "if snapshot active, use snapshot" branch. Keep the snapshot substitution scoped specifically to SELECT.

Edge cases to watch

• SNAPSHOT called while one is already active — decide whether it re-freezes or is a no-op (be consistent, and check what the test suite expects).
• SNAPSHOT RELEASE with no active snapshot should probably be a harmless no-op or an explicit error — don't crash.
• Writes issued while a snapshot is active: do they affect the live table only, or also need to be excluded from the snapshot's view? Since the snapshot is frozen at capture time, writes after SNAPSHOT should only be visible once the snapshot is released.

This lesson is a stepping stone toward the later MVCC lesson, where you'll replace "copy the whole table" with proper per-row version chains and commit timestamps — the technique real databases use to give every transaction a private, consistent view without ever blocking a reader against a writer.