MVCC

Multi-Version Concurrency Control (MVCC) is a concurrency mechanism in which writers do not block readers and readers do not block writers. Instead of locking rows for the duration of a transaction, the database keeps multiple versions of each row, and each transaction sees a consistent snapshot of the data as of its start time.

How it works

Every row carries internal metadata about which transaction created it and (if applicable) which transaction deleted or updated it. A transaction sees a row only if it was created by a committed transaction visible at the transaction's snapshot, and not yet deleted by another visible transaction. Updates create new versions rather than modifying in place; deletes mark the row as ended at the deleting transaction.

Implications

• High read concurrency. Reads never wait on writes and vice versa.
• Garbage collection. Dead row versions accumulate and must be reclaimed by a background process (PostgreSQL's VACUUM, MySQL InnoDB's purge thread).
• Long transactions are expensive. They prevent garbage collection of any row version they might read, bloating the database.
• Predictable read paths. Snapshot isolation makes reasoning about query results within a transaction easier.

Where MVCC is used

• PostgreSQL (the classic example), Oracle, MySQL InnoDB, SQL Server (snapshot isolation), CockroachDB, Spanner, FoundationDB