Multiversion Concurrency Control (MVCC)
Concurrency Control is a method for maintaining the execution of concurrent operations to prevent any inconsistencies or read/write conflicts in a database. One type of Concurrency Control is Multiversion Concurrency Control.
What is Multiversion Concurrency Control (MVCC)?
Multiversion Concurrency Control (MVCC) is a technique used to optimize databases by solving concurrency issues and data locking by maintaining old versions of the database.
Student Database
User ID | First Name | Last Name | Degree |
1 | David | Brown | Business Administration |
v1 2 | John | Dan | Computer Science |
3 | Seth | Grey | English Literature |
v2 4 | John | Dan | Software Engineering |
A student database following MVCC
What is the need for MVCC?
A user can either read from a database, write on it or do both. Still, an issue witnessed while writing (updating) databases is that it locks the data, which causes concurrency problems and makes it difficult for the readers to view the updated data. A reader may see only a partial update of the data or an inconsistent version. These concurrency issues may cause failed transactions and deadlocks. For the prevention of such issues, we use the Multiversion Concurrency Control.
Example
The table of the student database is given below:
Student Database
User ID | First Name | Last Name | Degree |
1 | David | Brown | Business Administration |
2 | John | Dan | Computer Science |
3 | Seth | Grey | English Literature |
When trying to update row 2 from the Student database, it can cause a lock for the readers as they might not be able to view the complete update.
Working of MVCC
The read and writes done to the database are not interrupted by each other. This is prevented by assigning each record the user is updating a version number. Whenever a user tries to read data from the database, the data with the highest version number is used. The write operations occur on a copy of the database, and the updates made on it are saved as a separate new record with a new incremented version number. The user can still view all the previous versions of data from the database. The database will now have multiple versions of data, which will not be seen by other users of the DBMS until a transaction is committed.
Example
Student Database
User ID | First Name | Last Name | Degree |
1 | David | Brown | Business Administration |
v1 2 | John | Dan | Computer Science |
3 | Seth | Grey | English Literature |
Updating row 2 from the Student Database, it has been assigned a version number, v1. After updating:
Student Database
User ID | First Name | Last Name | Degree |
1 | David | Brown | Business Administration |
v1 2 | John | Dan | Computer Science |
3 | Seth | Grey | English Literature |
v2 4 | John | Dan | Software Engineering |
The updated data of our row 2 is now added to the Student Database as a new row, row 4 with version number v2.
Pros of MVCC
Some of the pros of using MVCC are:
Provides concurrency by allowing multiple transactions at the same time.
Each transaction is independent of the other.
Gives multiple versions of the database that provides in-time recovery.
Cons of MVCC
MVCC needs more storage and can be more complex than normal databases.
They Can turn out to be slower due to a number of queries processing at the same time.
