What Is MVCC?

By Oleksandr Andrushchenko — Published on
Category: Databases & Data
0 Likes
0 Dislikes

MVCC, or Multi-Version Concurrency Control, is a database concurrency technique that keeps multiple versions of data so transactions can read a consistent view while other transactions modify the same data.

MVCC
MVCC

Instead of forcing every reader to wait for every writer, MVCC lets readers work with row versions that are visible to their transaction. This improves concurrency, but it also introduces version cleanup, snapshot management, transaction-isolation rules, and important problems caused by long-running transactions.

Table of Contents

Why MVCC Exists

Databases need to support many transactions accessing the same data concurrently.

Consider an account row:

accounts

id:      42
balance: 1000

Transaction A reads the account while Transaction B updates it:

Transaction A             Transaction B

SELECT balance
                           UPDATE balance
                           1000 → 900

A simple concurrency strategy could lock the row whenever it is accessed:

Writer locks row
       ↓
Reader waits
       ↓
Writer commits
       ↓
Reader continues

This protects consistency but reduces concurrency when reads and writes constantly block each other.

MVCC takes a different approach.

Old Version: balance = 1000
New Version: balance = 900

Transaction A → sees old version
Transaction B → creates new version

The reader can continue using the version appropriate for its snapshot while the writer creates a newer version.

The database therefore avoids many reader-writer conflicts without allowing transactions to observe arbitrary intermediate state.

How MVCC Works

The central idea is that a logical row can have multiple physical or logical versions over time.

MVCC Example
MVCC Example

Another example, suppose the original row is:

Version 1

account_id = 42
balance    = 1000

A transaction updates the balance:

UPDATE accounts
SET balance = 900
WHERE account_id = 42;

Under an MVCC design, the database can preserve the old version while creating a new one:

Version 1
balance = 1000
visible to older snapshot

Version 2
balance = 900
visible after update commits

Different transactions can therefore temporarily observe different versions of the same logical row.

This does not mean the database randomly returns old data. Visibility is determined by transaction metadata, snapshots, isolation rules, and commit state.

Row Versions

A useful simplified model is to associate row versions with the transactions that created or invalidated them.

Version A
value = 1000
created by transaction 100

Version B
value = 900
created by transaction 120

A transaction whose snapshot predates transaction 120 may continue seeing Version A.

A later transaction can see Version B after transaction 120 commits.

Time ─────────────────────────────→

Version A created
      │
      │ Transaction X snapshot
      │      ↓
      │   sees A
      │
      ├──────── Version B created + committed
      │
      │ Transaction Y snapshot
      │      ↓
      │   sees B

MVCC therefore separates two ideas that can otherwise be easy to confuse:

  • the newest physical version that exists;
  • the version visible to a particular transaction.

Transaction Snapshots

A snapshot describes which transactional changes should be visible to a database operation.

Suppose three transactions exist:

T100 → committed
T101 → still running
T102 → committed

A snapshot does not simply mean "read the newest bytes currently stored."

It provides enough information for the database to determine which row versions belong to transactions visible from that point in the transaction history.

Consider:

Row Version A
created by T100

Row Version B
created by T101

Row Version C
created by T102

If T101 has not committed, other transactions generally should not observe its changes even if Version B physically exists.

This is one of the mechanisms that prevents dirty reads in MVCC databases that provide that guarantee.

Visibility Rules

When a query encounters a row version, the database determines whether that version is visible to the current snapshot.

A simplified decision process looks like:

Find row version
      ↓
Who created it?
      ↓
Did that transaction commit?
      ↓
Is it visible to this snapshot?
      ↓
Has a newer visible version replaced it?
      ↓
Return or ignore version

Real database implementations contain more details and optimizations, but visibility checks are fundamental to MVCC.

This explains why two concurrent transactions can execute:

SELECT balance
FROM accounts
WHERE id = 42;

and receive different values without either query reading corrupted data.

Updates and Deletes Under MVCC

An UPDATE can be thought of as creating a new row version and making the previous version obsolete for future snapshots.

Before UPDATE

Version 1
balance = 1000


After UPDATE

Version 1
balance = 1000
old version

Version 2
balance = 900
new version

A DELETE can similarly mark a row version as no longer visible to later transactions while older snapshots may still need to see it.

Before DELETE

Version 1 → visible


After DELETE commits

Old snapshot → Version 1 visible
New snapshot → row not visible

The old physical data therefore cannot always be removed immediately.

The database must first determine that no transaction can still require the old version.

Readers vs Writers

One of MVCC's biggest benefits is reducing reader-writer blocking.

Consider:

Transaction A
SELECT account 42

Transaction B
UPDATE account 42

With MVCC, Transaction A can often continue reading its visible version while Transaction B modifies the row.

                Row 42

Reader A ─────→ Version 1

Writer B ─────→ creates Version 2

The reader does not need to inspect an uncommitted half-finished value, and the writer does not necessarily need to wait for ordinary readers to finish.

This is particularly valuable for read-heavy transactional systems where reads and writes happen continuously.

MVCC does not mean that every read is non-blocking. Explicit locks, schema operations, isolation behavior, and database-specific implementation details can still cause waits.

Writers vs Writers

MVCC reduces reader-writer conflicts, but two transactions trying to modify the same logical row still require coordination.

Consider:

Initial balance = 1000

Transaction A:
UPDATE balance → 900

Transaction B:
UPDATE balance → 800

The database cannot simply let both transactions independently replace the same current version without resolving the conflict.

Depending on the operation, database, and isolation level, one writer may:

  • wait for another transaction;
  • continue after re-evaluating the row;
  • receive a serialization error;
  • fail because of a deadlock or conflict.

MVCC therefore does not eliminate write locking or write conflicts.

When an application requires explicit coordination, database locks can still be appropriate. Database Locks and Transactions covers that side of concurrency control.

MVCC and Isolation Levels

MVCC provides the machinery for keeping and selecting row versions, while the transaction isolation level influences which committed changes a transaction is allowed to observe.

The exact behavior differs between database engines, so isolation semantics should always be checked for the database in use.

Read Committed

In PostgreSQL's Read Committed isolation level, each statement sees a snapshot of data committed before that statement begins.

For example:

Transaction A                    Transaction B

SELECT balance
→ 1000

                                 UPDATE → 900
                                 COMMIT

SELECT balance
→ 900

The two SELECT statements belong to the same transaction but can observe different committed database states.

Repeatable Read

Repeatable Read provides a more stable transaction-level view in PostgreSQL.

Transaction A                    Transaction B

SELECT balance
→ 1000

                                 UPDATE → 900
                                 COMMIT

SELECT balance
→ 1000

Transaction A continues operating from its established snapshot rather than automatically seeing Transaction B's later commit.

This behavior is possible because the older row version remains available.

Serializable

Serializable isolation aims to make concurrent transactions behave as though they had executed in some valid serial order.

MVCC alone does not automatically guarantee this property.

Database engines may need additional conflict detection, locking, predicate tracking, or serialization mechanisms. Some transactions can therefore fail and need to be retried under Serializable isolation.

Transaction boundaries and isolation levels are broader topics covered in Database Transactions.

MVCC in PostgreSQL

PostgreSQL is a useful concrete example because MVCC is central to its concurrency model.

At a simplified level, PostgreSQL stores metadata with tuples that helps determine their visibility.

Two important concepts are commonly represented by transaction IDs associated with tuple creation and deletion or replacement.

Tuple Version

xmin → transaction that created version
xmax → transaction that deleted/replaced version

Suppose:

Version 1
balance = 1000
xmin = 100
xmax = 120

Version 2
balance = 900
xmin = 120
xmax = empty

Transaction 120 created Version 2 and made Version 1 obsolete for snapshots where that transaction is visible.

An older transaction may still see Version 1.

A later transaction may see Version 2.

This explains an initially surprising PostgreSQL behavior: updating a row can create a new tuple version rather than simply modifying the original tuple in place.

The real implementation includes additional tuple metadata, transaction status information, optimizations, and special cases, but this model is enough to understand the operational consequences.

VACUUM and Version Cleanup

Old row versions consume storage even after they are no longer visible to current transactions.

Consider a frequently updated row:

Version 1 → obsolete
Version 2 → obsolete
Version 3 → obsolete
Version 4 → obsolete
Version 5 → current

Once no active transaction can require Versions 1–4, they become reclaimable.

In PostgreSQL, VACUUM is a key part of this cleanup process.

Old tuple versions
        ↓
No active snapshot needs them
        ↓
VACUUM
        ↓
Space becomes reusable

PostgreSQL normally uses autovacuum to perform this maintenance continuously.

MVCC therefore creates an important operational relationship:

More UPDATE / DELETE activity
           ↓
More obsolete tuple versions
           ↓
More cleanup work
           ↓
VACUUM capacity matters

If cleanup cannot keep up, tables and indexes can become larger than necessary and performance may degrade.

Long-Running Transactions

Long-running transactions are particularly important in MVCC systems because an old snapshot may require old row versions to remain available.

Consider:

10:00 Transaction A begins
      Snapshot established

10:01 rows updated
10:02 rows updated
10:03 rows deleted
10:04 rows updated
...
14:00 Transaction A still open

If Transaction A can still see versions from 10:00, the database may be unable to reclaim some obsolete versions created after that snapshot.

The consequences can include:

  • dead tuple accumulation;
  • table bloat;
  • index bloat;
  • more I/O;
  • slower scans;
  • greater vacuum pressure;
  • transaction ID maintenance problems in PostgreSQL.

An especially dangerous case is an application that begins a transaction and then performs unrelated work:

BEGIN
  ↓
SELECT
  ↓
Call external API
  ↓
Wait
  ↓
More application work
  ↓
COMMIT

The transaction may hold a database snapshot much longer than necessary.

Short transaction boundaries are therefore important not only for locks but also for MVCC cleanup and database health.

MVCC and Indexes

Indexes must work together with MVCC visibility rules.

An index may identify a tuple that matches an indexed key, but the database may still need to determine whether that tuple version is visible to the current transaction.

Index lookup
    ↓
Candidate tuple
    ↓
MVCC visibility check
    ↓
Visible?
 ┌──┴──┐
Yes    No
 ↓      ↓
Use   Ignore

Frequent updates can also affect index maintenance because new row versions may require new index entries depending on what changed and how the database implements the update.

PostgreSQL includes optimizations for some updates, but index design still influences the cost of write-heavy workloads.

This is one reason unnecessary indexes can be expensive on frequently updated tables. Index design and its read/write trade-offs are covered in Designing High-Performance Database Schemas.

Production Design Example

Consider an order-processing system using PostgreSQL.

The orders table receives:

Reads:    25,000/sec
Updates:   4,000/sec
Inserts:   2,000/sec

Orders change state frequently:

pending
   ↓
authorized
   ↓
processing
   ↓
shipped
   ↓
completed

A customer request reads an order:

SELECT id, status, total_cents, updated_at
FROM orders
WHERE id = $1;

At the same time, a worker updates its state:

UPDATE orders
SET status = 'processing',
    updated_at = NOW()
WHERE id = $1;

MVCC allows ordinary readers to continue using an appropriate committed row version while the update is occurring.

Customer Request
      ↓
Snapshot
      ↓
Committed Version A
status = authorized


Worker
      ↓
UPDATE
      ↓
Version B
status = processing

After the worker commits, later snapshots can see Version B.

The system initially performs well, but several months later database storage begins growing faster than expected.

Monitoring shows:

Live tuples:          stable growth
Dead tuples:          rapidly increasing
Autovacuum duration:  increasing
Oldest transaction:   3 hours
Table size:           increasing unusually fast

The application is inspected and one reporting job is found to do this:

BEGIN
  ↓
Run first query
  ↓
Process millions of records for hours
  ↓
Run another query
  ↓
COMMIT

The transaction keeps an old snapshot alive for hours while the production system performs thousands of updates per second.

The reporting workflow is redesigned to use shorter transactions:

Read batch
   ↓
Commit
   ↓
Process batch
   ↓
Read next batch
   ↓
Commit

After the oldest snapshot is released, vacuum can reclaim versions that are no longer needed.

The production team monitors:

  • transaction duration;
  • idle transactions;
  • oldest active transaction;
  • dead tuple count;
  • autovacuum activity;
  • vacuum duration;
  • table size;
  • index size;
  • update rate;
  • lock waits;
  • transaction conflicts;
  • serialization failures.

The key operational lesson is that MVCC performance depends not only on individual query speed but also on transaction lifetime and the database's ability to clean obsolete versions.

Monitoring MVCC

MVCC itself is an internal concurrency mechanism, but its effects are visible through production metrics.

For PostgreSQL, important signals include:

  • long-running transactions;
  • transactions idle while open;
  • dead tuple counts;
  • autovacuum activity;
  • vacuum progress;
  • table and index growth;
  • transaction age;
  • lock waits;
  • update and delete rates;
  • serialization failures;
  • database I/O.

These metrics should be interpreted together.

For example:

Dead tuples rising
        +
Old transaction active
        +
Vacuum running but reclaim limited
        ↓
Investigate snapshot retention

Another pattern might be:

High UPDATE rate
       +
High dead tuple creation
       +
Vacuum cannot keep up
       ↓
Tune workload, schema, indexes,
vacuum capacity, or table design

MVCC problems are therefore often workload-management problems rather than a reason to disable concurrency features.

Common MVCC Mistakes

  • Assuming MVCC means no locks. Writers still conflict, and explicit or internal locks still exist.
  • Assuming an UPDATE simply overwrites a row. MVCC engines may create a new row version.
  • Keeping transactions open during external work. Old snapshots remain active unnecessarily.
  • Leaving transactions idle. An application connection can quietly retain an old snapshot or other transactional resources.
  • Ignoring dead tuples. High-update workloads require enough cleanup capacity.
  • Disabling or neglecting autovacuum without understanding the consequences. PostgreSQL depends on regular vacuum processing.
  • Assuming every transaction sees the latest committed value. Visibility depends on the isolation level and snapshot.
  • Assuming MVCC prevents lost updates or every concurrency anomaly. Application logic and isolation semantics still matter.
  • Using long transactions for batch processing by default. Smaller transaction boundaries are often operationally safer.
  • Ignoring index cost on update-heavy tables. Row version churn can increase index maintenance work.
  • Confusing logical deletion with physical cleanup. A row can become invisible before its storage is reclaimable.

Frequently Asked Questions

MVCC is often summarized as "readers do not block writers," but production behavior is more nuanced. Versions, snapshots, locks, isolation levels, and cleanup all work together.

Does MVCC Eliminate Locks?

No. MVCC reduces the need for readers and writers to block each other, but databases still use locks for many operations.

Concurrent writes to the same rows, explicit locking queries, schema changes, constraints, and other operations can still require synchronization.

Does an UPDATE Immediately Overwrite the Old Row?

Not necessarily. In PostgreSQL's MVCC implementation, an update creates a new tuple version while the previous version remains until it is no longer needed and can be reclaimed.

This allows transactions with older snapshots to continue seeing the correct version.

Why Keep Old Row Versions?

An active transaction may have a snapshot from before a newer update committed.

The old version allows that transaction to maintain the visibility guarantees required by its snapshot and isolation level.

Does MVCC Guarantee Serializable Transactions?

No. MVCC provides versioning and snapshot mechanisms, but serializable behavior requires stronger isolation semantics and potentially additional conflict detection.

Applications using Serializable isolation must be prepared for transactions to fail when the database detects an unsafe concurrency pattern.

Why Are Long Transactions a Problem?

Long transactions can retain old snapshots, which can prevent the database from reclaiming obsolete row versions.

They can also hold locks and other resources for longer periods. In high-update systems, a single unexpectedly old transaction can contribute to substantial version accumulation and operational pressure.

Conclusion

MVCC allows databases to support high concurrency by maintaining multiple row versions and selecting the version visible to each transaction's snapshot. This lets many readers proceed without blocking concurrent writers while preserving transactional visibility rules.

The trade-off is that old versions must be tracked and eventually cleaned up. Transaction isolation, writer conflicts, vacuum behavior, indexes, and especially long-running transactions therefore become important parts of operating an MVCC database.

The core principle is: MVCC improves concurrency by separating row visibility from physical row replacement, but healthy production systems must keep transactions short and version cleanup moving.

Author

Oleksandr Andrushchenko

Oleksandr Andrushchenko

Software Engineer & Solution Architect
Nov 03, 2025 6 209
Enjoyed this article?

Support Oleksandr Andrushchenko

Buy me a coffee

This helps Oleksandr Andrushchenko continue creating useful content

Related articles

Comments (0)