What is MVCC (Multi-Version Concurrency Control) in MySQL?

Question

Accepted Answer

66
   What is MVCC (Multi-Version Concurrency Control) in MySQL?
  Level: Advanced — A deep InnoDB internals question that impresses senior interviewers.

  MVCC (Multi-Version Concurrency Control) is the technique InnoDB uses to allow readers and writers to work concurrently without blocking each other. Instead of locking rows for reads, InnoDB keeps multiple versions of a row, so each transaction sees a consistent snapshot of the data as of when it started.

  
     The core idea: "Readers never block writers, and writers never block readers." A reading transaction sees an older committed version of a row while a writer is updating it — no waiting required.
  

   How MVCC Works Under the Hood
  
     Hidden columns: InnoDB adds hidden fields to each row — DB_TRX_ID (the transaction that last modified it) and DB_ROLL_PTR (a pointer to the previous version).
     Undo logs: Older row versions are reconstructed from the undo log when a transaction needs to see the "past" state.
     Read view (snapshot): Each transaction gets a consistent snapshot determining which row versions are visible to it.
  

   Consistent vs Locking Reads
  
    
      
        
          Read Type
          Uses MVCC?
          Example
        
      
      
        
          Consistent (snapshot) read
          Yes — reads a version, no locks
          Plain SELECT
        
        
          Locking read
          No — reads latest, takes locks
          SELECT ... FOR UPDATE
        
      
    
  

   Benefits of MVCC
  
     High concurrency — reads don't block writes and vice versa.
     Provides consistent, non-locking reads for REPEATABLE READ and READ COMMITTED.
     Prevents dirty reads without heavy locking overhead.
     Enables point-in-time consistent snapshots within a transaction.
  

   Quick Example — MVCC in Action
-- Session A (REPEATABLE READ)
START TRANSACTION;
SELECT balance FROM accounts WHERE acc_id = 1;   -- sees 1000 (snapshot)

-- Session B (meanwhile)
UPDATE accounts SET balance = 2000 WHERE acc_id = 1;
COMMIT;

-- Session A reads again -> STILL sees 1000, thanks to MVCC snapshot
SELECT balance FROM accounts WHERE acc_id = 1;   -- 1000 (consistent read)

-- A locking read bypasses MVCC and sees the latest committed value
SELECT balance FROM accounts WHERE acc_id = 1 FOR UPDATE;  -- 2000
COMMIT;

  
     Interviewer tip: The one-liner they want — "MVCC lets InnoDB keep multiple row versions so readers see a consistent snapshot without locking, while writers modify data concurrently. It uses hidden transaction IDs, undo logs, and read views to decide row visibility."

Read Type	Uses MVCC?	Example
Consistent (snapshot) read	Yes — reads a version, no locks	Plain `SELECT`
Locking read	No — reads latest, takes locks	`SELECT ... FOR UPDATE`

What is MVCC (Multi-Version Concurrency Control) in MySQL?

Answer with Explanation

What is MVCC (Multi-Version Concurrency Control) in MySQL?

How MVCC Works Under the Hood

Consistent vs Locking Reads

Benefits of MVCC

Quick Example — MVCC in Action