Snapshot Isolation
Subject: Computer and Technology noun /ˈsnæpˌʃɑːt ˌaɪ.səˈleɪ.ʃən/ 1 definition
- database-systems
- concurrency-control
- data-integrity
- distributed-systems
-
Definition (noun)
Entry #309 · UpdatedSnapshot Isolation is a database concurrency control guarantee that presents each transaction with a consistent, point-in-time view of data committed as of the transaction's snapshot timestamp—typically acquired at transaction start or first read. Commonly implemented via multi-version concurrency control (MVCC), the storage engine preserves older record versions so queries reconstruct historical state without taking read locks, ensuring readers never block writers and writers never block readers. To preserve write integrity, concurrent transactions that update the same record trigger a write-write conflict check under a first-committer-wins or first-updater-wins rule, aborting the losing transaction to prevent lost updates. Its core purpose is to combine high read concurrency with freedom from dirty reads, non-repeatable reads, and phantom reads, though standard snapshot isolation stops short of full serializability because concurrent updates to disjoint records based on overlapping reads can still cause write skew.
Common Forms
First-Committer-Wins Snapshot Isolation: An optimistic implementation that lets concurrent transactions execute writes without blocking and validates write sets at commit time, aborting any transaction whose modified records overlap with updates committed after its snapshot began.
First-Updater-Wins Snapshot Isolation: A lock-assisted variant—used by PostgreSQL at
REPEATABLE READ—where a transaction acquires a row lock before modifying a record, waiting for any active concurrent updater and aborting if that earlier updater commits.Serializable Snapshot Isolation (SSI): A strengthened form that augments standard snapshot isolation with lightweight predicate-level read tracking (
SIREADlocks) to detect dangerous read-write dependency cycles and abort transactions before write skew anomalies can occur.Distributed Snapshot Isolation: A multi-node variant used by horizontally partitioned databases—such as Google Spanner—that leverages synchronized physical clocks or Hybrid Logical Clocks (HLCs) to assign globally ordered timestamps so transactions observe a consistent snapshot across shards.
Example
In PostgreSQL, a nightly billing audit begins a transaction with
BEGIN ISOLATION LEVEL REPEATABLE READto sum balances across anaccountstable while concurrentREPEATABLE READtransfer jobs update rowid = 42(balance = balance - 100). Under snapshot isolation, the audit query reads consistent historical MVCC row versions as of its initial snapshot without blocking or being blocked by writes, while any two concurrent transfer transactions that attempt to modify rowid = 42trigger a first-updater-wins conflict that aborts the second transaction with SQLSTATE40001.