I. Introduction
MVCC (Multi-Version Concurrency Control) is a technique used in databases to manage concurrent access to the database. MVCC lets readers and writers work at the same time without blocking each other: a query never waits for an update to finish, and an update never waits for a query. Two transactions that write the same row still have to take turns.
In this article, we will explore how MVCC works in databases like PostgreSQL and MySQL and its benefits for developers and users.
II. How MVCC Works in Databases
MVCC works by creating multiple versions of a row in the database to represent different states of the data at different points in time. When a transaction updates a row, the database creates a new version of the row with the updated data, while keeping the old version intact. This allows other transactions to continue reading the old version of the row without being affected by the update.
Each transaction reads from a snapshot: the set of row versions that were committed when the snapshot was taken. Versions created later, or by transactions that have not committed, are simply invisible to it. That is why a reader needs no lock on the rows it reads.
An old version is not removed when the updating transaction commits. It stays until no running transaction’s snapshot can still see it, and only then is it cleaned up. Engines do this differently:
- PostgreSQL writes the new version into the table itself and leaves the old one in place as a dead tuple.
VACUUM(usually autovacuum) reclaims the space later. - MySQL InnoDB updates the row in place and keeps the previous version in the undo log. A reader that needs the older version rebuilds it from the undo log, and a background purge thread discards undo records nobody needs any more.
Writers still use locks. An UPDATE or DELETE locks the rows it changes until the transaction ends, so a second transaction that wants to change the same row waits for the first one to commit or roll back. Plain SELECTs take no row locks; SELECT ... FOR UPDATE / FOR SHARE do, when you need them.
III. Alternative approaches
MVCC is the default in PostgreSQL, MySQL InnoDB and Oracle. Other ways to control concurrent access:
Lock-based concurrency (two-phase locking, 2PL): readers take shared locks and writers take exclusive locks, so a reader waits for a writer on the same row and the other way round. Under strict 2PL a transaction acquires locks as it goes (growing phase) and releases them only at commit or rollback (shrinking phase). It is simple to reason about, but long transactions block others and deadlocks are more common. Example: Microsoft SQL Server’s default
READ COMMITTEDis lock-based unlessREAD_COMMITTED_SNAPSHOTis turned on, which switches it to row versioning.Optimistic concurrency control (OCC): transactions run without locks, recording what they read and write, and are validated at commit; if something they read has changed in the meantime, the transaction is aborted and retried. It works well when conflicts are rare and poorly under heavy contention. Example: FoundationDB, which combines MVCC reads with commit-time conflict checks.
Timestamp ordering: every transaction gets a timestamp, and an operation that would break timestamp order aborts its transaction instead of waiting. It avoids deadlocks but causes more aborts; it appears mostly in research systems and as a building block inside distributed databases.
These are not mutually exclusive: real engines mix them, for example MVCC for reads plus row locks for writes, as described above.
IV. See it in PostgreSQL
Two psql sessions make the idea concrete. Create a table and one row:
CREATE TABLE account (id int PRIMARY KEY, balance int);
INSERT INTO account VALUES (1, 100);
Readers do not block writers. In session A, open a transaction and read:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE id = 1; -- 100
In session B, update and commit while A is still open:
UPDATE account SET balance = 150 WHERE id = 1; -- returns immediately
Back in session A, the same SELECT still returns 100: A keeps seeing the snapshot it took. Under the default READ COMMITTED, A would see 150 on its next statement instead, because each statement takes a fresh snapshot.
Each row version carries its own transaction ids. PostgreSQL stores xmin (the transaction that created the version) and xmax (the one that deleted or replaced it), plus ctid, the version’s physical location:
SELECT xmin, xmax, ctid, balance FROM account;
Run it before and after the update: xmin and ctid change, because the UPDATE wrote a new version somewhere else in the table. A query only ever returns the version its snapshot can see, but the old one is still in the table file. It becomes a dead tuple once no open transaction can see it any more (the pageinspect extension can show both versions on the page).
Old versions need cleanup. VACUUM reclaims dead tuples; autovacuum does this in the background. A transaction left open for hours holds the oldest snapshot, so nothing newer than it can be cleaned, and tables bloat. Look for such sessions with:
SELECT pid, now() - xact_start AS open_for, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;
This is the practical cost of MVCC: reads and writes stop blocking each other, and in exchange you must keep transactions short and let vacuum keep up.
V. Conclusion
MVCC is how PostgreSQL, MySQL InnoDB and Oracle let many transactions work at once. Each transaction reads a consistent snapshot of row versions, so readers and writers do not block each other; writers still lock the rows they change, so concurrent updates to the same row are serialized. The price is old row versions that must be cleaned up — dead tuples and VACUUM in PostgreSQL, undo log and purge in InnoDB — which is why long-running transactions hurt. Lock-based 2PL, optimistic concurrency control and timestamp ordering solve the same problem with different trade-offs, and real engines often combine them.
Public comments are closed, but I love hearing from readers. Feel free to contact me with your thoughts.