Multiversion concurrency control
lastmod 2021-05-15created 2015-10-254 edits
Multiversion concurrency control (MCC, MVCC), also known as multiversion concurrent execution control, is a concurrency control method commonly used by database management systems to provide concurrent access to the database and, in programming languages, to implement transactional memory.
About
Multiversion concurrency control (called MCC or MVCC) is one of the methods used to control concurrency in databases that allow concurrent access.
Consider a case where two or more people read or write data in a database at the same time. There are several concurrency control methods for reading and writing data safely in such cases. Among them, locking is the most widely used. Why? Because it is simple to implement. However, this method has the drawback of being slow, since it cannot process database requests in parallel. MVCC solves this problem with a different approach.
In the MVCC model, each user accessing data reads a snapshot of the database at the time of access. Changes made until the changes to this snapshot data are complete cannot be seen by other database users (until the transaction is committed).
Now, when a user updates data, instead of overwriting the previous data, a new version of the data is created. Instead, the changes compared to the previous version of the data are recorded. In this way, multiple versions of the data exist for a single piece of data. Users, of course, read the latest version of the data.
Because this MVCC approach does not require locking, it runs much faster than a typical RDBMS. Also, once you start reading data, you can use it without being affected even if someone else deletes or modifies that data. Instead, a system that periodically deletes unused versions of data is needed.
Because the MVCC model allows multiple versions of a single piece of data, data version conflicts can occur. Conflicts must be resolved in the application layer.
in PostgreSQL
PostgreSQL uses MVCC (Multi-Version Concurrency Control) to control database concurrency. It is responsible for allowing data to be modified and queried concurrently by multiple transactions. MVCC creates new data every time a user changes a value. Then, when committed, it marks the new data as the latest data. In this process, the old data that is no longer used is called a dead tuple. These dead tuples keep occupying PostgreSQL's storage space. So work is needed to reclaim and reuse this storage space. <- Vacuum is responsible for this.