📸Snapshot Isolation

NEW

Also known as: MVCC Snapshot

An isolation level where transactions read from a consistent snapshot, avoiding read locks and non-repeatable reads.

DESCRIPTION

Snapshot isolation provides transactions with a consistent view of the database as of their start time, commonly implemented via MVCC. It prevents dirty and non-repeatable reads while enabling high concurrency without long-held read locks. However, it does not prevent write skew anomalies and is weaker than full serializable isolation. Many systems expose snapshot semantics through READ COMMITTED or REPEATABLE READ variants. Choosing snapshot isolation balances performance and correctness for many OLTP workloads.

ALTERNATIVE DEFINITIONS

Microsoft Learn

SNAPSHOT specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Formal T-SQL description of consistent snapshot visibility across a transaction.

Source

PostgreSQL Documentation

Each SQL statement sees a snapshot of the database as of the start of the statement (or of the transaction), not including uncommitted changes made by concurrent transactions.

Connects snapshot semantics to MVCC visibility rules in PostgreSQL.

Source

RELATED TERMS

Repeatable Read Multiversion Concurrency Control Write Skew Phantom Read Two-Phase Locking

COMPETING TERMS

Serializable Isolation Read Committed

RELATIONS

• Broader: Isolation

KNOWLEDGE AREAS

Data Storage & Operations Software Engineering Data Management

TARGET AUDIENCE

Software Developer Data Engineer Data Architect MLOps Engineer

EXTERNAL SOURCES

• Wikipedia - Snapshot isolation
• Microsoft - Snapshot isolation definition and usage.
• PostgreSQL - MVCC and snapshot visibility rules.
• Jepsen - Isolation levels and anomalies overview.

TOP QUESTIONS

What does Snapshot Isolation guarantee?

It guarantees a consistent snapshot for reads, eliminating dirty and non-repeatable reads. It often increases concurrency by avoiding long read locks.

Category: Definition

How does Snapshot Isolation relate to MVCC?

MVCC provides multiple versions of rows; snapshot isolation selects versions as of the transaction start, enabling non-blocking reads.

Category: Theory

Does Snapshot Isolation prevent all anomalies?

No. It is vulnerable to write skew, which true serializable isolation prevents. Careful constraints or serializable mode may be needed.

Category: Misunderstanding

When should I use Snapshot Isolation?

Use it for read-heavy OLTP workloads needing high concurrency and predictable reads. Avoid when write skew would violate business invariants.

Category: Requirements

How is Snapshot Isolation implemented?

Typically via MVCC with version chains and transaction timestamps. Vacuuming or garbage collection reclaims old versions.

Category: Implementation

What are common mistakes with Snapshot Isolation?

Assuming it provides serializable guarantees or ignoring write skew in complex invariants are common pitfalls.

Category: Mistakes

What is a hot topic around Snapshot Isolation?

Serializable snapshot isolation and optimistic concurrency schemes that retain performance while strengthening guarantees.

Category: Topic

How does Snapshot Isolation impact performance?

It often improves read performance and reduces contention, but version maintenance and vacuuming introduce overhead.

Category: Performance

What is an alternative to Snapshot Isolation?

Serializable isolation eliminates write skew at higher cost, while Read Committed may offer higher throughput with weaker guarantees.

Category: Alternatives