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

This article mainly explains "the implementation principle of MYSQL MVCC". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the implementation principle of MYSQL MVCC".

MVCC is first produced for concurrency (MVCC multi version Concurrency control), and because of concurrent transactions, 2PL 2 segment locks are used. Draw a diagram below to see the two segments of locks.

2 Phase lock contains two segments, expansion and contraction, but in practice, this and the two stages can actually be distinguished by commit rollback, before which is the Growing phase, and after that all belong to shrink.

Based on the principle of two-stage lock, the first two kinds of lock S X lock are produced. The S lock is used for reading. When the record is loaded with S lock, the data of the relevant record row can not be updated, but other S locks can be added to read the data. X lock is that when the record is updated, there can be no other X lock, or S lock can be locked on this record.

The setting of such a lock gives rise to some controversy, and the performance of the database in this way is poor. Then how to put forward a method that can improve the performance of the system under this theory becomes important.

Developers have proposed a multi-version control method to reduce the performance problems caused by locking problems, which is the origin of MVCC.

For multi-version control, three hidden fields will be added to each row of records at design time, the transaction used by DB_TRX_ID to record this row currently uses its transaction ID, and DB_ROLL_PTR records the relationship between this record and UNDO spatial records. Fortunately, when the records are rolled back, the relationship between rollback segments and records is mapped.

Where DB_TRX_ID is the latest ID number of the retention transaction, and DB_ROLL_PTR refers to the information before the modification in UNDO LOG. (there are two versions, 1 new modified record and 2 unmodified record.) Multi-version control is only supported in RR and RC MVCC.

In the InnoDB multi-version control scheme, when you delete a row using an SQL statement, it is not immediately physically deleted from the database. InnoDB physically deletes the corresponding rows and their index records only when the update undo log records written for deletion are discarded. This delete operation, called cleanup, is very fast and usually uses the same chronological order as the SQL statement that performed the delete operation.

From which we can deduce that UNDO LOG must be carried out before the transaction commit.

1 data proposed to be modified

2 refresh the information before data modification to UNDO LOG

3 update the data to be updated

4 drop the information of UNDO LOG to disk

5 BINLOG record

6 transaction commit

Note: REDO LOG and various BUFFER are not discussed here.

In multi-version control, the data update between the clustered index and the secondary INDEX is different. When the secondary index column is updated, the old secondary index record is deleted, the new record is inserted, and finally the record marked by deletion is cleared. When the secondary index record is deleted or the secondary index page is updated by update's transaction, InnoDB looks for the database record in the clustered index. In a clustered index, check the DB_TRX_ID of the record, and if the record is modified after the read transaction starts, retrieve the correct version of the record from the undo log.

Therefore, in multi-version control, UNDO LOG plays an irreplaceable role, in the transaction is not committed, the data is read, UNDO LOG will provide the record information at that time, and the hidden fields in the rows of the table will control multiple versions is a key design.

At the same time, if the transaction is relatively large at one time, for example, UPDATE will occupy more UNDO LOG space, and if the size and frequency of the updated transaction is too much, it may also cause the overall data performance to be poor, so controlling the size of the transaction is very important to the performance of the overall system.

When it comes to the biggest original intention of MVCC, I think

1 Reading does not affect writing

2. Writing does not affect reading.

It is the most important function of MVCC.

Thank you for your reading, the above is the content of "the implementation principle of MYSQL MVCC", after the study of this article, I believe you have a deeper understanding of the implementation principle of MYSQL MVCC, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.