Shulou(Shulou.com)06/02 Report--

This article mainly introduces "the implementation of traditional mvcc and innodb mvcc". In daily operation, I believe that many people have doubts about the implementation of traditional mvcc and innodb mvcc. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the implementation of traditional mvcc and innodb mvcc". Next, please follow the editor to study!

According to the book "High performance mysql", the mvcc implementation should be roughly as follows:

1. There are two hidden fields behind each row of data, one field is the update ID, the other is the deletion ID, and the record content is the system version number at the time of update / deletion.

2. During the select process, data less than or equal to is filtered according to the current transaction version number.

3. The update operation will actually add a row of records instead of modifying the original data.

4. Delete is not physically deleted, but adds a version number to the deletion identification field.

However, innodb's mvcc is not the same!

In innodb, the two hidden fields are DATA_TRX_ID and DATA_ROLL_PTR (if there is no primary key, there will be another hidden primary key column. You have seen the del_flag field in some articles, but it is not clear whether it is a separate field or a bit in DATA_TRX_ID).

DATA_TRX_ID

Record the transaction ID that most recently updated this row record, with a size of 6 bytes

DATA_ROLL_PTR

Represents a pointer to the row's rollback segment (rollback segment) with a size of 7 bytes, through which InnoDB finds the previous version of the data. All the old versions of the row, organized in the form of linked lists in undo, have been searched on the Internet in the following format. It records the data modified by each version, not the reverse operation of an operation.

So, in fact, every time you do a update operation, you don't create a new copy. Instead, it first puts the data row copy into the undo log, then modifies the data row, and modifies its DATA_TRX_ID and DATA_ROLL_ID.

The column structure that supports the mvcc function is mentioned above, so let's take a look at the relationship between mysql's four transaction isolation levels and mvcc.

1. Dirty reading

It has nothing to do with mvcc. Every time the latest data of select, as long as the data of other transactions has been physically written or modified, it will read it and directly ignore the following DATA_TRX_ID.

two。 Submit for reading

In the use of mvcc, innodb introduces ReadView. When a ReadView is generated, the version numbers of all currently active transactions (both uncommitted transactions) are collected into a queue m_ids, and the current system version number + 1 is also added to the queue. Then you can find the minimum value up_limit_id and the maximum value low_limit_id of the version number. In the process of select, for a piece of data, compared with m_ids, there will be the following results.

a. Less than up_limit_id, indicating that the transaction has been committed before ReadView is opened, so it is valid data.

b. Greater than low_limit_id, it means that the thing was submitted after ReadView is enabled, and it is invalid data. At this time, you have to look up the undo log list until you find a valid one.

c. If up_limit_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.