MVCC: Read and Write Data Without Blocking Each Other
MVCC avoids slow, traditional locks by giving each transaction its own consistent data snapshot. This allows readers and writers to work at the same time without blocking each other, boosting performance in databases like PostgreSQL.
Why it exists
Traditional database concurrency models used locks. If a transaction needed to write to a row, it locked it, forcing any other transaction trying to read or write that same row to wait. In systems with many users, this constant waiting, known as lock contention, creates major performance bottlenecks.
The mental model
Think of MVCC like using Git for your database rows. Instead of everyone editing the single 'live' version of the data, each transaction gets its own 'branch' or snapshot of the database from the moment it began. It works on this private version. When it commits, the database creates a new version of the changed rows without having blocked others who were just reading the old one.
How it works
When a transaction starts, it sees a snapshot of the database at that instant. If another transaction updates a row, the database doesn't overwrite the data. Instead, it creates a new version of that row and marks the old one as obsolete for future transactions. Your transaction, however, continues to see the original version it started with, ensuring a consistent view and preventing it from seeing partial updates. Reading never blocks writing because readers look at old versions while writers create new ones.
When to use it
MVCC is the default and preferred concurrency model in systems like PostgreSQL. It is ideal for most OLTP (Online Transaction Processing) workloads where many users are reading and writing data concurrently. It excels in web applications, e-commerce platforms, and any system where read and write performance under load is critical.
When not to use it
While MVCC is generally superior, you might still use explicit locks for specific application logic that goes beyond simple data consistency. For example, ensuring only one process can claim a batch of jobs might be simpler with an advisory lock. However, for general data access, relying on MVCC and its transaction isolation levels is almost always the more performant choice.
One canonical example
A user starts a transaction to run a long, complex analytics query on a sales table. While this query is running, another transaction updates a specific sale record in that same table. With MVCC, the analytics query continues running on its original data snapshot, unaffected by the update. The update transaction completes quickly because it doesn't have to wait for the long query to finish. Both operations succeed without blocking each other.
Interview question
Which of the following best describes the main advantage of Multi-Version Concurrency Control (MVCC)?
- a.It minimizes storage requirements by maintaining only the latest committed state of each data record.
- b.It completely removes the necessity for any locking mechanisms within the database.
- c.It ensures that all transactions always operate on the most up-to-date version of data.
- d.It enables read and write operations to occur concurrently without blocking each other.Correct
Why? this is the answer
The card explicitly states MVCC allows readers and writers to work concurrently without blocking each other. Option C is incorrect because transactions operate on a consistent snapshot from their start, not necessarily the absolute latest data.
Just read this? Test yourself on what you have been reading.
Read the original → postgresql.org
- #databases
- #architecture
- #concurrency
- #postgresql
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles