12.1 PostgreSQL MVCC, Snapshots, and Isolation Levels
Metadata Card
- Prerequisites: Chapter 11, Transactions and Serializable Isolation
- Keywords: MVCC, snapshots, visibility, Read Committed, Repeatable Read, SSI, VACUUM
- Language: SQL (PostgreSQL)
MVCC lets ordinary reads select suitable row versions, reducing waits between readers and writers. Concurrent writes to one row, DDL, foreign-key checks, and explicit locking reads still require locks or conflict detection. Use PostgreSQL 16+ and the account table from 11.1. Restore the two balances to 500.00 and 200.00 before each experiment and end any other practice transactions.
The SQL Standard Specifies Minimum Guarantees
Modern SQL standards go beyond dirty reads, non-repeatable reads, and phantom reads by introducing serialization anomaly to describe results that cannot be mapped to any serial execution order.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Possible |
| Read Committed | Forbidden | Possible | Possible | Possible |
| Repeatable Read | Forbidden | Forbidden | Standardly allowed | Possible |
| Serializable | Forbidden | Forbidden | Forbidden | Forbidden |
This table represents the minimum guarantees defined by the standard, not a complete specification of actual behavior across all products. PostgreSQL accepts all four isolation levels by name, but it implements Read Uncommitted as if it were Read Committed. Its Repeatable Read does not allow phantom reads, yet it can still encounter serialization anomalies such as write skew.
How PostgreSQL Stores Row Versions
PostgreSQL's heap tuples include transaction identifiers that created them (xmin) and transaction identifiers that deleted or replaced them (xmax), among other header information. UPDATE is typically not simply overwriting the entire old content in place; instead, it creates a new tuple version and allows the old version to record that it has been replaced.
Old version: xmin=80, xmax=105, balance=500
New version: xmin=105, xmax=0, balance=400This diagram is a conceptual model. Whether a particular version is visible depends on the reader's current transaction, the transaction's commit status, the set of active transactions in the snapshot, and various flags. Comparing two integers is insufficient. xmax can also identify a locking transaction or MultiXact, so a nonzero value does not prove deletion; tuple flags and transaction status matter.
A snapshot can be abstracted into three types of boundaries:
- Transactions that were active at the time the snapshot was taken;
- Transactions that ended earlier and have already completed;
- Transactions that received transaction IDs after the snapshot was taken.
Readers use these boundaries to exclude uncommitted or "future-origin" versions, and retain only those older versions that remain valid within the current snapshot.
Read Committed: A New Snapshot Per Statement
PostgreSQL defaults to Read Committed. In this mode, a typical SELECT acquires a snapshot at the start of a statement and remains consistent throughout its execution, with no sudden visibility of rows committed later during the same statement. The next statement acquires a fresh snapshot, so it might observe newly committed data.
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM account WHERE account_id = 1;
-- Concurrent transactions committed modifications
SELECT balance FROM account WHERE account_id = 1;
-- The second statement shows the new value
COMMIT;Update statements add another layer of semantics: if an UPDATE, DELETE, or SELECT ... FOR UPDATE finds a target row that has been modified by a concurrent transaction, it will wait for that transaction to complete. After the concurrent transaction commits, PostgreSQL may re-evaluate the WHERE condition on a newer version of the data. This makes single-row atomic updates work reliably, but it also means that complex conditional updates cannot be naively interpreted as simply "the entire statement sees a static, unchanging database snapshot."
Repeatable Read: Establishing a Transaction Snapshot with the First Non-Transaction Statement
In PostgreSQL, a Repeatable Read snapshot is typically established at the moment the first non-transaction-control statement is executed within a transaction, not at the instant when BEGIN is processed.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE account_id = 1;
-- Establish and use transaction snapshot here
SELECT balance FROM account WHERE account_id = 1;
-- Can still see your own modifications within this transaction using the same snapshot
COMMIT;If a transaction attempts to modify a row that has been updated by another transaction and committed after the snapshot was created, PostgreSQL may return:
ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001The application must roll back and retry the transaction from its beginning. While Repeatable Read provides a stable snapshot, it does not guarantee that all successful transactions are equivalent to a serial execution order.
Write Skew: Stable Snapshots Can Still Break Collection Constraints
Suppose the shift schedule requires at least one person to be online:
CREATE TABLE guard (
guard_id bigint PRIMARY KEY,
on_duty boolean NOT NULL
);
INSERT INTO guard (guard_id, on_duty)
VALUES (1, true), (2, true);To construct the dependency, have A and B both BEGIN REPEATABLE READ and run SELECT count(*) FROM guard WHERE on_duty, each seeing 2. A sets guard_id=1 to false and commits. B then sets guard_id=2 to false and commits. A fresh transaction should count 0. Reset both rows to true and repeat this interleaving at Serializable: at least one attempt should receive 40001 instead of both committing.
Both Repeatable Read transactions see two people online and each simply marks themselves as offline. They write to different rows and do not directly conflict with one another, so no write-write conflict occurs. Both may commit, resulting in zero people being online. This is write skew.
A single row CHECK cannot express the constraint "at least one row in the entire table satisfies the condition." Possible alternatives include:
- Using Serializable and correctly retrying
40001; - Concentrating the constraint into a single row that can be locked;
- Explicitly locking a stable object that represents the business collection;
- Redesigning the model so that invariants can be expressed via unique constraints or foreign keys.
Serializable: SSI Detection of Hazardous Dependencies
PostgreSQL's Serializable isolation level extends the snapshot semantics of Repeatable Read with Serializable Snapshot Isolation (SSI). It tracks read-write dependencies and terminates one transaction if it detects a potential non-serializable outcome.
SIReadLock is a marker used to detect predicate-level read-write dependencies, not a conventional blocking lock. It can appear at the tuple, page, or relation granularity, and the granularity can be adjusted to optimize memory usage. Do not describe it as "locking every read and blocking writes."
The key semantic guarantees of Serializable are:
- When all transactions maintaining the invariant use the relevant Serializable protocol, the committed set produces a serializable outcome;
- A transaction attempt may fail with
40001; - The entire transaction must be retried;
SERIALIZABLE READ ONLY DEFERRABLEallows for waiting on a safe snapshot, which is suitable for certain long-running read-only reports.
Why Old Versions Need to Be Cleaned
As long as any snapshot might still see an old tuple, the database cannot reclaim it. Only after a transaction completes and its version becomes invisible to all relevant snapshots can VACUUM mark the space as reusable. Regular VACUUM generally retains reusable space inside the file; empty pages at the physical end may be truncated and returned to the operating system.
Long transactions that retain old snapshots, including some idle-in-transaction sessions, can delay cleanup and contribute to dead tuples, bloat, and delayed freezing. An idle transaction does not always hold one unchanged snapshot. Check the actual retention boundaries before tuning autovacuum.
Hands-on Observation
Use two separate connections. Session B must have autocommit enabled, with no enclosing uncommitted transaction. Send each section below to the connection named in its comment:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE account_id = 1;
-- Session B
UPDATE account SET balance = balance + 10 WHERE account_id = 1;
-- Return to session A
SELECT balance FROM account WHERE account_id = 1;
COMMIT;Starting at 500, A reads 500 twice at Repeatable Read, then sees 510 in a fresh query after commit. Reset and repeat at Read Committed: A should read 500 and then 510. To observe a conflict, run another round and issue UPDATE in A before its COMMIT. At Repeatable Read, A should receive 40001 if B has committed its update. If B is still open, A first waits; B's commit or rollback determines whether A must fail.
References
The next section shifts to InnoDB. It also uses MVCC, but stores older versions primarily in undo records, and the observation points for regular snapshot reads and locked reads differ.