Understanding Multi-Version Concurrency Control from First Principles
Every database promises something deceptively simple.
It tells multiple users they can read and write data at the same time.
Your banking application updates balances while another customer checks their account.
An online store updates inventory while thousands of customers browse products.
A social media platform lets millions of people publish posts while everyone else continues scrolling.
From the outside, everything appears to happen simultaneously.
But underneath, things are far more complicated.
Imagine two people trying to edit the same document at the exact same moment.
Or two customers purchasing the last product in stock.
Or two bank transfers modifying the same account balance.
Without careful coordination, databases would constantly overwrite each other's work.
This is the problem of concurrency.
For decades, databases solved this using locks.
One transaction would lock a row.
Everyone else had to wait.
Locks work.
But they don't scale well.
Modern databases like PostgreSQL, MySQL (InnoDB), CockroachDB, Oracle, and others rely heavily on a different idea.
One that initially feels almost magical.
Instead of making readers wait...
They create multiple versions of the same data.
This technique is called Multi-Version Concurrency Control, or MVCC.
When I first encountered MVCC, it seemed surprisingly abstract.
After building one from scratch, however, it became one of the most elegant pieces of engineering I'd ever studied.
Let's build one together.
The Problem with Traditional Locks
Imagine a simple Users table.
Users
+----+---------+
| ID | Name |
+----+---------+
| 1 | Derek |
+----+---------+
Now suppose Transaction A wants to update the row.
UPDATE users
SET name='Alex'
WHERE id=1;
Meanwhile...
Transaction B wants to read it.
Traditional locking produces:
Transaction A
LOCK ROW
↓
Update
↓
Commit
↓
Unlock
↓
Transaction B Reads
The reader waits.
Not ideal.
Why Waiting Hurts Performance
Imagine one thousand users reading product information.
Only one administrator updates the description.
Without MVCC:
999 Readers
↓
Waiting
↓
Waiting
↓
Waiting
Even though readers don't modify data.
The database becomes unnecessarily slow.
The Core Idea Behind MVCC
Instead of modifying rows directly...
Create a new version.
Version 1
Name = Derek
↓
Update
↓
Version 2
Name = Alex
Readers continue using Version 1.
Writers create Version 2.
Nobody blocks.
Thinking in Timelines
MVCC is easier to understand visually.
Time
──────────────────────────►
Version 1
"Derek"
───────────────┐
│
▼
Version 2
"Alex"
──────────────────────────►
Both versions coexist temporarily.
Every Row Has History
Instead of storing:
User
ID
Name
We store:
User
ID
Name
Created Version
Deleted Version
Each row knows when it became visible.
And when it disappeared.
Designing Our Row
Rust implementation.
pub struct Row {
pub id: u64,
pub name: String,
pub created_tx: u64,
pub deleted_tx: Option<u64>,
}
Notice something important.
Rows are never immediately overwritten.
Transactions Need IDs
Every transaction receives a unique identifier.
Transaction 1
↓
Transaction 2
↓
Transaction 3
↓
Transaction 4
Rust.
pub struct Transaction {
pub id: u64,
}
Simple.
Yet incredibly powerful.
Reading Data
Suppose the table contains:
ID
Name
Created
Deleted
---------------------------------
1
Derek
1
None
Transaction 5 reads.
Is it visible?
Yes.
Because:
Created <= Transaction ID
The row already existed.
Updating Data
Now Transaction 6 executes:
UPDATE users
SET name='Alex'
WHERE id=1;
Instead of replacing the row...
We perform:
Old Row
Deleted = 6
↓
New Row
Created = 6
Now two versions exist.
Table After Update
+----+--------+---------+---------+
| ID | Name | Created | Deleted |
+----+--------+---------+---------+
| 1 | Derek | 1 | 6 |
| 1 | Alex | 6 | None |
+----+--------+---------+---------+
Nothing was overwritten.
Visibility Rules
Now comes the magic.
Transaction 5 should see:
Derek
Transaction 7 should see:
Alex
Same table.
Different results.
Depending on when the transaction started.
Snapshot Isolation
Every transaction observes a snapshot.
Transaction Starts
↓
Snapshot Created
↓
Reads Always Use Snapshot
Even if newer updates occur.
This provides consistency.
Visualizing Snapshots
Time
────────────────────────────────────►
T1
Reads Derek
───────────────────────────
Update to Alex
───────────────────────────
T2
Reads Alex
Neither transaction blocks the other.
Implementing Visibility
Rust.
impl Row {
pub fn visible(
&self,
tx: u64,
) -> bool {
self.created_tx <= tx
&&
match self.deleted_tx {
Some(d) => tx < d,
None => true,
}
}
}
This small function powers MVCC.
Reading Rows
Query execution.
rows.iter()
.filter(
|r|
r.visible(tx.id)
)
Every query automatically filters invisible versions.
Applications never notice.
Inserting Rows
Insertion becomes simple.
Row {
id,
name,
created_tx: tx.id,
deleted_tx: None,
}
Only one version exists.
Initially.
Deleting Rows
Deletion doesn't remove anything immediately.
Instead:
Deleted = Transaction ID
The row remains.
Older transactions still need it.
Example Timeline
Transaction 2
Insert User
↓
Transaction 4
Delete User
↓
Transaction 3
Still Sees User
↓
Transaction 5
Doesn't See User
Notice the overlap.
Different transactions observe different realities.
Version Chains
Many databases organize versions as chains.
Newest
↓
Alex
↓
Derek
↓
Original
Readers traverse the chain until they find a visible version.
Storage Layout
Users
↓
Version List
↓
Row
↓
Previous Version
↓
Previous Version
Some databases optimize this differently.
The idea remains similar.
Garbage Collection
Eventually...
Old versions become unnecessary.
Version 1
No Active Transactions
↓
Delete Version
Otherwise storage would grow forever.
Vacuuming
PostgreSQL calls this process:
VACUUM
It removes obsolete versions once no active transaction can see them.
Transaction Manager
Eventually we need something managing transactions.
pub struct TransactionManager {
next_id: u64,
}
Creating one.
pub fn begin(
&mut self,
) -> Transaction {
self.next_id += 1;
Transaction {
id: self.next_id,
}
}
Every transaction receives a unique snapshot.
Building the Engine
Architecture.
Applications
│
▼
Query Engine
│
▼
Transaction Manager
│
▼
MVCC Visibility
│
▼
Storage Engine
Each layer performs one responsibility.
Reading Algorithm
Read Query
↓
Find Candidate Rows
↓
Check Visibility
↓
Return Matching Versions
Simple.
Elegant.
Fast.
Updating Algorithm
Find Row
↓
Mark Deleted
↓
Insert New Version
↓
Commit
No overwriting.
Ever.
Comparing Locking vs MVCC
| Locking | MVCC |
|---|---|
| Readers Wait | Readers Continue |
| Writers Block Readers | Readers Never Block |
| Less Storage | More Storage |
| Simple | More Sophisticated |
| Lower Concurrency | Excellent Concurrency |
The additional complexity buys significantly better scalability.
Real Database Behavior
PostgreSQL.
Tuple
↓
xmin
↓
xmax
These resemble our:
created_tx
deleted_tx
Different names.
Same philosophy.
Why This Changed My Thinking
Before understanding MVCC, I imagined databases as continuously modifying rows.
Now I think differently.
Rows don't simply exist.
They evolve through time.
A table becomes a timeline rather than a static structure.
Queries become historical observations.
Transactions become snapshots of reality.
That perspective fundamentally changed how I design backend systems.
Complete Architecture
Applications
│
▼
SQL Queries
│
▼
Query Planner
│
▼
Transaction Manager
│
▼
Snapshot Manager
│
▼
Visibility Checker
│
┌────────────────┼────────────────┐
▼ ▼ ▼
Version Chain Version Chain Version Chain
│ │ │
▼ ▼ ▼
Storage Engine
│
▼
Disk Persistence
Notice how MVCC isn't just one algorithm.
It's an architecture.
Implementation Summary
Our miniature MVCC engine now supports:
✓ Transaction IDs
✓ Snapshot visibility
✓ Multiple row versions
✓ Inserts
✓ Updates
✓ Deletes
✓ Version filtering
✓ Garbage collection strategy
Although simplified, these are the same foundational ideas used inside production databases serving millions of users every day.
Where Real Databases Go Further
Production systems extend these ideas considerably.
They include:
- Write-Ahead Logging (WAL)
- Crash recovery
- Serializable isolation
- Lock escalation
- Index versioning
- Replication
- Distributed transactions
- Checkpointing
- Compression
- Background vacuum workers
Yet none of those features replace MVCC.
They build upon it.
Final Thoughts
Building a miniature MVCC engine completely changed the way I think about databases.
Before studying concurrency control, I viewed a table as a collection of rows that were constantly being modified in place.
After implementing MVCC, I realized that's not how many modern databases think at all.
Instead of treating data as something that changes, they treat it as something that evolves.
Each update becomes a new version.
Each transaction becomes a snapshot in time.
Each query observes a carefully constructed view of reality that remains consistent from start to finish.
That's an incredibly elegant solution to one of the hardest problems in computer science.
It also reinforces a broader lesson about software engineering.
The fastest systems aren't always the ones that perform less work.
They're often the ones that organize work more intelligently.
Rather than forcing readers and writers to compete over the same data, MVCC lets them coexist.
Instead of serializing the world through locks, it embraces history.
And perhaps that's why MVCC remains one of my favorite database concepts.
It's a reminder that some of the best engineering solutions don't eliminate complexity.
They reorganize it into something that feels almost effortless.
Top comments (1)
Building the toy engine is a great way to make version visibility concrete. The next step I’d add is that a monotonically increasing transaction ID is not enough to define a real snapshot.
If transaction 6 created a version but is still active (or later aborts), transaction 7 must not treat created_tx <= 7 as visible. A snapshot needs at least a visibility horizon plus the set/range of transactions that were active when it was taken, and the visibility function needs commit/abort state, “my own writes,” and delete-transaction rules.
That also surfaces two important corrections to the simplified model:
A useful extension would run overlapping schedules explicitly: uncommitted insert, aborted update, two writers on one row, long-lived reader during several commits, then GC using the oldest active snapshot. Those cases turn the timeline illustration into an actual isolation model.