What is MVCC in databases?
SHORT ANSWER
MVCC keeps multiple versions of a row so readers see a consistent snapshot while writers create new versions. This lets reads and writes proceed without blocking each other. PostgreSQL, MySQL InnoDB and Oracle use it.
Explain it simply
Think of a shared Google Doc where you can open "the version as of 10:00" and keep reading it while others edit the live copy. Your view does not change halfway, and their edits do not wait for you.
Example
Row balance = 500, at time T1.
- Transaction A starts at T2 and reads 500.
- Transaction B updates the balance to 400 and commits at T3. The database now holds two versions: 500 (old) and 400 (new).
- Transaction A, if its isolation level is REPEATABLE READ or higher, reads again and still gets 500, from its snapshot.
- A new transaction starting after T3 sees 400.
sql
-- PostgreSQL shows hidden version information
SELECT xmin, xmax, balance FROM accounts WHERE id = 1;Consequences:
- Readers do not block writers, and writers do not block readers.
- Two writers to the same row still conflict.
- Old versions must be cleaned up later: VACUUM in PostgreSQL, purge in InnoDB. Long-running transactions delay cleanup and cause table bloat.
What to say in the interview
"MVCC stores row versions and gives each transaction a snapshot, so reads do not take locks that block writes. The cost is cleanup of old versions, and write-write conflicts still need locks."
Common mistakes
- Saying MVCC removes locking entirely. Writers still lock rows against each other.