Multi-version concurrency control (MVCC) in DBMS Theory - Step-by-Step Execution

Concept Flow - Multi-version concurrency control (MVCC)

Transaction Start

↓

Read Data Version

↓

Check Version Validity

↓

Read

↓

Write New Version

↓

Commit Transaction

↓

Old Versions Cleanup

MVCC allows multiple versions of data to exist so transactions can read consistent snapshots without waiting for others.

Execution Sample

DBMS Theory

T1 starts
T1 reads version V1
T2 starts
T2 writes new version V2
T1 reads again
T1 commits

Shows two transactions accessing data versions concurrently without blocking.

Analysis Table

Step	Transaction	Action	Data Version Read/Written	Result/State
1	T1	Start	-	T1 begins, snapshot sees V1
2	T1	Read	V1	Reads version V1 successfully
3	T2	Start	-	T2 begins, snapshot sees V1
4	T2	Write	V2	Creates new version V2, does not block T1
5	T1	Read	V1	Still reads old version V1 (snapshot isolation)
6	T1	Commit	-	T1 commits, no conflict
7	T2	Commit	-	T2 commits, new version V2 becomes visible
8	-	-	-	Old version V1 kept until no transactions need it

💡 Both transactions commit successfully; old versions kept for consistency until safe to remove.

State Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	After Step 7	Final
Data Versions	V1	V1	V1 and V2	V1 and V2	V1 and V2	V2 (V1 kept until safe)
T1 Snapshot	-	Sees V1	Sees V1	Sees V1	Committed	Committed
T2 Snapshot	-	-	Sees V1	Sees V1	Committed	Committed

Key Insights - 3 Insights

Why does T1 keep reading the old version V1 even after T2 writes V2?

Because T1 uses a snapshot taken at its start (Step 1), it sees a consistent view of data as of that time, ignoring newer versions like V2 created by T2 (see Steps 2, 4, 5).

What happens to old versions like V1 after transactions commit?

Old versions remain available until no active transactions need them, ensuring consistent reads. Cleanup happens later (Step 8).

Can T2's write block T1's read in MVCC?

No, MVCC allows concurrent reads and writes by keeping multiple versions, so T1's read of V1 is never blocked by T2's write of V2 (see Steps 4 and 5).

Visual Quiz - 3 Questions

Look at the execution table at Step 5. Which data version does T1 read?

A V2

B V1

C No version (blocked)

D Both V1 and V2

💡 Hint

Check Step 5 in execution_table where T1 reads version V1.

At which step does T2 create a new data version?

A Step 4

B Step 2

C Step 5

D Step 7

💡 Hint

Look at Step 4 in execution_table where T2 writes version V2.

If T1 started after T2 committed, which version would T1 read?

A V1

B No version

C V2

D Both V1 and V2

💡 Hint

Refer to variable_tracker showing snapshots and visible versions after commits.

Concept Snapshot

Multi-version concurrency control (MVCC):
- Keeps multiple data versions for concurrent access
- Each transaction reads a consistent snapshot
- Writes create new versions without blocking reads
- Old versions kept until no longer needed
- Enables high concurrency with snapshot isolation

Full Transcript

Multi-version concurrency control (MVCC) is a method used in databases to allow multiple transactions to access data concurrently without blocking each other. When a transaction starts, it sees a snapshot of the data at that time. If another transaction writes new data, it creates a new version without overwriting the old one. This way, the first transaction continues to read the old version consistently. Transactions commit independently, and old versions are kept until no active transactions need them. This approach improves performance and consistency by avoiding locks during reads.