A database snapshot in an MVCC system is a rule for deciding which row versions a reader is allowed to see. It does not copy the whole database for every query. Instead, the database keeps enough version information to reconstruct a logically consistent view while other transactions continue changing the underlying records.
This is one of the central ideas behind multi-version concurrency control: two transactions can look at the same logical table at the same moment and correctly receive different answers because they began with different visibility boundaries.
This guide focuses on read views and visibility. For the complete mechanism, use How MVCC Works. The companion guides explain row versions and safe reclamation of versions that are no longer visible. For the wider subject, see How Databases Work.
The Direct Answer: A Snapshot Is a Visibility Boundary
Suppose a table contains one equipment record. Transaction A begins reading when the status is available. Transaction B later changes the same logical item to reserved and commits. Should A now see reserved?
The answer depends on A’s isolation semantics. Under one model, each statement receives a fresh snapshot and A’s next query can see B’s committed update. Under another, A keeps the same transaction snapshot and continues seeing the older version for the rest of its transaction. Both behaviours can be correct because the database contract is different.
PostgreSQL’s current SET TRANSACTION documentation describes this directly: under READ COMMITTED, a statement sees rows committed before that statement began; under REPEATABLE READ, the transaction continues using a snapshot established near its first query or data-modification statement. PostgreSQL’s MVCC glossary defines MVCC as keeping tuple versions so multiple transactions can read and write without making ordinary reads wait for ordinary writes.
A Snapshot Is Not a Backup
The word snapshot is overloaded in computing. A storage snapshot can be a recoverable copy or copy-on-write image of a volume. A database MVCC snapshot is primarily a visibility description: it tells the transaction which database changes count as committed for this view and which concurrent changes remain invisible.
PostgreSQL exposes snapshot information through its system information functions. A PostgreSQL snapshot can be represented with boundaries such as xmin and xmax plus a list of transactions that were still in progress. The engine combines that information with tuple metadata when testing visibility.
This is why an MVCC snapshot can be very small compared with the database it describes. It does not need a second physical copy of every visible row. The engine needs a stable rule plus retained row versions sufficient to answer the reads permitted by that rule.
The Teaching Model: Sequence Numbers and Eligible Versions
Use a simplified sequence-number model. Every committed change receives a monotonically increasing sequence. A snapshot at sequence S may see committed changes with sequence numbers no greater than S, subject to the engine’s own transaction rules.
Camera-2 is available at sequence 41. It becomes reserved at 47. It is deleted at 52. A snapshot at 45 sees available. A snapshot at 49 sees reserved. A snapshot at 55 sees absence.
This teaching model is intentionally simpler than any one production database. PostgreSQL uses transaction identifiers and visibility information rather than the exact sequence scheme above. InnoDB uses multi-versioning and read views with undo information. The invariant is the same: readers need a rule that selects the correct historical version rather than simply choosing whichever physical copy was written most recently.
Why File or Page Timestamps Are Not Enough
Physical storage can be rewritten without creating a new logical transaction. A vacuum, compaction, page split, backup restore or file copy can change a file’s modification time while preserving old logical versions. Therefore, operating-system timestamps cannot substitute for transaction visibility metadata.
This matters in the same way it mattered in the LSM cluster: physical movement and logical time are separate. A reader must answer, “Which transaction created or removed this version, and was that transaction visible to my snapshot?” rather than, “Which file looks newest?”
Statement Snapshots and Transaction Snapshots
The most useful first distinction is the lifetime of the snapshot.
- Statement-level snapshot: each statement starts from a fresh view. A later statement in the same transaction can observe commits that happened after an earlier statement finished.
- Transaction-level snapshot: the transaction holds a stable view across multiple statements. Later commits by other transactions remain invisible for that transaction’s ordinary snapshot reads.
PostgreSQL READ COMMITTED behaves primarily like the first model; REPEATABLE READ behaves like the second. MySQL InnoDB’s consistent nonlocking read documentation describes a related distinction: under its REPEATABLE READ default, consistent reads within one transaction normally use the snapshot established by the first such read.
Your Own Writes Are a Special Case
A transaction generally needs to see changes it has already made itself, even when those changes are newer than the external snapshot boundary. Otherwise an application could update a row and immediately fail to observe its own update.
This creates a useful warning for learners: “snapshot at time T” is a metaphor, not a complete implementation specification. The transaction’s own writes can be visible through rules that differ from ordinary visibility of other transactions’ writes. Product documentation should be the authority for these details.
InnoDB’s documentation explicitly notes this self-visibility behaviour for consistent reads. That can produce a table view combining the transaction’s own newest versions with older versions of rows concurrently changed by others. This is not proof that snapshots are broken; it is a consequence of the transaction’s special relationship to its own changes.
A Snapshot Must Distinguish Committed, Active and Aborted Work
Imagine three transactions around the moment a snapshot is created. Transaction 100 has already committed. Transaction 101 is still running. Transaction 102 began later and also remains active. A correct snapshot cannot simply use “transaction ID below 103” as synonymous with visible.
Transaction 101 may eventually commit, but it was still incomplete at the snapshot boundary and should normally remain invisible to that snapshot. An aborted transaction should never become visible merely because its identifier is numerically old.
This is why snapshot representations track more than one cutoff. PostgreSQL’s pg_snapshot representation includes a low bound, a high bound and the transactions that were still in progress. The exact visibility function then combines those snapshot components with tuple creation/deletion metadata.
Worked Example: One Reader, Two Writers
Start with camera-2 = available. Reader R obtains a transaction-level snapshot. Writer W1 changes camera-2 to reserved and commits. Writer W2 inserts tripod-4 and commits.
R performs another ordinary snapshot read. Under a stable transaction snapshot, it can continue seeing camera-2 as available and can continue not seeing tripod-4. A new transaction starting afterward sees camera-2 as reserved and tripod-4 present.
There is no contradiction. R is reading one permitted historical view; the new transaction is reading a later view. Both are derived from the same underlying physical database plus different visibility rules.
Read Committed Is Not “Read Whatever Is There”
READ COMMITTED still creates a coherent statement snapshot. It does not mean a single query should randomly mix arbitrary intermediate changes from transactions that commit while the query is scanning.
PostgreSQL documents that each command sees a snapshot as of the beginning of that command. If another transaction commits halfway through the scan, that newly committed state is not simply spliced into the already-running statement’s ordinary snapshot view.
This property matters for reports and calculations. A query aggregating thousands of rows needs a defined visibility point so totals are not assembled from uncontrolled intermediate moments. Freshness is traded for internal consistency during the statement.
Repeatable Read Is Stronger, but It Is Not the Same as Serial Execution
A stable snapshot prevents many changes in observed data across repeated reads. However, snapshot consistency alone does not guarantee that concurrent transactions collectively behave exactly as if they had executed one after another.
Consider two doctors independently checking whether at least one doctor remains on call. Each transaction sees two doctors currently on call. Doctor A goes off call; Doctor B also goes off call. If the system allows both transactions to commit based on the shared old snapshot, the final state has zero doctors on call even though each transaction’s local reasoning looked valid.
This family of anomaly is often used to explain write skew. It demonstrates why “my reads did not change” and “the concurrent execution is equivalent to some serial order” are different claims.
Snapshot Isolation and Serializable Isolation Are Different Questions
Snapshot isolation asks whether a transaction reads from a stable snapshot while certain write conflicts are prevented. Serializable isolation asks whether the overall effect of concurrent transactions can be explained as some serial ordering.
Different engines implement these levels differently. PostgreSQL’s current transaction documentation describes SERIALIZABLE as detecting patterns that could not occur under any serial execution and aborting a transaction with a serialization failure when necessary. Its implementation uses Serializable Snapshot Isolation rather than converting every read into a blocking lock.
The practical lesson is simple: do not call every stable-snapshot mode “serializable.” Read the actual engine contract and test the application invariants that matter.
Why Locks Still Exist in an MVCC Database
MVCC reduces many read-versus-write conflicts, but concurrent writers can still collide. Schema changes can require stronger coordination. Applications can also explicitly request row or table locks when they need a particular ordering or ownership guarantee.
PostgreSQL’s MVCC documentation explains that table- and row-level locking remains available alongside the multiversion model. MVCC is therefore not “a database with no locks.” It is a concurrency architecture that lets many ordinary reads proceed without blocking ordinary writes.
For debugging, ask exactly which operation is waiting. A SELECT waiting on a schema lock and an UPDATE waiting on another writer are different mechanisms from a plain snapshot read choosing an old visible version.
A Snapshot Defines Visibility, Not Freshness
A long-running transaction can be perfectly consistent and increasingly old. If it began at 09:00 and remains open at 15:00, its snapshot can still answer according to the earlier state while many newer commits exist.
That may be exactly what a report needs. It may also surprise an application that assumes every query automatically sees the newest committed data. The correct design chooses a snapshot lifetime appropriate to the task.
Label the requirement before choosing the isolation level: consistent multi-query report, current account balance, conflict-sensitive reservation, analytical export or interactive dashboard. “More isolation” is not automatically “more correct” without a specific correctness requirement and retry plan.
Exported Snapshots Let Several Transactions Share a View
Some systems let one transaction export a snapshot so another transaction can import the same visibility boundary. PostgreSQL supports this through pg_export_snapshot and SET TRANSACTION SNAPSHOT under documented restrictions.
This is useful when independent workers need to read one consistent logical database state without funnelling all work through one session. The snapshot identifier coordinates visibility; it does not duplicate the underlying tables for each worker.
The lifetime still matters. A snapshot can only be imported while the exporting transaction remains available under PostgreSQL’s rules. Treat snapshot handles as coordination state with a documented lifecycle, not permanent archival identifiers.
Why Long-Lived Snapshots Affect Garbage Collection
If an old snapshot can still read version 41, the engine cannot safely discard version 41 merely because the latest state has advanced to version 80. Snapshot retention therefore becomes a storage-management obligation.
PostgreSQL’s vacuum documentation explains that tuples still potentially visible to an older snapshot cannot be removed yet. A long-running transaction can hold back the cutoff used to decide which dead versions are reclaimable.
This creates a powerful systems connection: a read-only transaction can consume very little CPU while still imposing a cost on future cleanup. Resource usage is not only what a transaction is doing now; it also includes what historical state the database must continue preserving because that transaction still exists.
Snapshots and Replication Are Separate Layers
A local MVCC snapshot describes visibility within one database engine’s transaction model. Replication adds another question: which commits have reached another node, and which consistency guarantee does that replica offer?
A replica can provide a locally consistent snapshot that is behind the primary. That snapshot can be internally coherent and still stale relative to the latest primary commit. Conversely, a primary transaction snapshot can intentionally remain old even though the primary itself has newer committed data.
Do not use the single word snapshot to hide both dimensions. Ask where the read runs, how far replication has progressed, and which transaction visibility boundary the reader has chosen.
Snapshots and “Time Travel” Queries Are Related but Not Identical
Some databases expose historical queries such as AS OF a timestamp. That is a user-facing history feature. Ordinary MVCC snapshots are usually retained only as long as needed for current transactions and engine-specific cleanup rules.
Therefore, the fact that an engine once created row versions does not mean it can answer an arbitrary historical query forever. History becomes queryable only while the required versions and metadata remain available under that product’s retention model.
Similarly, an audit log is not automatically an MVCC history. An audit system can preserve business events long after the engine has reclaimed old tuple versions. The two mechanisms can complement each other without being interchangeable.
The Visibility Decision Can Be Modelled as a Predicate
For a simplified teaching implementation, imagine every row version has created_by and deleted_by transaction identifiers. A snapshot has information describing transactions that were committed, active or not yet started relative to the view.
The visibility function asks whether the creating transaction counts as visible and whether a deleting transaction counts as visible. If creation is invisible, ignore the version. If creation is visible but deletion is not visible, the version may appear. If both creation and deletion are visible, the version is absent for that snapshot.
Production engines optimise this heavily and represent states differently. The model is useful because it separates the logical test from the physical location of the row. That same logical predicate can operate whether the historical information sits in heap tuples, undo records or another version store.
Deletion Is Visibility Metadata Until Reclamation Is Safe
Deleting a row does not necessarily erase all of its bytes immediately. In MVCC, an older snapshot may still be entitled to see the row. The delete therefore marks a change in visibility before physical reclamation completes.
This is the same conceptual split seen in LSM tombstones, though the storage mechanism differs. Logical absence can happen now; physical reclamation can happen later after the system proves that no supported reader needs the older state.
That distinction is essential for debugging storage growth. “We deleted half the rows” is a statement about logical state. It is not yet a measurement of reclaimed disk space.
A Three-Session Laboratory
Create a disposable test table with one row: camera-2 = available. Open three database sessions. In session A, begin a transaction at the engine’s stable-snapshot level. Read the row.
In session B, update camera-2 to reserved and commit. In session C, insert tripod-4 and commit. Return to session A and repeat both reads. Record whether A sees the updated camera and the new tripod.
Repeat at READ COMMITTED. Then repeat while session A modifies camera-2 itself. The goal is not to memorise a vendor-independent answer; it is to observe how snapshot lifetime, self-writes and concurrent commits interact under the exact engine you use.
A Write-Skew Laboratory
Use a two-row table representing two independent resources that jointly satisfy one invariant, such as at least one on-call doctor. Start two transactions from the same stable snapshot. Each verifies that the other row remains on call, then each switches its own row off call.
Observe whether both commits are allowed under the chosen isolation level. Repeat under SERIALIZABLE. If the serializable mode aborts one transaction, the retry requirement is not an implementation annoyance; it is how the database prevents a non-serializable result.
Never run an isolation experiment against a production table. Use a disposable database or dedicated test schema and record the database version and configuration so the observation can be reproduced.
Diagnose Stale Reads by Asking Four Questions
- Where is the read running? Primary, replica, cache or application-side snapshot?
- When was the read view established? At transaction start, first query or each statement?
- Whose writes are involved? This transaction’s own writes or another transaction’s commits?
- Which isolation and transaction mode is active? Do not infer this from framework defaults without checking the connection.
These four questions usually provide more information than asking whether “the database cache is stale.” A stale result can be a correct result for an old snapshot.
Diagnose Blocking Separately From Visibility
If a query is waiting, identify the lock or resource wait. MVCC visibility itself often allows readers to choose an older version rather than waiting for a writer, but explicit locks, schema changes, conflicting writes and implementation-specific operations can still block.
A visibility problem returns an unexpected but immediate version. A blocking problem delays progress. A serialization failure aborts and requests retry. These are three different symptoms and should lead to three different diagnostic paths.
Snapshots Need an Application Retry Story
Stronger isolation can move complexity from inconsistent outcomes into transaction retries. If the database rejects a transaction to preserve serializable behaviour, the application needs to decide whether and how to retry safely.
Retries must consider idempotency and external side effects. Sending an email, charging a card or calling another service inside a transaction cannot be blindly repeated merely because the database transaction restarts. Database concurrency control solves database visibility; application workflow still needs its own side-effect design.
Frequently Asked Questions
Does a snapshot copy every row?
No. In MVCC, the snapshot is primarily visibility metadata. The engine keeps row versions and uses the snapshot to decide which version each query may observe.
Can two correct transactions see different values at the same time?
Yes. They can have different snapshots or isolation semantics. Correctness is evaluated against each transaction’s visibility contract, not against the assumption that every reader must always receive the physically newest committed version.
Does REPEATABLE READ mean serializable?
No. A stable snapshot prevents many read changes, but serializability is a stronger property about the combined effect of concurrent transactions. Engine-specific documentation determines the exact guarantees.
Why can an old read prevent cleanup?
The database may need old row versions to continue answering that snapshot correctly. Cleanup must wait until those versions are no longer visible to any relevant reader under the engine’s rules.
Is an MVCC snapshot a backup?
No. It is a transaction visibility mechanism. A backup addresses recoverability after loss or corruption and needs its own consistency and retention protocol.
Can a snapshot be stale but correct?
Yes. A stable snapshot intentionally preserves an earlier committed view. Whether that is suitable depends on the application’s freshness requirement.
Sources and Scope
The main implementation references are PostgreSQL’s current MVCC introduction, transaction characteristics, snapshot-information functions and transaction-isolation documentation, plus MySQL InnoDB’s consistent nonlocking reads. The numbered camera and doctor examples are teaching models, not measurements of either product.
Final Synthesis: A Snapshot Freezes the Question, Not the Database
Other transactions can continue committing while a reader holds a snapshot. What remains stable is the reader’s rule for interpreting which versions belong to its view. That is the power of MVCC: the physical database can keep changing while a transaction continues reading a coherent logical state.
The cost of that power is historical responsibility. Versions must remain available long enough, cleanup must respect active readers, and applications must understand which isolation guarantee they actually requested. Once those boundaries are explicit, snapshots stop looking like magic copies and become a precise visibility mechanism.