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

Editor to share with you what the database MVCC is, I hope you will learn a lot after reading this article, let's discuss it together!

What is MVCC?

The full name Multi-Version Concurrency Control, that is, multi-version concurrency control, is mainly to improve the concurrency performance of the database. The following articles revolve around the InnoDB engine because myIsam does not support transactions.

When a read and write request occurs for the same row of data, the lock will be blocked. But mvcc handles read-write requests in a better way, so that there is no lock in the event of a read-write request conflict.

This read refers to the snapshot read, not the current read, which is a locking operation and a pessimistic lock.

So how on earth does it read-write without locking? what the heck is snapshot reading and current reading? follow your sweet brother and read on.

What the heck are current reading and snapshot reading?

What are current reads and snapshot reads under MySQL InnoDB?

Current read

The database records it reads are the latest version and will lock the data currently read to prevent other transactions from modifying the data. It's an operation of pessimistic locks.

The following operations are currently read:

Select lock in share mode (shared lock)

Select for update (exclusive lock)

Update (exclusive lock)

Insert (exclusive lock)

Delete (exclusive lock)

Serialized transaction isolation level

Snapshot read

The implementation of snapshot reading is based on multi-version concurrency control, that is, MVCC. Since it is a multi-version, the data read by the snapshot is not necessarily the latest data, but may be the data of previous historical versions.

The following actions are snapshot reads:

Unlocked select operations (note: transaction level is not serialization) relationship between snapshot reads and mvcc

MVCCC is an abstract concept of "maintaining multiple versions of a data so that there is no conflict between read and write operations".

This concept requires specific functions to implement, and this specific implementation is snapshot reading. (the specific implementation is described below)

After listening to the intimate elder brother's explanation, the toilet opened instantly.

Database concurrency scenario

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.

What concurrency issues does MVCC solve?

The lock-free concurrency control used by mvcc to resolve read-write conflicts is to allocate one-way growing timestamps to transactions. A version is saved for each data modification, and the version is associated with the transaction timestamp.

The read operation reads only the database snapshot before the transaction starts.

The problem is solved as follows:

Concurrent read-write: it can be achieved that the read operation does not block the write operation, and the write operation does not block the read operation.

Solve the transaction isolation problems such as dirty read, phantom read, non-repeatable read, but can not solve the above write-write update loss problem.

So there are the following combinations to improve concurrency performance:

MVCC + pessimistic lock: MVCC solves read and write conflicts, pessimistic lock solves write conflicts

MVCC + optimistic lock: MVCC solves read-write conflicts, optimistic locks resolve write-write conflicts

The realization principle of MVCC

Its implementation principle is mainly realized by version chain, undo log and Read View.

Edition chain

For every row of data in our database, in addition to the data we see with the naked eye, there are several hidden fields that can only be seen with the sky's eyes open. They are db_trx_id, db_roll_pointer and db_row_id.

Db_trx_id

6byte, recently modified (modified / inserted) transaction ID: record the transaction ID that created this record / last modified it.

Db_roll_pointer (version chain key)

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

Db_row_id

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

There is actually a deleted flag hidden field. The fact that the record is updated or deleted does not mean that it is really deleted, but that the deletion flag has changed.

As shown in the figure above, db_row_id is the only implicit primary key generated by the database by default, db_trx_id is the transaction ID that currently operates the record, and db_roll_pointer is a rollback pointer used to cooperate with the undo log and point to the previous version.

Every time a change is made to the database record, a undo log is recorded, and each undo log also has a roll_pointer attribute (the undo log corresponding to the INSERT operation does not have this attribute, because the record does not have an earlier version). These undo logs can be connected to form a linked list, so the situation is like the following figure:

After each update to the record, the old value will be put in a undo log. Even for an old version of the record, with the increase in the number of updates, all versions will be linked into a linked list by the roll_pointer attribute. We call this linked list as the version chain, and the header node of the version chain is the latest value of the current record. In addition, each version contains the corresponding transaction id when the version was generated, which is important and is used when determining version visibility based on ReadView.

Undo log

Undo log is mainly used to record the log before the data is modified, and the data will be copied to the undo log before the table information is modified.

When the transaction is rolled back, the data can be restored through the log in undo log.

The purpose of Undo log

The atomicity and consistency of the transaction during rollback are guaranteed, and the data of undo log can be recovered when the transaction is rolled back.

For the data used for MVCC snapshot reading, in MVCC multi-version control, by reading the historical version data of undo log, different transaction version numbers can have their own independent snapshot data version.

There are two main types of undo log:

Insert undo log

Represents the undo log generated by a transaction when a new insert record is created, which is needed only when the transaction is rolled back and can be discarded immediately after the transaction is committed

Update undo log (Primary)

Undo log generated by a transaction during update or delete; required not only for transaction rollback, but also for snapshot read

Therefore, it cannot be deleted casually. Only when the log is not involved in fast read or transaction rollback, the corresponding log will be cleared by the purge thread.

Read View (read View)

The read view (Read View) produced when the transaction performs a snapshot read generates a current snapshot of the database system at the moment of the snapshot read performed by the transaction.

Record and maintain the ID of the current active transaction of the system (without commit, when each transaction is opened, an ID is assigned. This ID is incremented, so the newer the transaction, the higher the ID value), which is the id list of other transactions in the system that should not be seen by this transaction.

Read View is mainly used for visibility judgment, that is, when a transaction performs a snapshot read, create a Read View read view of the record and compare it to a condition to determine which version of the data the current transaction can see. It may be either the latest data or some version of the data in the undo log recorded by the row.

Several attributes of Read View

Trx_ids: current system active (uncommitted) transaction version number collection.

Low_limit_id: "current system maximum transaction version number + 1" when creating the current read view.

Up_limit_id: "the system is in an active transaction minimum version number" when the current read view was created

Creator_trx_id: the transaction version number that created the current read view

Read View visibility judgment condition

Db_trx_id

< up_limit_id || db_trx_id == creator_trx_id（显示） 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。 或者数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。 db_trx_id >

= low_limit_id (not shown)

If the data transaction ID is greater than the maximum transaction ID of the current system in the read view, the data is generated after the current read view is created, so the data is not displayed. If it is less than, proceed to the next judgment.

Whether db_trx_id is in active transaction (trx_ids)

Does not exist: it means that the transaction is already commit when the read view is generated, and the data in this case can be displayed.

Already exists: it represents my Read View generation time, your transaction is still active, there is no Commit, your modified data, my current transaction is also invisible.

MVCC and transaction isolation level

The Read View described above is used to support the implementation of RC (Read Committed, read commit) and RR (Repeatable Read, repeatable readable) isolation levels.

Timing of RR and RC generation

Under the RC isolation level, each snapshot read generates and gets the latest Read View

Under the RR isolation level, the Read View is created only by the first snapshot read in the same transaction, and then the snapshot read acquires the same Read View, so the query result of a transaction is the same every time.

Solve the problem of phantom reading

Snapshot read: it is controlled by MVCC without locking. Add, delete, modify, check and other operations according to the "grammar" stipulated in MVCC to avoid illusory reading.

Current read: solve the problem through next-key lock (row lock + gap lock).

Differences between InnoDB Snapshot reads at RC and RR levels

The first snapshot read of a record by a transaction at the RR level creates a snapshot and a Read View to record other transactions active in the current system, and then the snapshot read is still called using the same Read View, so as long as the current transaction uses the same snapshot read before the other transaction commits the update, then all subsequent snapshot reads use the same Read View So subsequent changes are not visible.

That is, at the RR level, when a snapshot read generates Read View, Read View takes a snapshot of all other active transactions at this time, and the modifications of these transactions are not visible to the current transaction. Changes made by firms created earlier than Read View are visible.

And at the RC level, in transactions, each snapshot read generates a new snapshot and Read View, which is why we can see updates committed by other transactions in transactions at the RC level.

Summary

From the above description, we can see that the so-called MVCC refers to the process of using READ COMMITTD and REPEATABLE READ transactions to access the recorded version chain when performing ordinary SEELCT operations, so that the read-write and write-read operations of different transactions can be executed concurrently, thus improving system performance.

After reading this article, I believe you have a certain understanding of the database MVCC, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

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.