Mysql topic|If I knew that MVCC could learn this way, I wouldn't have been abused so badly by the interviewer!

Insert picture description here

What is MVCC?

MVCC is the abbreviation of Multi-Version Concurrency Control.

What problem does MVCC solve?

Insert picture description here

We know that there are four transaction isolation levels in mysql: read uncommitted, read committed, repeatable read and serial read.
Among the four isolation levels, repeatable reading is achieved through MVCC. Through MVCC, it can be ensured that the data read each time is the same after the transaction is started; but it cannot solve the problem of phantom reading. Fortunately, mysql uses gap locks to solve the phantom reading that occurs at the repeatable read level. problem.

MVCC realization principle

MVCC is mainly realized with the help of mysql's undo log and consistent view (snapshot).

The undo log records the transaction ID associated with the data in the process of change;

The consistency view (snapshot) saves a snapshot of the data after the thread starts a transaction, and records the status of the current transaction.

So how does MVCC achieve repeatable reading through undolog and consistent views?

First of all, let's think about such a question, in the repeatable read mode, what will happen after a transaction is opened:

  • Can see the data generated by all committed transactions before the transaction is started
  • Cannot see the data generated by uncommitted transactions

Assuming that each transaction has its own transaction ID, and this id is incremented, the transaction ID created later is greater than the transaction ID created first

So if you want to implement such a scenario, after opening the transaction, you need to save the following two data states:

Uncommitted transactions as an array un_commit[], arranged in order

Generate a transaction ID MAX_ID that will be allocated next

After the preparations are done, let's first introduce how mysql stores the bottom layer of mysql when adding, deleting and modifying data.

How does mysql record the data we add, delete and modify?

mysql adds three pseudo-fields for each piece of data in the undolog at the bottom: create transaction ID, whether to delete the mark (default is no), and the previous version pointer

The data records are arranged from top to bottom according to the data update time. Here, for the convenience of writing, the arrangement order is changed. Please pay attention to the distinction.

  • Initial structure
IdNametxc_iddelete or notPrevious version pointer
1yang100Falseair
  • Modify name= zhang
IdNametxc_iddelete or notPrevious version pointer
1yang100Falseair
1zhang200FalseAddress 1
  • Add id=2
IdNametxc_iddelete or notPrevious version pointer
1yang100Falseair
1zhang200FalseAddress 1
2lisi300Falseair
  • Delete id=2
IdNametxc_iddelete or notPrevious version pointer
1yang100Falseair
1zhang200FalseAddress 1
2lisi300Falseair
2lisi400trueAddress 2

Regardless of whether it is added or deleted or modified, it is a copy of the data, rather than operating on the original data. This will eventually form a data link, which is very suitable for snapshots.

Through the above description, everyone should have a general understanding of how mysql stores our data link through undolog. Now we return to the topic: How does MVCC find our data through undolog to achieve repeatable reading?

How does MVCC query the data we want to ensure repeatable reading?

As mentioned earlier, mysql will generate a consistent view after opening a transaction. In fact, for the program, it is recording the current data point:

Uncommitted transactions make an array un_commit[], arranged in order

Generate a transaction ID MAX_ID that will be allocated next time

ok, now we use these two sets of data to find the data with id 1

Assuming that the currently assigned transaction ID is 300 and there are currently two uncommitted transactions [100,200], we will now simulate the search process

Initial state
IdNametxc_iddelete or notPrevious version pointer
1yang50Falseair
After A starts the transaction, it is the first time to find

When the first select statement is executed, the system assigns a transaction ID 300. At this time, there are two uncommitted transactions 100 and 200. At present, I want to find the record with id 1.

  • Compare the first one and extract the creation transaction id=50. After the comparison, it is found that the creation transaction id is less than the current transaction ID=300, and then go to the next step
  • If it is judged that the created transaction id is less than the smallest uncommitted transaction id=100, it can be considered that the current piece of data has been committed before the transaction is started, so this piece of data is returned.
  • Find complete
At this time, the transaction ID=100 modified the data of id=1, and the transaction was submitted

The data at this time looks like this:

IdNametxc_iddelete or notPrevious version pointer
1zhang100FalseAddress 1
1yang50Falseair
Transaction A performs a second lookup at this time

Look down

  • Extract the first piece of data and determine that the created transaction id=100 is less than the current transaction ID=300, then enter the next judgment
  • The judgment found that the transaction id=100 is in the uncommitted array [100,200], so it is invisible to the current transaction, enter the next judgment
  • Extract the address of the previous version pointer and locate the data
  • The comparison found 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, the transaction ID=200 deletes the data with id=1, and the transaction is not committed

The data now looks like this:

IdNametxc_iddelete or notPrevious version pointer
1zhang200trueAddress 2
1zhang100FalseAddress 1
1yang50Falseair
Transaction A performs a second lookup at this time

The search process is the same as above, the data record generated when transaction id=50 is finally located

A transaction will update the data view after the update operation

Uncommitted array: [200], current pre-allocated transaction ID=400

After A starts the transaction, perform the first query

Generate data view savepoint:
uncommitted array: [200], current pre-allocated transaction ID=400

  • From the first comparison, it is found that the transaction Id=200 is in the array of uncommitted transactions, and the following record is found according to address 2.
  • If the created transaction id=100 is less than the smallest uncommitted transaction id=200, this data will be returned.

Note: In all the search processes, after matching the final visible data, it is also necessary to determine whether the deletion mark of the data has been marked as deleted. If it is marked as deleted, this piece of data is not returned and the downward query is terminated! ! !

Welfare broadcast

Follow the WeChat public account "AI Coder" to receive interview materials and the latest full set of microservice tutorials

Insert picture description here