MVCC without the jargon
See how row versions let ordinary reads see a consistent view, why the snapshot changes with isolation level, and when old versions cost space.
MVCC keeps an older row version available while a query might still need it. In PostgreSQL, an ordinary SELECT at Read Committed gets a new snapshot for each command; at Repeatable Read, later commands keep the snapshot from the first statement. Locks and cleanup still matter.
Imagine an editor posting a revised notice without tearing down the previous one. One reader who began reading before the revision can finish with the old notice. A later reading may use the new notice or the old one, depending on the reading rule; opening a session alone does not freeze the board.
Two transactions read the same account while another transaction changes it. Can both reads be correct if they return different balances? In PostgreSQL, multiversion concurrency control (MVCC) lets an ordinary SELECT choose a row version visible to its snapshot. The useful question is when that snapshot was taken. It depends on the isolation level, not simply on when the transaction began.
Two versions of the same row#
Start with a simplified PostgreSQL example: an account row has balance 100. Session B updates it to 120 and commits. PostgreSQL’s update leaves an older tuple version until it can be reclaimed. A query whose snapshot predates B’s commit can still see 100; a later snapshot can see 120. Neither query had to copy the whole table.
That is why an ordinary reader and a writer usually do not need conflicting row locks. It does not remove locking from the database. Two writers targeting the same row can wait on each other. A locking read such as SELECT … FOR UPDATE deliberately waits for a conflicting row lock; a table-level ACCESS EXCLUSIVE lock can block even an ordinary SELECT. PostgreSQL documents these distinctions in its MVCC introduction and lock modes.
What a snapshot actually is#
A snapshot is visibility information, not a frozen copy of every row. PostgreSQL uses it to decide which transactions’ changes an ordinary query may see. At its default Read Committed level, each ordinary SELECT takes a snapshot when that command starts. A second SELECT in the same transaction can therefore see B’s newly committed 120. At Repeatable Read, the snapshot is established at the first non-transaction-control statement, not at BEGIN; the second read still sees 100. Both levels let a transaction see its own earlier writes. These are PostgreSQL’s documented transaction isolation rules.
Try it: when does the second read change?
Illustrative PostgreSQL 18 example. Session A begins a transaction and reads an account balance of 100. Session B then updates it to 120 and commits. Session A makes a second ordinary SELECT.
- Session A first read: 100. This SELECT uses a snapshot taken when the statement starts.
- Session B commits its update: 120.
- Session A second read: 120.
Read Committed: the second SELECT gets a new snapshot after B commits, so it sees 120.
This models two ordinary reads of one row and no writes by A. Locking reads, concurrent updates, DDL and retries have other rules. It does not run a database. Check PostgreSQL's isolation rules.
This is one visibility example, not a promise that every query in every engine behaves alike. Updates and locking reads have additional conflict rules. For anomalies and choosing a level for a business rule, continue with isolation levels.
A snapshot held open too long#
Old versions are not free. An update creates a new row version and records the change; cleanup is another cost. In PostgreSQL, VACUUM reclaims dead tuples once no relevant transaction can still need them. A long-running transaction can hold back that cleanup and let dead tuples and storage pressure accumulate. The exact effect depends on which snapshots are active and which rows were changed; an open transaction alone is not proof that every old version throughout the database must be kept.
If dead tuples accumulate, inspect transaction age and backend_xmin in pg_stat_activity, then check the affected tables and vacuum activity. A short Read Committed query and a long Repeatable Read transaction do not keep the same snapshot for the same duration. PostgreSQL’s routine vacuuming guide explains both reclamation and the effect of old transaction IDs. This is a diagnosis to verify, not a claim that every long read slows every write.
The storage layout is engine-specific: InnoDB reconstructs earlier versions using undo logs. For the separate question of why a query planner may prefer a table scan to an index, see why your index is not being used. That choice is based on estimated work and query shape; the planner does not simply “stop trusting” an index. The persistence path places row storage and transactions in a broader sequence.
IF YOU REMEMBER ONE THING
MVCC lets an ordinary query read a visible row version while another session writes. In PostgreSQL, Read Committed renews that view per command; Repeatable Read keeps it from the first statement. Locks still handle conflicting operations, and old versions need cleanup.
Questions people also ask
5 QUESTIONSDoes MVCC mean writers never block anyone?
No. In PostgreSQL, an ordinary SELECT usually reads a visible version without taking a row lock that conflicts with a writer. Two writers of the same row can still wait, SELECT FOR UPDATE deliberately locks rows, and an ACCESS EXCLUSIVE table lock can block even an ordinary SELECT.
What exactly is a snapshot?
It is visibility information used to decide which row versions a statement may see, not a copy of the entire database. In PostgreSQL Read Committed, each ordinary SELECT gets a snapshot when that command begins. In Repeatable Read, the snapshot is established by the transaction's first non-transaction-control statement. A transaction also sees its own earlier writes.
Why keep old row versions?
An existing snapshot may still need a version superseded by a later write. PostgreSQL VACUUM removes dead tuples only when they are no longer needed and makes space reusable. A long-lived transaction can delay cleanup, but not every old version everywhere is retained by every open transaction. Writes also have immediate version and logging costs.
Is MVCC the same as optimistic locking?
No. MVCC determines which row version a read sees. An application can separately use an optimistic version check to reject a write if the row changed since it read it. MVCC does not make conflicting writes automatically safe.
Do all databases implement MVCC the same way?
No. PostgreSQL keeps updated row versions in its table and reclaims dead tuples with VACUUM. InnoDB uses undo logs to reconstruct older versions for consistent reads and purges undo records when they are no longer needed. Snapshot timing and locking rules also vary by engine and isolation level.