MVCC - Multi-Version Concurrency Control (PostgreSQL/MySQL)

🎯

MVCC - Multi-Version Concurrency Control (PostgreSQL/MySQL)

hardDBMSAmazonGoogleFlipkart

Imagine multiple users editing a shared document simultaneously without overwriting each other's changes - MVCC is the database equivalent that enables this seamless concurrency.

💡 Beginners often confuse MVCC with simple locking mechanisms and miss how it uses multiple versions to avoid blocking reads and writes.

📋

Interview Question

Explain what Multi-Version Concurrency Control (MVCC) is, how it works in PostgreSQL/MySQL, and how it helps achieve concurrency without locking conflicts.

Versioning of data rowsSnapshot isolation and transaction visibilityHow MVCC avoids read-write blocking

💡

Scenario & Trace

ScenarioTwo transactions concurrently read and update the same row in a PostgreSQL database.

Transaction A starts and reads row R (version V1). Transaction B starts and updates row R, creating version V2 but does not commit yet. Transaction A continues to see version V1 due to snapshot isolation. When Transaction B commits, version V2 becomes visible to new transactions. Transaction A commits based on version V1, ensuring no conflicts during its execution.

ScenarioA long-running SELECT query runs while an UPDATE query modifies the same data in MySQL InnoDB.

The SELECT query sees a consistent snapshot of the data as of its start time, reading older versions of rows if they were updated after it began. The UPDATE creates new versions of rows without blocking the SELECT. Both queries proceed concurrently without waiting on locks.

• What happens if two concurrent transactions update the same row and commit?
• How does MVCC handle long-running transactions that prevent cleanup of old versions?
• What occurs when a transaction tries to read data that has been deleted by another uncommitted transaction?

⚠️

Common Mistakes

Confusing MVCC with simple locking

Interviewer thinks candidate lacks understanding of versioning and snapshot isolation

✅ Clarify that MVCC uses multiple versions to avoid blocking, unlike traditional locks

Assuming readers always see the latest data

Interviewer suspects candidate misses snapshot isolation concept

✅ Explain that readers see a consistent snapshot as of transaction start, not uncommitted changes

Ignoring conflict resolution on concurrent writes

Interviewer doubts candidate’s knowledge of transaction serialization

✅ Mention that concurrent conflicting writes cause one transaction to abort or retry

Not recognizing impact of long-running transactions

Interviewer thinks candidate misses practical MVCC maintenance issues

✅ Discuss how old versions accumulate and require vacuuming or cleanup

🧠

Basic Definition - What It Is

💡 This level covers the essential idea you must grasp to answer basic interview questions.

Intuition

MVCC keeps multiple versions of data to allow concurrent reads and writes without locking conflicts.

Explanation

Multi-Version Concurrency Control (MVCC) is a database technique that maintains multiple versions of data rows to enable concurrent access. Instead of locking data for reads or writes, MVCC allows readers to see a consistent snapshot of the database at a point in time, while writers create new versions of rows. This approach reduces contention and improves concurrency by avoiding blocking between readers and writers.

Memory Hook

💡 Think of MVCC like a library where every reader gets their own photocopy of a book page, so no one waits for others to finish reading or writing.

Interview Questions

❓ What is the main advantage of MVCC over traditional locking?

• Allows concurrent reads and writes without blocking
• Readers see a consistent snapshot without waiting
• Improves performance and reduces deadlocks

⏱

Depth Level

Interview Time30 seconds

Depthbasic

Covers the core concept and benefits of MVCC, sufficient for screening rounds.

Interview Target: Minimum floor - never go below this

Knowing only this will help you pass initial screening but not detailed technical rounds.

🧠

Mechanism Depth - How It Works

💡 This level explains the internal workings and is expected in product company interviews.

Intuition

MVCC uses transaction timestamps and version chains to provide snapshot isolation and avoid conflicts.

Explanation

MVCC works by associating each data row version with transaction timestamps or IDs indicating when it was created and when it became obsolete. When a transaction starts, it takes a snapshot timestamp. Reads access the latest committed version visible at that snapshot, ignoring newer uncommitted changes. Writes create new versions with their own timestamps. The database maintains version chains per row, allowing concurrent transactions to operate on different versions without blocking. Garbage collection removes obsolete versions once no active transaction needs them. This mechanism provides snapshot isolation, preventing phenomena like dirty reads and non-repeatable reads, while allowing high concurrency.

Memory Hook

💡 Imagine a timeline of edits to a document where each reader sees the document as it was at their start time, while writers add new edits without erasing history.

Interview Questions

❓ How does MVCC ensure a transaction sees a consistent snapshot?

• Transaction gets a snapshot timestamp at start
• Reads only versions committed before that timestamp
• Ignores newer uncommitted versions

❓ What happens when two transactions update the same row concurrently?

• Both create new versions
• One commit succeeds, the other may get a conflict error
• Database enforces serialization or aborts one transaction

⏱

Depth Level

Interview Time2-3 minutes

Depthintermediate

Demonstrates understanding of MVCC internals, version visibility rules, and conflict resolution.

Interview Target: Target level for FAANG on-sites

Mastering this level distinguishes you from most candidates.

📊

Explanation Depth Levels

💡 Choose depth based on interview stage and company expectations

Level	Interview Time	Suitable For	Risk
Basic Definition	30s	Screening call, initial rounds	Too shallow for on-site or deep technical rounds
Mechanism Depth	2-3 minutes	On-site interviews at FAANG and top product companies	Requires solid understanding; missing details may lose points

💼

Interview Strategy

💡 Use this guide to structure your explanation clearly and confidently before interviews.

How to Present

Start with a concise definition of MVCCGive a real-world analogy or example to illustrate snapshot isolationExplain the internal mechanism of versioning and transaction visibilityDiscuss common edge cases and how MVCC handles conflicts

Time Allocation

Definition: 30s → Example: 1min → Mechanism: 2min → Edge cases: 30s. Total ~4min

What the Interviewer Tests

Interviewer checks your grasp of concurrency control, snapshot isolation, and conflict handling under MVCC.

Common Follow-ups

• What is the difference between MVCC and two-phase locking?
• How does MVCC handle long-running transactions and version cleanup?

💡 These follow-ups test deeper understanding and practical implications of MVCC.

🔍

Pattern Recognition

When to Use

Asked when discussing transaction isolation, concurrency control, or database internals.

Signature Phrases

'Explain MVCC and how it works''Compare MVCC with locking''What happens when two transactions update the same row concurrently?'

NOT This Pattern When

Similar Problems

Practice

1. Consider a 1:N relationship between entities Department and Employee, where one department has many employees. When a new employee is added, what is the sequence of steps to maintain referential integrity in the database?

easy

A. Insert the employee record without any reference to the department.

B. Insert the employee record with a foreign key referencing the department's primary key; verify the department exists.

C. Insert the department record with a foreign key referencing the employee's primary key.

D. Insert the employee record first, then create the department record if missing.

Solution

1. Step 1: Understand the 1:N relationship

   Each employee belongs to one department; department is the 'one' side.

2. Step 2: Referential integrity requires foreign key

   Employee table must have a foreign key referencing Department's primary key.

3. Step 3: Insert employee with valid department reference

   Before inserting employee, ensure the department exists to avoid foreign key violation.

4. Step 4: Evaluate other options

   Insert the employee record first, then create the department record if missing inserts employee before department, risking integrity violation. Insert the department record with a foreign key referencing the employee's primary key reverses foreign key direction, which is incorrect. Insert the employee record without any reference to the department omits foreign key, breaking relationship.

5. Final Answer:

   Option B -> Option B

6. Quick Check:

   Foreign keys must reference existing primary keys to maintain integrity.

Hint: In 1:N, the 'many' side holds the foreign key referencing the 'one' side.

Common Mistakes:

• Inserting dependent records before referenced records
• Misplacing foreign keys on the 'one' side instead of 'many' side

2. Trace the sequence of steps the query optimizer takes when generating an explain plan for a query with a filter predicate on a large table. What happens to the cardinality estimate after applying the filter?

easy

A. The optimizer first estimates the total rows, then reduces cardinality based on filter selectivity before choosing access paths

B. The optimizer ignores filter predicates during cardinality estimation and applies them only at execution time

C. The optimizer assumes filter predicates reduce cardinality to zero if any predicate exists

D. The optimizer estimates cardinality after choosing the access path, not before

Solution

1. Step 1: Initial cardinality estimation

   The optimizer starts by estimating the total number of rows in the table.

2. Step 2: Apply filter selectivity

   It adjusts cardinality by applying the estimated selectivity of the filter predicate, reducing the row count.

3. Step 3: Use adjusted cardinality for access path selection

   The reduced cardinality informs the choice of index scans or full scans.

4. Step 4: Why other options are incorrect

   The optimizer ignores filter predicates during cardinality estimation and applies them only at execution time: Ignoring filters during estimation leads to poor plans.
   The optimizer assumes filter predicates reduce cardinality to zero if any predicate exists: Assuming zero cardinality is unrealistic and would discard valid plans.
   The optimizer estimates cardinality after choosing the access path, not before: Cardinality estimation precedes access path selection to guide it.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Filter predicates reduce estimated rows before access path decisions.

Hint: Filter predicates reduce cardinality estimates before access path selection.

Common Mistakes:

• Thinking filters are ignored until execution
• Assuming filters eliminate all rows
• Believing cardinality is estimated after access path choice

3. Which of the following statements about the trade-offs of clustered indexes is TRUE?

medium

A. Clustered indexes always improve insert performance because data is stored sequentially.

B. Clustered indexes can degrade update performance if the indexed column changes frequently due to physical row movement.

C. Clustered indexes reduce storage space because they eliminate the need for separate index structures.

D. Clustered indexes are ideal for columns with high cardinality and random access patterns.

Solution

1. Step 1: Analyze insert performance claim

   Clustered indexes may slow inserts if rows must be inserted in the middle, causing page splits.

2. Step 2: Evaluate storage space claim

   Clustered indexes do not eliminate index structures; non-clustered indexes still exist and clustered index is part of data storage.

3. Step 3: Understand update impact

   Updating clustered index key causes physical row movement, which can degrade performance.

4. Step 4: Assess suitability for high cardinality random access

   Clustered indexes are less beneficial for random access on high cardinality columns; non-clustered indexes often better.

5. Final Answer:

   Option B -> Option B

6. Quick Check:

   Frequent updates on clustered key -> performance hit due to physical row moves [OK]

Hint: Clustered index updates on key -> physical row moves -> slower updates [OK]

Common Mistakes:

• Assuming clustered indexes always speed inserts
• Believing clustered indexes save storage by removing indexes
• Thinking clustered indexes are best for random access on high cardinality columns

4. What is the time complexity of the SQL query using window functions to rank products within categories, assuming n sales records and m unique products?

medium

A. O(n * m) due to nested aggregation and ranking

B. O(n log n) due to sorting inside window function over all rows

C. O(m^2) because ranking requires pairwise comparisons of products

D. O(n) since aggregation and ranking are linear scans

Solution

1. Step 1: Identify operations in the query

   The query aggregates sales (O(n)) then applies window function with ORDER BY, which requires sorting.

2. Step 2: Analyze sorting cost in window function

   Sorting all rows by category and total_sales costs O(n log n), dominating the complexity.

3. Final Answer:

   Option B -> Option B

4. Quick Check:

   Sorting inside window function -> O(n log n) time [OK]

Hint: Window functions with ORDER BY require sorting -> O(n log n) [OK]

Common Mistakes:

• Assuming linear time ignoring sorting
• Confusing n and m in complexity

5. Which of the following statements about deadlocks in Two-Phase Locking (2PL) is INCORRECT?

medium

A. Strict 2PL completely prevents deadlocks by ordering lock acquisition

B. Deadlock detection algorithms can resolve deadlocks by aborting one of the transactions

C. Deadlocks occur when two or more transactions wait indefinitely for locks held by each other

D. Deadlocks can arise even if transactions follow the 2PL protocol

Solution

1. Step 1: Understand deadlock conditions

   Deadlocks happen when transactions cyclically wait for locks held by each other, which can occur under 2PL.

2. Step 2: Analyze deadlock prevention

   Strict 2PL does not guarantee deadlock prevention; it only ensures serializability.

3. Step 3: Evaluate options

   A, B, and D are correct statements. C is incorrect because strict 2PL does not prevent deadlocks by ordering lock acquisition.

4. Final Answer:

   Option A -> Option A

5. Quick Check:

   Deadlocks require detection and resolution even under strict 2PL.

Hint: Strict 2PL ensures serializability but not deadlock prevention [OK]

Common Mistakes:

• Assuming strict 2PL prevents deadlocks
• Confusing deadlock detection with prevention
• Believing 2PL eliminates all concurrency issues