MVCC
How a database lets readers and writers work on the same rows at the same time, by keeping more than one version.
5 minute lesson
Readers and writers in each other's way
The simple way to keep a reader from seeing half-finished work is a lock: while someone writes a row, nobody may read it. On a busy table that turns into queues. Most databases solve it differently, with MVCC: a write does not replace a row. It adds a new version, and the old one stays for those who still need it.
- Locks aloneReaders wait
- MVCCReaders read a version
- MVCC
- Multiversion concurrency control. The database keeps several versions of a row, so each transaction reads a consistent snapshot without blocking writers.
- snapshot
- The state of the data as it was at one moment, which a transaction keeps reading while others change the data.
Key terms
One row and two versions
- A reader starts
A report begins and reads a product: price 10.
- Version 1price 10
- A writer updates
Another transaction changes the price to 12. The database writes a second version of the row. Version 1 is kept.
- Version 1price 10
- Version 2price 12
- Both carry on
The report still reads version 1, the state from when it started. The writer did not wait for the report, and the report does not wait for the writer.
- Reportreads 10
- New transactionsread 12
- Clean up
Once no transaction can need version 1 any more, a background process removes it and frees the space.
- Version 1removed
- Version 2price 12
In a database with MVCC, a long report is reading the products table. Meanwhile another transaction updates the price of a product and commits.
What happens between the two?
- Neither waits for the other
- The update waits for the report
- The report waits for the update
- One of the two is cancelled
Show the answer
- Neither waits for the other
The update writes a new version of the row and leaves the old one where it is. The report goes on reading the old version, which belongs to its snapshot. Neither needs anything the other holds. This is the main benefit of MVCC: reading never blocks writing, and writing never blocks reading.
Under MVCC readers do not block writers and writers do not block readers.
A snapshot is a point in time
What a transaction reads under MVCC is a snapshot: the data as it was at one moment. A report that runs for ten minutes adds up balances from that one moment, although thousands of payments are committed while it runs. Its total is right for the moment it started.
A developer opens a transaction in a database console, runs one query and goes home without closing it. The table is updated heavily all night.
What is the effect by morning?
- Old row versions have piled up, as none could be removed
- All updates of the night have waited for the console
- The console's query has returned different rows hourly
- Nothing, as an idle transaction reads no data at all
Show the answer
- Old row versions have piled up, as none could be removed
The open transaction holds a snapshot from the evening. For all the database knows, it may still read any row as it was then, so every version replaced since must be kept. The updates themselves ran freely, but the table has grown with dead versions and queries have slowed down. Forgotten open transactions are a classic cause of this.
An open transaction pins its snapshot. Versions it might still read cannot be cleaned up.
How does MVCC let a long read and many writes run at the same time, and what does it cost?
Show the answer
A write does not overwrite a row. It creates a new version and leaves the old one in place. A reader keeps seeing the versions that were current in its snapshot, so reading never blocks writing and writing never blocks reading. The cost is the old versions: they take space until no transaction needs them, and a long-running transaction holds back the clean-up.
Recap
- MVCC keeps several versions of a row, so readers and writers do not block each other.
- A transaction reads a snapshot: the data as it was at one moment.
- Old versions are cleaned up later. Long-open transactions keep them alive and bloat the tables.