Multiversion concurrency control
Multiversion concurrency control coordinates concurrent database access by retaining data versions and selecting which version a read may observe under the transaction protocol.
Mechanism: Writes create versions rather than immediately making every earlier value unavailable. Visibility and conflict rules determine which versions a transaction can read and when it can commit. Multiversion algorithms can use timestamps, locks or combinations of both. [1]
Isolation: MVCC is not an isolation level. In PostgreSQL 18 Read Committed, each ordinary SELECT takes a fresh statement snapshot and sees its own earlier writes. Repeatable Read uses the snapshot established by its first non-transaction-control statement and also sees its own earlier writes. That Repeatable Read mode uses snapshot isolation and can permit serialization anomalies; Serializable adds dependency checks. These are engine-specific rules. [2]
Retention: Old row versions consume space while transactions can still need them. PostgreSQL VACUUM reclaims eligible dead-version space for reuse; ordinary VACUUM generally does not shrink the file. Version retention for concurrency does not promise an archive or backup. [3]
Persistence: Visibility and durable acknowledgement are separate contracts. PostgreSQL asynchronous commit can return success before the transaction's WAL reaches persistent storage, so recent acknowledged changes may be lost after a crash. [4]
Relations
- related
- Snapshot isolation, Serializable Snapshot Isolation, Read Committed isolation, Repeatable Read isolation
Limitations
- Multiversion does not require unlimited history: section 5 describes particular algorithms using at most two versions per item, not PostgreSQL's version-retention policy. [1]
- MVCC does not remove every lock: ordinary PostgreSQL SELECT takes ACCESS SHARE, which conflicts with ACCESS EXCLUSIVE, and competing writers can wait. [5]
Citations
- Bernstein and Goodman. Multiversion Concurrency Control: Theory and Algorithms. ACM TODS 8(4), 465-483 (1983), sections 1 and 4-6.
- PostgreSQL 18: Transaction Isolation, sections 13.2.1-13.2.3.
- PostgreSQL 18: Routine Vacuuming, section 24.1.2, Recovering Disk Space.
- PostgreSQL 18: Asynchronous Commit, section 28.4.
- PostgreSQL 18: Explicit Locking, sections 13.3.1-13.3.2.
Further reading
Sources & caveats
- Primary-source synthesis, not a new engine execution, crash experiment or performance comparison.
- The 1983 paper analyzes serializable algorithms and earlier work; its date is not an invention claim. PostgreSQL 18 behavior is not a universal MVCC implementation contract.