What is MVCC?
MVCC stands for Multi-version Concurrency Control.
What problem does MVCC solve?
We know that there are four transaction isolation levels in mysql: read uncommitted, read committed, repeatable read, and serial read. Of the four isolation levels, repeatable reads are implemented through MVCC. MVCC ensures that the same data is read each time after the transaction is started. However, it does not solve the illusion problem. Fortunately, mysql uses gap locks to solve the illusion problem at the repeatable read level.
MVCC implementation principle
MVCC is implemented with the help of mysql’s undo log and consistency view (snapshot).
Undo log records the transaction ID associated with the data in the process of change.
Consistency view (snapshot) holds a snapshot point of data after a thread has started a transaction, recording the current transaction status.
So how does MVCC implement repeatable reads through Undolog and consistent views?
First let’s consider what happens when a transaction is started in repeatable read mode:
- You can see the data generated by all committed transactions before the transaction starts
- Data generated by uncommitted transactions cannot be seen
It is assumed that each transaction has its own transaction ID, which is incremented, and that the last transaction ID created is greater than the first
So if you want to implement such a scenario, after starting the transaction, you need to save the following two data states:
-
Uncommitted transactions are sorted in order as an array un_commit[]
-
Generate a transaction ID MAX_ID for the next to be allocated
Mysql > add, delete, and modify data
How does mysql keep track of the data we add, delete, and change?
Create transaction ID, delete flag (default no), and previous version pointer
The data records are arranged from top to bottom according to the data update time. For the convenience of writing, the order has been changed. Please pay attention to the distinction
- The initial structure
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | yang | 100 | False | empty |
- Modify the name = zhang
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | yang | 100 | False | empty |
| 1 | zhang | 200 | False | Address 1 |
- New id = 2
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | yang | 100 | False | empty |
| 1 | zhang | 200 | False | Address 1 |
| 2 | lisi | 300 | False | empty |
- Delete the id = 2
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | yang | 100 | False | empty |
| 1 | zhang | 200 | False | Address 1 |
| 2 | lisi | 300 | False | empty |
| 2 | lisi | 400 | true | Address 2 |
Whether you add, delete, or modify data, you copy the data instead of operating on the original data. In this way, a data chain is formed, which is suitable for making snapshots.
Mysql uses Undolog to store our data links, so we can get a general idea of how MVCC uses Undolog to find our data.
How does MVCC query the data we want and make it repeatable?
As mentioned earlier, mysql generates a consistent view of the current data point when a transaction is started:
-
Uncommitted transactions make an array un_commit[], sorted in order
-
Generate a transaction ID MAX_ID that will be allocated next time
Ok, now let’s use these two sets of data to find the data with ID 1
Assuming that the currently allocated transaction ID is 300 and there are currently two uncommitted transactions [100,200], we now simulate the lookup process
The initial state
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | yang | 50 | False | empty |
A Searches for the first time after the transaction is started
When the first SELECT statement is executed, the system assigns a transaction ID 300, and there are two uncommitted transactions 100,200, currently trying to find the record with ID 1
- In the first comparison, extract the created transaction ID= 50. After comparison, find that the created transaction ID is smaller than the current transaction ID=300 and proceed to the next step
- If the created transaction ID is smaller than the minimum uncommitted transaction ID =100, it can be considered that the current data has been committed before the start of the transaction, so this data is returned.
- Find the complete
At this point, the transaction ID=100 modifies the data with ID=1 and commits the transaction
The data looks like this:
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | zhang | 100 | False | Address 1 |
| 1 | yang | 50 | False | empty |
The A transaction now performs A second lookup
Look at it from the top down
- Extract the first data and judge that the created transaction ID= 100 is less than the current transaction ID=300, then enter the next judgment
- It is found that the transaction id=100 is in the uncommitted array [100,200], so the transaction is not visible to the current transaction, enter the next judgment
- Extract the address of the previous version pointer to locate the data
- Comparison shows that the transaction ID created by the current data is 50, which is less than the ID of the smallest uncommitted transaction, so this data is returned
At this time, transaction ID=200 deletes the data with ID= 1, and no transaction is committed
The data looks like this:
| Id | Name | txc_id | Whether or not to delete | Previous version pointer |
|---|---|---|---|---|
| 1 | zhang | 200 | true | Address 2 |
| 1 | zhang | 100 | False | Address 1 |
| 1 | yang | 50 | False | empty |
The A transaction now performs A second lookup
The search process is the same as above, eventually locating the data record generated when transaction ID =50
The data view is updated after the TRANSACTION A performs an UPDATE operation
Uncommitted array :[200], currently pre-allocated transaction ID=400
A Performs the first query after the transaction is enabled
Generate data view savepoint: uncommitted array :[200], current pre-allocated transaction ID=400
- If transaction Id=200 is found in the array of uncommitted transactions, the next record is found at address 2
- Create a transaction whose ID =100 is less than the smallest uncommitted transaction whose ID =200.
Note: in all the search process, after matching the final visible data, we also need to judge whether the deletion of the data has been marked as deletion state, if marked as deletion state, this data will not be returned, and terminate the downward query!!
Welfare big broadcast
Follow the wechat official account “AI Coder” to receive the interview materials and the latest full set of micro service courses