Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 36

Snapshot Isolation & MVCC Explained

Recommended resources

Hello! Welcome to our next lesson in the series on database concurrency control.

In our last session, we introduced Multi-Version Concurrency Control (MVCC) as a powerful, optimistic alternative to locking. We saw how creating new versions of data instead of overwriting it allows readers and writers to operate in parallel without blocking each other. This is achieved by giving each transaction a consistent "snapshot" of the database.

Today, we're going to formalize this concept by studying Snapshot Isolation (SI), a popular and highly efficient isolation level implemented by many modern databases like PostgreSQL, Oracle, and Microsoft SQL Server. Our goal is to describe the precise mechanism of snapshot isolation: how it uses MVCC to provide a consistent view, and, crucially, how it handles conflicts when multiple transactions try to write to the same data.

The Core Principle of Snapshot Isolation

The fundamental guarantee of Snapshot Isolation is straightforward:

A transaction running under Snapshot Isolation appears to operate on a perfectly consistent, personal snapshot of the database, frozen at the moment the transaction began.

This means all reads within the transaction will see the same data, regardless of what other concurrent transactions are doing. This immediately provides two important guarantees:

  1. No Non-Repeatable Reads: If you read a value, and then read it again later in the same transaction, you will always get the same value.
  2. No Phantoms: If you run a query that counts the number of rows matching a certain condition, that count will not change if you run the same query again.

Let's visualize how this works.

Implementing Snapshot Isolation using Multi-Version Objects
This diagram illustrates Snapshot Isolation. Transaction `txid=12` starts and takes a snapshot of the database where Account 1 has $500 and Account 2 has $500. Shortly after, `txid=13` starts, updates both accounts, and commits, creating new versions (`v2`). However, when `txid=12` later reads the accounts, it still sees the old `v1` versions from its original snapshot, ensuring a consistent view.

As you can see, txid=12 is completely isolated from the changes made by txid=13. The mechanism enabling this is the versioning system we discussed last lesson, which relies on transaction IDs to determine visibility.

How a Snapshot is Implemented

To understand the mechanism, let's revisit how versions are managed. Each version of a row has metadata, typically including the transaction ID that created it (xmin) and the transaction ID that made it obsolete (xmax). When a transaction T starts, the system effectively defines its "snapshot" by determining the set of all transactions that had already committed before T began.

For a row version to be visible to transaction T, two conditions must be met:

  1. The transaction that created the version (xmin) must be in T's snapshot (i.e., it must have committed before T started).
  2. The version must not have been deleted by a transaction in T's snapshot (xmax is either null or belongs to a transaction that committed after T started).

The following video segment from the CMU Database Systems course provides an excellent, detailed walkthrough of this process using begin and end timestamps, which serve the same purpose as xmin and xmax.

18 - Multi-Version Concurrency Control MVCC (CMU Intro to Database Systems / Fall 2022)

This clip illustrates how a transaction's start time determines its view of the database. Pay close attention to how the begin and end timestamps on each version are used to decide which one is visible.

Watch from 32:53 to 43:37. This will reinforce the concept of a consistent snapshot and how a transaction reads the correct version of data based on its timestamp, guaranteeing repeatable reads.

As the video demonstrates, the transaction always reads the version that was "current" when it started, creating the illusion of a static database. Your experience with data structures and algorithms should make it clear how this visibility check—essentially a range check on transaction IDs—can be implemented efficiently.

The Write-Conflict Rule: First-Committer-Wins

Reading from a snapshot is only half the story. What happens when two concurrent transactions try to write to the same data? This is where Snapshot Isolation enforces its key rule to prevent lost updates.

Let's say two transactions, T1 and T2, both start at roughly the same time. They both read the same snapshot of the database.

  • T1 reads a value x=10 and decides to write x=11.
  • T2 also reads x=10 and decides to write x=12.

If both were allowed to commit, one of the updates would be lost. Snapshot Isolation prevents this with a simple rule: First-Committer-Wins.

When a transaction T attempts to commit, the system checks if any other concurrent transaction has already committed a write to an item that T also wrote to.

  • If there is no conflict, T commits successfully. Its changes become visible to all transactions that start after it commits.
  • If there is a conflict, T must abort and roll back.

This check is performed at commit time. It's an optimistic check—the system hopes for the best and only verifies for conflicts at the very end.

The following technical blog post provides a fantastic, code-level explanation of how to implement MVCC and Snapshot Isolation. It's written in Go, but the data structures and logic are directly applicable to your knowledge of C++ and Java.

Implementing MVCC and major SQL transaction isolation ...

This blog post walks through building a toy MVCC database. Focus on the data structures and the specific logic for Snapshot Isolation.

First, read the 'Scaffolding' section to understand the core data structures, especially Value (with txStartId, txEndId) and Transaction (with writeset). Then, read the 'Snapshot Isolation' section. It defines the isolation level and shows the implementation of the commit-time check that aborts a transaction if a write-write conflict is detected.

The key implementation detail from that post is the writeset. By tracking the set of keys each transaction writes to, the database can efficiently check for overlaps at commit time. If transaction T1's writeset has any keys in common with the writeset of another transaction T2 that has committed since T1 began, T1 must abort.

Test your understanding!

Two concurrent transactions, T_Alice (txid=101) and T_Bob (txid=102), are running under Snapshot Isolation. They both manage the inventory for a product, product_id = 'abc', which currently has quantity = 10.

  1. T_Alice starts, reads quantity = 10.
  2. T_Bob starts, reads quantity = 10.
  3. T_Alice updates quantity to 8 (a sale of 2 items).
  4. T_Bob updates quantity to 7 (a sale of 3 items).
  5. T_Alice attempts to commit.
  6. T_Bob attempts to commit.

Describe the outcome for both transactions based on the "First-Committer-Wins" rule.

Show answer
  1. When T_Alice attempts to commit at step 5, the system checks for conflicts. No other transaction that started concurrently with T_Alice has committed a write to product_id = 'abc'. Therefore, T_Alice succeeds and commits. The new official version of the product has quantity = 8.
  2. When T_Bob attempts to commit at step 6, the system runs the same check. It sees that T_Bob wrote to product_id = 'abc'. It also sees that T_Alice (which was concurrent with T_Bob) has already committed a write to that same key. This is a write-write conflict. Therefore, T_Bob must abort.

The application would then typically retry T_Bob's transaction, which would now start with a new snapshot, read quantity = 8, and correctly update it to 5.

Conclusion

We've now formally defined the mechanism of Snapshot Isolation. It combines the non-blocking reads of MVCC with a specific rule for handling write conflicts.

Key Takeaways:

  • Snapshot Isolation guarantees that a transaction reads from a consistent database snapshot taken at its start time. This provides repeatable reads and prevents phantoms.
  • This is implemented using MVCC, where row versions are tagged with transaction IDs (xmin/xmax or timestamps) that allow the system to determine visibility for a given snapshot.
  • Write-write conflicts are resolved with a "First-Committer-Wins" rule. A transaction is aborted at commit time if it has written to any data item that was also written to by another concurrently-committing transaction.
  • This conflict detection is often implemented by tracking a writeset for each transaction and checking for overlaps at commit.

Preview of the Next Lesson

Snapshot Isolation is very powerful and avoids most concurrency anomalies. However, it is not fully serializable. There is a subtle but important anomaly it allows, known as write skew. In our next lesson, we will dissect this anomaly, see how it can occur even with Snapshot Isolation's protections, and understand why SERIALIZABLE isolation is sometimes necessary for absolute correctness.