MVCC
Multiversion concurrency control is how PostgreSQL lets readers and writers work on the same row without waiting for each other. Follow one row of a stock table: an UPDATE writes a new version instead of overwriting, every version carries xmin and xmax stamps, a reader's snapshot and the commit log decide which version it sees (5, then 5, then 4), and VACUUM finally clears the dead version away.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01T2 runs UPDATE stock SET qty = 4 on a row whose qty is 5. What does PostgreSQL do to the page?
PostgreSQL never updates a row in place. The old version stays, marked as replaced by T2 (its xmax), and a new version records T2 as its creator (its xmin). Both sit on the page until VACUUM removes the old one.
02T1's snapshot is 101:101:. Version v2 has xmin 101. Can T1 see v2, even after transaction 101 commits?
xmax 101 in the snapshot means: ids below 101 are finished, 101 and later are not. The snapshot never changes, so in Repeatable Read T1 keeps reading the old version. It sees v2 only with a new snapshot.
03What does COMMIT change in PostgreSQL?
Visibility is decided by reading the commit log (pg_xact), so committing only has to flip one status. A rollback just marks the transaction aborted, and readers ignore its versions: nothing on the page is undone.
04T2 has updated row 1 but not committed. T1 runs SELECT on row 1. What happens?
In MVCC reading never blocks writing and writing never blocks reading. T1's snapshot treats T2 as unfinished, so the old version is still the visible one. Only two writers to the same row take turns.
05After plain VACUUM removes a dead version, what happens to its space?
In the example v1's 32 bytes become free on page 0, and the file stays 8,192 bytes. Plain VACUUM only shrinks a table when pages at its end become empty; VACUUM FULL rewrites it smaller but locks it.
Glossary
- MVCC
- Multiversion concurrency control: the database keeps several versions of a row so that each transaction can read a consistent snapshot without waiting for writers.
- Tuple
- One version of a row as stored on a page, with a header that holds xmin, xmax and ctid.
- xmin
- The id of the transaction that created a row version.
- xmax
- The id of the transaction that deleted or replaced a row version; 0 while nobody has.
- Snapshot
- A note of which transactions count as finished for a reader, written xmin:xmax:running-list (for example 101:101:).
- Commit log
- PostgreSQL's record of each transaction's status (in progress, committed or aborted), kept in pg_xact.
- Dead tuple
- A row version that no running or future snapshot can see; it still takes space until VACUUM removes it.
- VACUUM
- The command (also run automatically by autovacuum) that removes dead tuples and marks their space free for reuse.
Notes and sources
- Real transaction ids are much larger than 100 and 101; the figure keeps only their order and gaps. A transaction that only reads does not even get an id.
- PostgreSQL keeps the commit log in the pg_xact folder, a few bits per transaction id. It also caches the answer in each tuple header (hint bits), so most reads never look it up.
- The visibility rule in the figure is simplified: the real check also handles a transaction seeing its own changes, subtransactions and locked rows.
- Because qty has no index and v2 fits on the same page, this is a heap-only (HOT) update. After VACUUM, slot (0,1) keeps a tiny 4-byte pointer on to (0,2), so the primary key index still finds the row.
- Plain VACUUM never shrinks a table except when whole pages at its end become empty; VACUUM FULL rewrites the table smaller but locks it while it works.
- Two writers to the same row still take turns: a second UPDATE of row 1 waits until T2 commits or rolls back. MVCC removes the waiting between readers and writers only.
- SQLite does not keep row versions. In WAL mode it keeps changed pages in the write-ahead log file, and an open reader keeps reading the older pages, so the Python example shows the same effect.
- PostgreSQL documentation: Concurrency Control, Introduction
- PostgreSQL documentation: Transaction Isolation
- PostgreSQL documentation: System Columns (xmin, xmax, ctid)
- PostgreSQL documentation: Database Page Layout
- PostgreSQL documentation: System Information Functions (snapshot components)
- PostgreSQL documentation: Transactions and Identifiers
- PostgreSQL documentation: Database File Layout (pg_xact)
- PostgreSQL documentation: Routine Vacuuming
- PostgreSQL documentation: Heap-Only Tuples (HOT)
- PostgreSQL documentation: pageinspect
- PostgreSQL source: heapam_visibility.c (HeapTupleSatisfiesMVCC)
- SQLite: Isolation In SQLite
- Python Library Reference: sqlite3
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 18
Quiz Unlocks after the last step.