Multiversion Concurrency Control (MVCC) is a mechanism that is used by InnoDB, the default storage engine in MySQL, to provide transactional consistency and support for concurrent access to the same data.
In InnoDB, MVCC is implemented by keeping multiple versions of a row in the same table. When a transaction modifies a row, it does not overwrite the original row but instead creates a new version of the row with the updated values. The original version of the row is not deleted and remains accessible to other transactions that started before the modification took place. This way, multiple transactions can access different versions of the same row concurrently, without interfering with each other.
InnoDB uses a combination of undo logs and row versions to implement MVCC. The undo logs store the old values of the row before the modification, while the row versions store the current values of the row. When a transaction is committed, the corresponding undo logs are discarded, and the new row version becomes the current version of the row. When a transaction is rolled back, the undo logs are used to restore the original row version.
To support concurrent access to different row versions, InnoDB uses a system of transaction IDs and row version timestamps. Each transaction is assigned a unique transaction ID, and each row version is assigned a timestamp that indicates when it was created. InnoDB uses these timestamps to determine which row version is visible to each transaction.
MVCC is a key feature of InnoDB that enables it to provide transactional consistency and support for concurrent access to the same data. It helps to ensure data integrity and consistency, even in high-concurrency environments.
Here’s a simple example to illustrate how MVCC works in InnoDB.
Suppose we have a table named employees with the following data:
|
1 2 3 4 |
id | name | salary ---|------|------- 1 | John | 5000 2 | Jane | 6000 |
Now, consider the following scenario:
- Transaction 1 begins and updates the salary of John from 5000 to 6000:
|
1 2 |
BEGIN; UPDATE employees SET salary = 6000 WHERE id = 1; |
2. Before Transaction 1 commits, Transaction 2 begins and retrieves the salary of John:
|
1 2 |
BEGIN; SELECT salary FROM employees WHERE id = 1; |
3. At this point, InnoDB will create a new version of the row for John with the updated salary value, but it will not overwrite the original row. Instead, it will keep both versions of the row, the original and the updated, in the table.
4. Transaction 2 retrieves the original version of the row with salary 5000, because it started before the modification made by Transaction 1.
5. Transaction 1 then commits:
|
1 |
COMMIT; |
6. At this point, the updated version of the row for John becomes the current version of the row, and the original version is discarded.
7. Finally, Transaction 2 retrieves the updated version of the row with salary 6000:
|
1 |
SELECT salary FROM employees WHERE id = 1; |
In this example, MVCC allowed both transactions to access different versions of the same row concurrently, without interfering with each other. This is a key feature of InnoDB that helps to ensure data integrity and consistency, even in high-concurrency environments.
