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.

Practice

1. What is the main purpose of Multi-Version Concurrency Control (MVCC) in databases?

easy

A. To delete old versions of data immediately after update

B. To prevent any user from reading data during updates

C. To lock the entire database during transactions

D. To allow multiple users to access data simultaneously without blocking

Solution

1. Step 1: Understand MVCC's role in concurrency

   MVCC allows multiple users to work on the database at the same time without waiting for locks.

2. Step 2: Compare options with MVCC purpose

   Only To allow multiple users to access data simultaneously without blocking correctly states that MVCC avoids blocking by allowing simultaneous access.

3. Final Answer:

   To allow multiple users to access data simultaneously without blocking -> Option D

4. Quick Check:

   MVCC = simultaneous access without blocking [OK]

Hint: MVCC means no waiting for others to finish [OK]

Common Mistakes:

• Thinking MVCC locks the whole database
• Believing MVCC deletes old data immediately
• Assuming MVCC blocks reads during writes

2. Which of the following best describes how MVCC manages data versions?

easy

A. It creates a new version of data for each transaction without overwriting existing versions

B. It keeps only the latest version of data and deletes old ones immediately

C. It locks data rows to prevent any version changes during transactions

D. It merges all versions into one after each transaction

Solution

1. Step 1: Recall MVCC version handling

   MVCC keeps multiple versions of data so each transaction sees a consistent snapshot.

2. Step 2: Match options to MVCC behavior

   It creates a new version of data for each transaction without overwriting existing versions correctly states that MVCC creates new versions without overwriting old ones immediately.

3. Final Answer:

   It creates a new version of data for each transaction without overwriting existing versions -> Option A

4. Quick Check:

   MVCC = new versions per transaction [OK]

Hint: MVCC never overwrites old data instantly [OK]

Common Mistakes:

• Confusing MVCC with locking mechanisms
• Thinking MVCC deletes old versions right away
• Assuming MVCC merges versions after transactions

3. Consider a database using MVCC. If Transaction T1 reads data version V1 and Transaction T2 updates the same data creating version V2, what version does T1 see?

medium

A. Both versions V1 and V2 simultaneously

B. Version V1, the snapshot at the start of T1

C. Version V2, the latest updated version

D. No version until T2 commits

Solution

1. Step 1: Understand MVCC snapshot isolation

   Each transaction sees a consistent snapshot of data as it was when the transaction started.

2. Step 2: Apply to T1 and T2 scenario

   T1 started before T2's update, so T1 sees version V1, not the newer V2.

3. Final Answer:

   Version V1, the snapshot at the start of T1 -> Option B

4. Quick Check:

   MVCC snapshot = version at transaction start [OK]

Hint: Transaction sees data as of its start time [OK]

Common Mistakes:

• Assuming T1 sees latest version immediately
• Thinking transactions see multiple versions at once
• Believing uncommitted changes are visible

4. A developer notices that a transaction reads outdated data even after another transaction commits an update. What is the likely cause in an MVCC system?

medium

A. The second transaction did not commit properly

B. The database failed to create a new version for the update

C. The first transaction started before the update and sees a consistent snapshot

D. MVCC locks prevented the update from being visible

Solution

1. Step 1: Analyze transaction snapshot behavior

   In MVCC, a transaction sees data as it was when it started, ignoring later commits.

2. Step 2: Identify cause of outdated read

   If the transaction started before the update commit, it will see the old data version.

3. Final Answer:

   The first transaction started before the update and sees a consistent snapshot -> Option C

4. Quick Check:

   MVCC snapshot isolation causes outdated reads [OK]

Hint: Old snapshot hides newer commits until restart [OK]

Common Mistakes:

• Blaming MVCC for failed commits
• Assuming MVCC uses locks to block reads
• Thinking new versions always appear instantly

5. In a busy database using MVCC, how does keeping multiple versions of data improve performance and accuracy?

hard

A. By allowing readers to access old versions without waiting for writers to finish

B. By locking data rows to prevent any concurrent access

C. By deleting all old versions immediately to save space

D. By forcing all transactions to run one after another

Solution

1. Step 1: Understand MVCC versioning benefits

   Multiple versions let readers see consistent data without waiting for writers to complete.

2. Step 2: Compare options for performance and accuracy

   By allowing readers to access old versions without waiting for writers to finish correctly explains that readers access old versions, avoiding delays and ensuring accuracy.

3. Final Answer:

   By allowing readers to access old versions without waiting for writers to finish -> Option A

4. Quick Check:

   MVCC = readers never wait for writers [OK]

Hint: Readers use old versions to avoid waiting [OK]

Common Mistakes:

• Thinking MVCC locks block readers
• Believing old versions are deleted immediately
• Assuming transactions run sequentially