Multi-version concurrency control (MVCC) in DBMS Theory - Full Explanation

Introduction

Imagine many people trying to read and write in a shared notebook at the same time. Without a good system, their notes could get mixed up or lost. Multi-version concurrency control helps databases handle many users working together without conflicts or delays.

Explanation

Multiple Versions of Data

Instead of changing data directly, MVCC keeps several versions of the same data. Each change creates a new version, so readers can see a stable snapshot without waiting for writes to finish.

MVCC stores multiple versions of data to allow simultaneous reads and writes without blocking.

Snapshot Isolation

When a user reads data, they see a consistent snapshot from a specific point in time. This means their view does not change even if others update the data later, preventing confusion from seeing partial changes.

Snapshot isolation ensures each user sees a stable view of data during their transaction.

Non-blocking Reads

Readers do not block writers and writers do not block readers. Because readers access old versions, they never have to wait for ongoing writes to complete, improving performance and user experience.

MVCC allows reads and writes to happen at the same time without waiting.

Garbage Collection

Old versions of data that are no longer needed are cleaned up by the system. This process frees storage space and keeps the database efficient over time.

Unused old data versions are removed to maintain database efficiency.

Real World Analogy

Imagine a library where every time a book is updated, a new copy is made instead of changing the original. Readers can borrow the copy they want without waiting for updates, and old copies are recycled when no longer needed.

Multiple Versions of Data → Making new copies of books instead of changing the original

Snapshot Isolation → Readers borrowing a specific copy that doesn't change while they read

Non-blocking Reads → Readers and writers using different copies so they don't wait for each other

Garbage Collection → Recycling old book copies that no one is using anymore

Diagram

Diagram

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Version 1     │──────▶│ Version 2     │──────▶│ Version 3     │
│ (Old Data)    │       │ (Updated)     │       │ (Latest)      │
└───────────────┘       └───────────────┘       └───────────────┘
       ▲                      ▲                      ▲
       │                      │                      │
Reader sees           Reader sees           Writer creates
Version 1 snapshot    Version 2 snapshot    Version 3 snapshot

Garbage collection removes versions no longer in use.

This diagram shows how multiple versions of data exist over time, with readers accessing stable snapshots and writers creating new versions.

Key Facts

MVCC → A method that keeps multiple versions of data to allow concurrent access without locking.

Snapshot Isolation → A technique where each transaction sees a consistent snapshot of the database at a point in time.

Non-blocking Reads → Reads that do not wait for writes to finish because they access older data versions.

Garbage Collection → The process of removing old data versions that are no longer needed.

Code Example

DBMS Theory

import sqlite3

conn = sqlite3.connect(':memory:')
conn.isolation_level = None  # Autocommit mode for manual control
cur = conn.cursor()

# Create table
cur.execute('CREATE TABLE items (id INTEGER PRIMARY KEY, value TEXT)')

# Insert initial data
cur.execute("INSERT INTO items (value) VALUES ('Version 1')")

# Start transaction 1 (reader)
cur.execute('BEGIN')
cur.execute('SELECT * FROM items')
print('Transaction 1 reads:', cur.fetchall())

# Start transaction 2 (writer)
cur2 = conn.cursor()
cur2.execute('BEGIN')
cur2.execute("UPDATE items SET value = 'Version 2' WHERE id = 1")

# Transaction 1 reads again (still sees old data)
cur.execute('SELECT * FROM items')
print('Transaction 1 reads again:', cur.fetchall())

# Commit transaction 2
cur2.execute('COMMIT')

# Transaction 1 reads after commit (still old snapshot)
cur.execute('SELECT * FROM items')
print('Transaction 1 reads after commit:', cur.fetchall())

# Commit transaction 1
cur.execute('COMMIT')

# New transaction reads latest data
cur.execute('BEGIN')
cur.execute('SELECT * FROM items')
print('New transaction reads:', cur.fetchall())
cur.execute('COMMIT')

Common Confusions

MVCC means no conflicts ever happen.

MVCC means no conflicts ever happen. MVCC reduces conflicts but does not eliminate them; write-write conflicts still require resolution.

Readers always see the very latest data.

Readers always see the very latest data. Readers see a snapshot from when their transaction started, not the most recent changes made after.

Summary

MVCC allows multiple users to read and write data at the same time by keeping several versions of the data.

Each user sees a consistent snapshot of the data, preventing delays and conflicts during concurrent access.

Old versions are cleaned up automatically to keep the database efficient.

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