Multi-version concurrency control (MVCC) in DBMS Theory - Time & Space Complexity

Time Complexity: Multi-version concurrency control (MVCC)

O(n)

Understanding Time Complexity

We want to understand how the time cost of managing multiple versions of data grows as more transactions happen.

How does MVCC handle many reads and writes efficiently as data changes?

Scenario Under Consideration

Analyze the time complexity of the following MVCC operations.

-- When a transaction reads data:
SELECT * FROM table WHERE id = X AND version <= current_version ORDER BY version DESC LIMIT 1;

-- When a transaction writes data:
INSERT INTO table (id, data, version) VALUES (X, new_data, new_version);

-- Cleanup old versions:
DELETE FROM table WHERE version < oldest_active_version;
    

This snippet shows how MVCC reads the latest visible version, writes a new version, and cleans old versions.

Identify Repeating Operations

Look at what repeats as data and transactions grow.

• Primary operation: Searching for the correct version of a row during reads.
• How many times: Once per read, but depends on how many versions exist for that row.

How Execution Grows With Input

As more versions accumulate, finding the right version can take longer if not indexed well.

Input Size (versions per row)	Approx. Operations
10	About 10 checks to find the right version
100	About 100 checks if no index
1000	About 1000 checks if no index

Pattern observation: Without indexing, the search grows linearly with the number of versions per row.

Final Time Complexity

Time Complexity: O(n)

This means the time to find the correct version grows linearly with the number of versions stored for a row.

Common Mistake

[X] Wrong: "MVCC always gives constant time reads regardless of versions."

[OK] Correct: If many versions exist and no indexing is used, the system may scan many versions, making reads slower.

Interview Connect

Understanding how MVCC scales helps you explain database performance and concurrency control clearly, a valuable skill in many roles.

Self-Check

"What if we added an index on the version column? How would the time complexity change?"

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