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

This article introduces the relevant knowledge of "what are the concurrency control MVCC knowledge points of MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MVCC

MVCC (Multi-Version Concurrency Control), that is, multiversion concurrency control. It is an important function of innodb to realize transaction concurrency and rollback. The locking mechanism can control concurrent operations, but its system overhead is high, while MVCC can replace row-level locks in most cases and use MVCC to reduce its system overhead.

The implementation is to add three additional fields to each row of the database:

DB_TRX_ID: records the transaction ID of the last transaction that inserted or updated the row

DB_ROLL_PTR: pointer to the undolog corresponding to the line change

DB_ROW_ID: monotonously increasing ID, which is the primary key ID of AUTO_INCREMENT

Snapshot read

For example, the unlocked select operation is snapshot reading, the emergence of snapshot reading is based on the consideration of improving concurrency performance, and the implementation of snapshot reading is based on multi-version concurrency control, namely MVCC. It can be considered that MVCC is a variant of row lock, in many cases, the locking operation is avoided and the overhead is reduced; since it is based on multiple versions, snapshot reading may not necessarily read the latest version of the data, but may be the previous historical version.

Current read

The current data is read, and there is no need to undo log back to the state before the transaction starts. Read is the latest version of the record, but also to ensure that other concurrent transactions can not modify the current record, will lock the read record.

There are three database concurrency scenarios, which are:

Read-read: there are no problems and no concurrency control

Read-write: there are thread safety problems, which may cause transaction isolation problems, and may encounter dirty reading, phantom reading, and unrepeatable reading.

Write-write: there are thread safety problems, and there may be update loss problems, such as the first type of update loss and the second type of update loss.

To put it bluntly, MVCC is to achieve read-write conflict without lock, and this read refers to snapshot read, not current read, which is actually a locking operation and the realization of pessimistic lock.

The emergence of MVCC is that bosses are not satisfied with using pessimistic locks to solve read-write conflicts, so there are two solutions:

MVCC + pessimistic lock

MVCC solves read-write conflicts, pessimistic locks resolve write-write conflicts

MVCC + optimistic lock

MVCC solves read-write conflicts, optimistic locks resolve write-write conflicts

Three Hidden Fields of MVCC implementation principle

DB_TRX_ID

6 bytes, recently modified (modified / inserted) transaction ID: record creates this record / the transaction ID that last modified the record

DB_ROLL_PTR

7 bytes, rollback pointer to the previous version of this record (stored in rollback segment)

DB_ROW_ID

6-byte, implied self-increasing ID (hidden primary key). If the data table does not have a primary key, InnoDB will automatically generate a clustered index in DB_ROW_ID.

Version chain / undo log

Because undo log records the old version data before the transaction, and then the rollback pointer in the row record points to the location of the old version, thus forming a version chain. Read View traverses the DB_TRX_ID of the linked list until it finds a DB_TRX_ID that meets a specific condition. So the old record where the DB_TRX_ID is located is the latest "old version" that can be seen by the current transaction.

Read View

Is a collection of all currently active transactions (transactions that have not yet been committed) when the transaction is opened. In other words, Read View is the read view (Read View) produced when the transaction performs a snapshot read operation. At the moment of the snapshot read performed by the transaction, a current snapshot of the database system is generated, and the ID of the current active transaction of the system is recorded and maintained.

Three important Read View structures:

Trx_list (I chose the name at random)

A list of values

Used to maintain the ID list of transactions that are active in the system when Read View is generated

Up_limit_id

Is the ID with the lowest transaction ID in the trx_list list

Low_limit_id

ReadView generates the next transaction ID that has not been assigned by the system at the time of generation, that is, the maximum value of the transaction ID that has occurred so far.

Why low_limit? Because it is also the minimum value of transaction ID that can be allocated by the system at the moment.

The overall flow of the MVCC implementation:

This is the end of the content of "what are the concurrency control MVCC knowledge points of MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.