SQL 104 🛢️ Multi-Version Concurrency Control (MVCC) Internals - KandZ

⏱️ Estimated read: 13 min | |

Multi-Version Concurrency Control is the mechanism that lets a database serve readers and writers simultaneously without locking. A reader sees a consistent snapshot of the data as it existed when its query or transaction started, while a writer modifies the current version. The two never block each other because they are looking at different versions of the same row. Every major relational database implements MVCC, but the implementations diverge in one fundamental decision: where the old versions live. PostgreSQL stores multiple versions in the table itself and garbage-collects them later. Oracle and MySQL/InnoDB store only the current version and reconstruct the past from undo logs. SQL Server stores the old versions in a separate database (tempdb). This choice determines the storage overhead, the vacuum/purge behavior, and the failure modes of each engine.

Key point: MVCC provides non-blocking reads by exposing different versions of a row to different transactions. PostgreSQL and SQL Server store old versions in the data (or tempdb) and rely on garbage collection; Oracle and InnoDB store only the latest version and reconstruct older ones from undo records. The visibility decision is always made by comparing a transaction’s snapshot against the version metadata.

Why two implementation families exist

The update-in-place problem. A database that overwrites the row in place cannot serve an old reader that is still scanning the page. The reader would see the new data mid-query, violating the consistency guarantee. The solution is to preserve the old data somewhere. The two families differ in where: PostgreSQL writes a new tuple into the table and leaves the old one in place, while Oracle and InnoDB overwrite the row and write the before-image to an undo log . Both preserve the old data; one does it in the heap, the other in a separate structure.

The garbage collection problem. Old versions accumulate. PostgreSQL needs a vacuum process to reclaim the space occupied by dead tuples . InnoDB needs a purge thread to remove undo records that are no longer needed by any active read view . Oracle reuses undo segments once the transactions that needed them have completed. SQL Server relies on the version store in tempdb, and if tempdb fills, the engine shrinks the version store and may terminate long-running transactions . The garbage collection mechanism is the operational cost of MVCC, and it is the source of the most common performance problems: table bloat in PostgreSQL, undo tablespace growth in Oracle, and tempdb pressure in SQL Server.

The snapshot problem. A reader’s snapshot is not simply “the current time.” It is the set of transactions that had committed when the snapshot was taken. PostgreSQL checks the tuple’s xmin and xmax against the transaction status in CLOG . InnoDB builds a ReadView that contains the list of active transaction IDs at the moment the view was created, and a row version is visible only if its trx_id is not in that list and is less than the maximum ID . Oracle compares the block’s SCN against the query’s SCN . The mechanism differs, but the logic is the same: the version is visible if it was created by a transaction that committed before the snapshot and has not been deleted by a transaction that committed before the snapshot.

a. PostgreSQL: in-table versions and xmin/xmax

PostgreSQL’s MVCC is append-oriented. An UPDATE does not modify the existing row; it inserts a new tuple and marks the old one as expired . The old tuple remains in the table until vacuum removes it. The table may contain data from aborted transactions, because PostgreSQL does not undo changes on abort; it marks the transaction as aborted in CLOG and the tuple becomes invisible .

Each tuple carries two hidden system columns. xmin is the transaction ID that inserted this tuple version. xmax is the transaction ID that deleted or updated it; it is zero if the tuple has not been deleted . A tuple is visible to a transaction if xmin is valid (committed or the current transaction) and xmax is not valid . The validity check consults the transaction status, not a simple numeric comparison, because transactions can commit out of order .

The vacuum process is the garbage collector. It scans the table, identifies tuples that are dead (expired and no longer visible to any active transaction), and marks their space as reusable. It also removes the index entries that point to those tuples . Without vacuum, the table grows without bound, and the index entries for dead tuples slow down every scan. The autovacuum daemon automates this, and the fillfactor table option reserves space on each page for the new tuples that updates create.

b. InnoDB: undo logs and ReadView

InnoDB stores only the latest version in the index. When a record is modified, the current version is first copied to an undo record in the undo log. The record in the index is then updated, and its ROLL_PTR (rollback pointer) points to the undo record. Each undo record points to the previous one, forming a version chain . A reader that needs an older version follows the chain and applies the undo records until it reaches a version that its ReadView allows it to see.

The ReadView is the snapshot. At REPEATABLE READ (the default), the ReadView is created at the first read in the transaction and remains fixed for the transaction’s duration. At READ COMMITTED, a new ReadView is created for each statement . The ReadView contains the list of active transaction IDs and the maximum transaction ID at the time it was created. A row version is visible if its trx_id is less than the maximum and not in the active list . If the version is not visible, the reader follows the ROLL_PTR to the previous version and checks again.

The purge thread is the garbage collector. It removes undo records that are no longer needed by any active ReadView . A long-running transaction holds an old ReadView, which prevents the purge thread from removing the undo records that the transaction might still need. This is why long-running transactions cause undo tablespace growth in InnoDB: the history chain cannot be truncated until the oldest ReadView is released.

c. Oracle, SQL Server, and the SCN comparison

Oracle’s MVCC is built on the System Change Number (SCN). Every transaction and every query is associated with an SCN. When a query reads a block, it compares the block’s SCN against its own SCN. If the block contains changes from a transaction with a later SCN, Oracle uses the undo segments to reconstruct the block as it existed at the query’s SCN . The row header in Oracle is fixed at 3 bytes, and the transaction information is stored in the block header’s ITL (Interested Transaction List) rather than in every row . This saves space compared to PostgreSQL’s per-tuple headers, but it makes the undo-based reconstruction more complex.

SQL Server’s snapshot isolation and read-committed snapshot use row versioning in tempdb. When a row is modified, the old version is written to the version store in tempdb, and the row is stamped with a 14-byte overhead: a 6-byte transaction sequence number and an 8-byte pointer to the versioned row . The overhead is added the first time a row is modified after row versioning is enabled, and it increases the row size by 14 bytes. For a table with an average row size of 300 bytes, this is roughly a 5% growth . The version store is garbage-collected when the transactions that needed the old versions complete, but if tempdb fills, the engine shrinks the version store and may roll back long-running transactions .

Complete Example Session

The following session traces an update through PostgreSQL’s in-table versioning and InnoDB’s undo log, showing how a concurrent reader sees different versions.

// ============================================
// PART 1: THE INITIAL ROW
// ============================================
// PostgreSQL tuple:
//   xmin = 100 (inserted by T100)
//   xmax = 0 (not deleted)
//   balance = 500
//
// InnoDB record:
//   trx_id = 100
//   balance = 500
//   ROLL_PTR = null (no undo record yet)

// ============================================
// PART 2: T200 UPDATES THE ROW
// ============================================
// PostgreSQL:
//   1. New tuple inserted with xmin = 200, xmax = 0
//   2. Old tuple's xmax set to 200
//   3. Both tuples are in the table
//   4. Index entries point to both versions
//
// InnoDB:
//   1. Undo record created with the old balance (500)
//   2. Index record updated: trx_id = 200, balance = 400
//   3. ROLL_PTR points to the undo record
//   4. Only one record in the index

// ============================================
// PART 3: A CONCURRENT READER WITH SNAPSHOT BEFORE T200
// ============================================
// PostgreSQL:
//   The reader's snapshot sees T200 as active (not committed)
//   It checks the new tuple: xmin = 200 is active → invisible
//   It checks the old tuple: xmin = 100 is committed,
//     xmax = 200 is active → visible
//   The reader sees balance = 500
//
// InnoDB:
//   The reader's ReadView includes T200 in the active list
//   It reads the index record: trx_id = 200 is active → invisible
//   It follows ROLL_PTR to the undo record
//   The undo record has trx_id = 100 (committed) → visible
//   The reader sees balance = 500

// ============================================
// PART 4: A READER WITH SNAPSHOT AFTER T200 COMMITS
// ============================================
// PostgreSQL:
//   T200 is committed in CLOG
//   The new tuple: xmin = 200 is committed, xmax = 0 → visible
//   The reader sees balance = 400
//
// InnoDB:
//   T200 is not in the active list
//   The index record: trx_id = 200 is committed → visible
//   The reader sees balance = 400

// ============================================
// PART 5: VACUUM / PURGE
// ============================================
// PostgreSQL:
//   Vacuum sees that no active transaction can see the old tuple
//   (xmax = 200 is committed, and all snapshots are after T200)
//   The old tuple's space is marked free
//
// InnoDB:
//   The purge thread sees that no active ReadView needs the undo record
//   The undo record is removed
//   The rollback segment space is reused

The five parts cover the initial row, the update, the concurrent reader with an old snapshot, the reader with a new snapshot, and the garbage collection. What the session demonstrates is that the two implementations produce the same visibility result through different mechanisms: PostgreSQL keeps both tuples in the table and checks xmin/xmax, while InnoDB keeps one record and follows the undo chain.

Quick Reference

MVCC Implementation Families

Family	Engines	Old Version Location
In-table versions	PostgreSQL	Table heap
Undo-based	Oracle, InnoDB	Undo logs/segments
Separate database	SQL Server	tempdb

Visibility Metadata

Engine	Metadata	Snapshot
PostgreSQL	`xmin`, `xmax`	Transaction status in CLOG
InnoDB	`trx_id`, `ROLL_PTR`	ReadView active list
Oracle	SCN in block/row	Query SCN
SQL Server	14-byte version pointer	Snapshot transaction sequence

Garbage Collection

Engine	Mechanism	Trigger
PostgreSQL	Vacuum	Autovacuum or manual
InnoDB	Purge thread	Oldest ReadView release
Oracle	Undo segment reuse	Transaction completion
SQL Server	Version store cleanup	Transaction completion, tempdb pressure

Best Practices

✅ Do This:

-- PostgreSQL: keep autovacuum enabled and tuned
ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.05);

-- InnoDB: avoid long-running transactions
-- A long transaction holds an old ReadView and blocks purge

-- SQL Server: monitor tempdb version store size
SELECT * FROM sys.dm_db_file_space_usage;

❌ Don’t Do This:

-- PostgreSQL: run a long transaction and expect vacuum to keep up
-- Dead tuples accumulate, table bloats

-- InnoDB: leave a transaction open while a user thinks
-- Undo log grows, purge is blocked

-- SQL Server: enable snapshot isolation without sizing tempdb
-- Version store fills tempdb, transactions roll back

Common Pitfalls

Pitfall	Why It Happens	Fix
PostgreSQL table bloat	Vacuum cannot keep up with updates	Tune autovacuum, lower fillfactor
InnoDB undo growth	Long-running ReadView blocks purge	Shorten transactions
SQL Server tempdb full	Version store exceeds capacity	Size tempdb, shorten transactions
Oracle ORA-01555	Undo segment too small for long query	Increase undo retention
InnoDB history list length	Purge lagging	Check `Innodb_history_list_length`

Real-World Examples

1. PostgreSQL dead tuples

SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';

2. InnoDB history list

SHOW ENGINE INNODB STATUS\G
-- Look for "History list length"

3. SQL Server version store

SELECT * FROM sys.dm_tran_version_store_space_usage;

4. Oracle undo retention

ALTER SYSTEM SET undo_retention = 3600;

5. PostgreSQL vacuum

VACUUM (VERBOSE, ANALYZE) orders;

6. InnoDB purge

-- Purge is automatic; check innodb_purge_threads
SHOW VARIABLES LIKE 'innodb_purge_threads';

7. SQL Server snapshot isolation

ALTER DATABASE db SET ALLOW_SNAPSHOT_ISOLATION ON;

8. Oracle SCN

SELECT CURRENT_SCN FROM V$DATABASE;

9. PostgreSQL fillfactor

CREATE TABLE t (...) WITH (fillfactor = 80);

10. InnoDB long transaction check

SELECT trx_id, trx_started FROM information_schema.innodb_trx;

Visual

PostgreSQL In-Table Versions

┌──────────────────────────────────────────────┐
│  Table heap                                  │
│  ┌────────────────────────────────────────┐  │
│  │ tuple v1: xmin=100, xmax=200, bal=500  │  │
│  │ tuple v2: xmin=200, xmax=0,   bal=400  │  │
│  └────────────────────────────────────────┘  │
│         │                                    │
│         ▼                                    │
│  Old snapshot sees v1 (xmax active)          │
│  New snapshot sees v2 (xmin committed)       │
└──────────────────────────────────────────────┘

InnoDB Undo Chain

┌──────────────────────────────────────────────┐
│  Index record                                │
│  ┌────────────────────────────────────────┐  │
│  │ trx_id=200, bal=400, ROLL_PTR ───────┐ │  │
│  └──────────────────────────────────────┼─┘  │
│                                         │    │
│         ┌───────────────────────────────┘    │
│         ▼                                    │
│  Undo record                                 │
│  ┌────────────────────────────────────────┐  │
│  │ trx_id=100, bal=500, prev=null         │  │
│  └────────────────────────────────────────┘  │
│         │                                    │
│         ▼                                    │
│  Old ReadView follows ROLL_PTR to v1         │
└──────────────────────────────────────────────┘

Garbage Collection

┌──────────────────────────────────────────────┐
│  PostgreSQL: vacuum removes dead tuples      │
│  → space reused, index entries cleaned       │
│                                              │
│  InnoDB: purge removes old undo records      │
│  → rollback segment space reused             │
│  → blocked by oldest ReadView                │
└──────────────────────────────────────────────┘

Summary

Item	Value
MVCC purpose	Non-blocking reads and writes
PostgreSQL	In-table versions, `xmin`/`xmax`, vacuum
InnoDB	Undo logs, ReadView, purge
Oracle	Undo segments, SCN comparison
SQL Server	tempdb version store, 14-byte overhead
Snapshot	Set of committed transactions at read time
Garbage collection	Vacuum, purge, undo reuse, tempdb cleanup

Key takeaways:

MVCC gives each transaction a consistent snapshot without locking. A reader sees the data as it existed when its snapshot was taken, even if a writer has modified the current version .
PostgreSQL stores multiple tuple versions in the table. The xmin and xmax fields determine visibility, and vacuum reclaims the space of dead tuples. The table can contain data from aborted transactions .
InnoDB stores one version and reconstructs the past from undo logs. The ROLL_PTR links the current record to its undo chain, and the ReadView determines which version is visible. The purge thread removes undo records that no active ReadView needs .
Oracle uses SCN comparison and undo segments. The block’s SCN is compared against the query’s SCN, and undo is applied to reconstruct the block at the query’s point in time .
SQL Server stores versions in tempdb with a 14-byte overhead. Snapshot isolation and read-committed snapshot add a transaction sequence number and a pointer to the versioned row, increasing row size and table growth .
Long-running transactions are the enemy of MVCC. They hold old snapshots, which block garbage collection and cause table bloat (PostgreSQL), undo growth (InnoDB), or tempdb pressure (SQL Server) .

Remember: MVCC is not a single algorithm. It is a family of implementations that share the same goal: letting readers and writers proceed without blocking. The differences are in where the old versions live and how they are cleaned up. PostgreSQL keeps them in the table and relies on vacuum. InnoDB keeps them in undo logs and relies on purge. Oracle keeps them in undo segments and relies on SCN comparison. SQL Server keeps them in tempdb and relies on the version store cleanup. The visibility rules are always the same: a version is visible if it was created by a committed transaction and not deleted by another committed transaction, as of the reader’s snapshot. The garbage collection is always the same: old versions are removed when no active snapshot can see them. The operational discipline is always the same: keep transactions short, or the garbage collector falls behind.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!