In this lesson, you will learn how multi-version concurrency control (MVCC) enables non-blocking reads by maintaining historical snapshots of data rows.
The Problem with Traditional Locking
In older database systems, if one transaction updated a row, it would place an exclusive lock on that data. This meant any other query attempting to read that same row would be forced to wait until the update was finished. In a high-concurrency environment, this causes a performance bottleneck where readers and writers are constantly fighting for access.
How MVCC Solves This
MVCC solves this by treating data as immutable versions. Instead of overwriting a row in place, the database engine creates a new version of the row whenever an update occurs.
When a transaction needs to read data, the engine doesn't just look for "the" row; it looks for the specific version that was valid at the time the transaction started. Because the old version of the data remains untouched, a reader can continue to access the previous version of the row while a writer simultaneously generates a new version.
A Simplified Versioning Illustration
Imagine a table storing an AccountBalance. Internally, the engine tracks version metadata (like xmin for the creating transaction ID and xmax for the deleting one):
| RowID | Balance | xmin (Created) | xmax (Deleted) |
|---|---|---|---|
| 101 | 500 | 50 | NULL |
| 101 | 750 | 51 | NULL |
In this scenario:
- A transaction that started at timestamp 50 sees a balance of
500(it ignores any row wherexmin> 50). - A transaction that started at timestamp 52 sees
750because it can see the most recent row created by transaction 51.
Both transactions can work simultaneously without blocking each other. The old row (with balance 500) eventually becomes eligible for "garbage collection" or vacuuming once no active transactions need that version anymore. This allows the engine to provide a consistent, "snapshot" view of the database without requiring heavy locks that halt read operations.
Key Takeaway: MVCC enables high performance by allowing readers to access historical row versions, ensuring that writers do not block readers and vice-versa.