Snapshot Concept

Imagine you're taking a photograph of a bustling marketplace. In that single instant, the camera captures every vendor, every customer, every product—frozen in time. Even as the marketplace continues to buzz with activity after the shutter clicks, your photograph preserves an exact, consistent view of that precise moment. This is the essence of a snapshot in database systems.

A database snapshot provides a transaction with a logically consistent view of all data as it existed at a specific point in time. While other transactions continue to read and modify data concurrently, your transaction operates on its own private, immutable view—unaffected by changes happening elsewhere. This powerful abstraction enables remarkable levels of concurrency while maintaining the consistency guarantees that databases must provide.

A database snapshot is a logically consistent, read-only view of the database at a specific point in time, called the snapshot timestamp. When a transaction begins under snapshot isolation, it establishes a snapshot and all subsequent reads within that transaction will observe the database state as it existed at that timestamp—regardless of any concurrent modifications made by other transactions.

Formal Definition:

Let $T_i$ be a transaction that begins at time $t_s$ (the snapshot timestamp). The snapshot $S(T_i)$ is defined as:

$$S(T_i) = \{ (x, v) \mid x \text{ is a data item with value } v \text{ at time } t_s \}$$

For every read operation $R_i(x)$ performed by $T_i$, the value returned is $v$ where $(x, v) \in S(T_i)$, irrespective of any writes $W_j(x)$ by concurrent transactions $T_j$ where $j eq i$.

Key Properties of Snapshots:

1. Point-in-Time Consistency

A snapshot represents the database at exactly one moment. All data items in the snapshot are mutually consistent—they reflect the committed state of the database at the snapshot timestamp. There are no "torn reads" where some data reflects one moment and other data reflects another.

2. Immutability

Once established, a snapshot never changes. Even if the underlying data is modified by other transactions, the snapshot remains frozen. This immutability is what enables non-blocking reads—readers never wait for writers.

3. Isolation from Concurrent Modifications

A transaction reading from its snapshot is completely isolated from uncommitted changes made by concurrent transactions. It sees only data that was committed before the snapshot timestamp.

4. Logical, Not Physical

Snapshots are logical constructs. The database doesn't actually copy all data at the snapshot moment. Instead, it uses version chains or other mechanisms to reconstruct the correct version of each data item when requested.

To appreciate the power of snapshots, we must contrast them with traditional locking-based concurrency control. In lock-based systems, readers and writers can block each other—leading to reduced concurrency, potential deadlocks, and complex lock management. Snapshots fundamentally transform this equation.

The Lock-Based World:

In strict two-phase locking (2PL), a transaction reading data item $x$ must acquire a shared lock $S(x)$, and a transaction writing $x$ must acquire an exclusive lock $X(x)$. These locks conflict:

• A writer holding $X(x)$ blocks all readers attempting to acquire $S(x)$
• Readers holding $S(x)$ block any writer attempting to acquire $X(x)$

This means long-running read transactions can delay critical write operations, and write-heavy workloads can starve readers.

The Snapshot-Based World:

With snapshots, readers don't acquire locks on data items at all. Instead, they read from their snapshot—a frozen view of past state. Writers modify the "live" current state. Because readers and writers operate on different versions of data, they never conflict.

When Is the Snapshot Taken?

A critical design decision in snapshot-based systems is determining when a transaction's snapshot timestamp is assigned. This choice affects both the consistency semantics and the visibility of concurrent updates.

Option 1: Transaction Start Time

The snapshot is established when the transaction begins (issues its first statement or explicit BEGIN command). This is the most common approach, used in PostgreSQL's REPEATABLE READ and Oracle's READ COMMITTED (with modifications).

T1 begins at time 100 → Snapshot timestamp = 100
T1 sees all changes committed before time 100
T1 does NOT see changes committed at time 101, 102, ... (even if they commit while T1 runs)

Option 2: Statement Start Time

A new snapshot is established for each SQL statement within the transaction. This is PostgreSQL's READ COMMITTED behavior.

T1 begins at time 100
  Statement S1 at time 100 → Snapshot = 100
  Statement S2 at time 150 → Snapshot = 150 (sees more recent commits)
  Statement S3 at time 200 → Snapshot = 200 (sees even more recent commits)

Option 3: First Read Time

The snapshot is established lazily when the transaction performs its first read operation. This can reduce overhead for write-only transactions.

Timestamp Generation Mechanisms:

The system needs a reliable way to generate monotonically increasing timestamps. Common approaches include:

1. System Clock Timestamps

Use wall-clock time (e.g., microseconds since epoch). Simple but can have issues with clock skew in distributed systems.

2. Logical Counter (Transaction ID)

Maintain a global counter that increments for each new transaction. PostgreSQL uses 32-bit transaction IDs (XIDs). This ensures strict ordering but requires wraparound handling.

3. Hybrid Logical Clocks

Combine physical time with logical counters to get the benefits of both: approximate real-time ordering with guaranteed monotonicity. Used in distributed databases like CockroachDB.

4. Lamport Timestamps

Pure logical clocks that maintain happened-before relationships. Useful in distributed systems but don't correlate with real time.

When a transaction reads data under snapshot isolation, it must determine which version of each data item is visible. This requires precise visibility rules that consider transaction states and commit timestamps.

The Core Visibility Question:

Given a transaction $T_{reader}$ with snapshot timestamp $ts$, and a data item $x$ with multiple versions $\{v_1, v_2, ..., v_n\}$ created by transactions $\{T_1, T_2, ..., T_n\}$, which version should $T_{reader}$ see?

Visibility Rule:

Version $v_i$ created by $T_i$ is visible to $T_{reader}$ if and only if:

1. $T_i$ committed before $T_{reader}$'s snapshot was taken
2. $T_i$'s commit timestamp $\leq ts$ (the snapshot timestamp)
3. $v_i$ is the most recent version satisfying conditions 1 and 2

Among all visible versions, the transaction sees the one with the highest commit timestamp.

The Active Transaction List:

A critical component of snapshot visibility is tracking which transactions were active (in-progress) when the snapshot was taken. Consider this scenario:

Time 100: T1 begins
Time 110: T2 begins (T2's snapshot sees T1 as active)
Time 120: T1 writes X = 10
Time 130: T1 commits
Time 140: T2 reads X

Even though T1 committed (at time 130) before T2 reads (at time 140), T2 should NOT see T1's changes. Why? Because when T2 took its snapshot at time 110, T1 was still active. T2's snapshot captured the state before T1 committed.

This is why snapshots must record the list of active transaction IDs at the snapshot moment. Any changes from these transactions are invisible, regardless of when they commit.

Snapshots are the logical abstraction; Multi-Version Concurrency Control (MVCC) is the implementation mechanism that makes snapshots practical. Without MVCC, providing each transaction with its own consistent view would require either:

1. Copying the entire database at snapshot time (impossibly expensive)
2. Blocking all writes during reads (defeats the purpose)

MVCC solves this by maintaining multiple versions of each data item, allowing different transactions to see different versions simultaneously.

How MVCC Enables Snapshots:

1. When a transaction writes data item $x$, it creates a new version rather than overwriting the old one
2. Each version is tagged with the creating transaction's ID and timestamp
3. The old version remains available for transactions with older snapshots
4. A garbage collection process (VACUUM in PostgreSQL) eventually removes versions no longer needed by any active snapshot

The Version Chain:

MVCC maintains data as a chain of versions, typically ordered from newest to oldest. When a transaction reads:

1. Start at the head of the version chain (newest version)
2. Walk backward through versions
3. Stop at the first version that passes visibility rules
4. Return that version's value

This allows each reader to find its appropriate version without blocking any writer.

Cost of Versioning:

MVCC's benefits come with costs:

• Storage overhead: Multiple versions consume more space
• Version chain traversal: Long chains slow down reads
• Garbage collection: Old versions must be cleaned up
• Index maintenance: Some implementations require index entries for each version

These costs are manageable with proper tuning (vacuum frequency, version chain limits) and are generally outweighed by the concurrency benefits.

What exactly does a snapshot guarantee? Understanding the precise consistency semantics is crucial for designing correct applications.

Guarantees Provided by Snapshots:

1. Point-in-Time Consistency (Snapshot Read)

All reads within a transaction observe the same database state. If you read table A and then table B, both reflect the committed state at your snapshot timestamp. There are no "torn reads" across tables.

2. Repeatable Reads

If you read the same row twice within a transaction, you get the same value both times (assuming you use transaction-level snapshots, not statement-level). This eliminates the non-repeatable read anomaly.

3. No Dirty Reads

You only see committed data. Uncommitted changes from other transactions are invisible, eliminating dirty read anomalies.

4. Read Stability

Once you observe a data item's value, that value remains stable for the duration of your transaction. External updates don't affect your view.

Guarantees NOT Provided by Snapshots Alone:

Snapshots provide excellent read consistency but do not, by themselves, prevent all anomalies:

1. Lost Updates

Two transactions can both read the same value, compute updates, and write. One update overwrites the other. Snapshot isolation requires additional mechanisms (first-committer-wins) to prevent this.

2. Write Skew

Two transactions can read overlapping data, make decisions based on that data, and write to non-overlapping items in ways that violate application constraints. This is the signature anomaly of snapshot isolation and will be covered in detail in a later page.

3. Serialization Anomalies

Snapshot isolation does not guarantee serializability. There exist schedules that are allowed under SI but are not equivalent to any serial execution.

These limitations are important—snapshot isolation is powerful but not a complete solution for all consistency requirements.

Let's trace through a concrete example to solidify understanding. Consider a banking system with an accounts table:

Initial State (committed):

Account A: Balance = $1000
Account B: Balance = $500
Account C: Balance = $750

Timeline of Transactions:

Analysis:

T1 (Report Transaction): Started at time 100, generating a total balance report. Even though T2 and T3 both committed during T1's execution, T1 sees none of their changes. T1's snapshot captured A=$1000, B=$500, C=$750 at time 100, and it reports that consistent total: $2,250.

T2 (Transfer Transaction): Transferred $200 from A to B. It read A (seeing $1000 from its snapshot at time 110), wrote A=$800, wrote B=$700, and committed. These changes became visible to transactions starting after time 170.

T3 (Deposit Transaction): Deposited $100 to C. It read C from its snapshot (seeing $750), wrote C=$850, and committed. This change became visible after time 190.

Key Observations:

✓ T1's report is consistent—it shows the exact state at time 100 ✓ T1 was never blocked by T2 or T3's writes ✓ T2 and T3 were never blocked by T1's reads ✓ All three transactions completed successfully with maximum concurrency