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

This article mainly explains the "MySQL multi-version concurrency control MVCC implementation method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "MySQL multi-version concurrency control MVCC implementation method is what" it!

What is MVCC?

MVCC is multi-version concurrency control.

MySQL's transactional storage engine improves concurrency performance through multi-version concurrency control (MVCC).

MVCC can be thought of as a variant of row-level locking, but it avoids locking operations in most cases and implements non-blocking read operations, so the overhead is lower.

MVCC is realized by saving a snapshot of the data at a certain point in time. The core idea is to save the historical version of the data and to realize the concurrency control of the database through the management of multiple versions of the data rows.

In this way, we can determine whether the data is displayed by comparing the version number, and the isolation effect of the transaction can be ensured without locking when reading the data.

The realization of MVCC

In fact, InnoDB adds three hidden fields to each row of records:

ROW_ID: row ID, which is monotonously incremented as a new row is inserted, and the column is not included if there is a primary key.

TRX_ID: the transaction ID that records the transaction that inserts or updates the row.

ROLL_PTR: rollback the pointer to the undo log record. Each time a change is made to a record, the column stores a pointer through which the pre-modified information of the record can be found. When a record is modified multiple times, there will be multiple versions of the record, forming a concept similar to a version chain through ROLL_PTR links.

Take the RR level as an example:

Each time a transaction is opened, the system assigns a transaction Id to the transaction. When the transaction executes the first select statement, it generates a transaction snapshot ReadView at the current point in time, which mainly contains the following attributes:

M_ids: represents the transaction id list of uncommitted read and write transactions in the current system when the ReadView is generated.

Min_trx_id: indicates the smallest transaction id of the uncommitted read and write transactions in the current system when the ReadView is generated, that is, the minimum value in m_ids.

Max_trx_id: represents the id value that should be assigned to the next transaction in the system when the ReadView is generated.

Creator_trx_id: represents the transaction id of the transaction when the ReadView is generated.

With this ReadView, when accessing a record, you only need to follow the following steps to determine whether a version of the record is visible:

Trx_id = = creator_trx_id: you can access this version.

Trx_id

Max_trx_id: you cannot access this version.

Min_trx_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.