Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the Mysql MVCC multi-version concurrency control knowledge points have, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will gain something after reading this Mysql MVCC multi-version concurrency control knowledge point. Let's take a look.

1 、 MVCC

MVCC, full name Multi-Version Concurrency Control, that is, multi-version concurrency control. MVCC is a method of concurrency control, which generally implements concurrent access to the database in the database management system and transaction memory in the programming language.

The main purpose of the implementation of MVCC in MySQL InnoDB is to improve the concurrency performance of the database and to deal with read-write conflicts in a better way, so that even if there are > read-write conflicts, it can be unlocked and non-blocking.

2. Current reading

Operations like select lock in share mode (shared lock), select for update; update, insert, delete (exclusive lock) are all current reads, why are they called current reads? That is, it reads the latest version of the record, ensures that other concurrent transactions cannot modify the current record, and locks the read record.

3. Snapshot read (improve the concurrent query ability of the database)

For example, an unlocked select operation is a snapshot read, that is, an unlocked non-blocking read; the premise of a snapshot read is that the isolation level is not a serial level, and the snapshot read at the serial level will degenerate to the current read The reason why snapshot reading occurs is based on the consideration of improving concurrency performance. The implementation of snapshot reading is based on multi-version concurrency control, that is, MVCC, which can be considered as a variant of row lock, but in many cases, it avoids locking operations and reduces overhead. 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.

4. Current read, snapshot read, MVCC relationship

MVCC multi-version concurrency control refers to maintaining multiple versions of a data so that there is no conflict between read and write operations. Snapshot reading is a non-blocking read function of MySQL to implement MVCC. The concrete implementation of MVCC module in MySQL is realized by three implicit fields, undo log and read view.

5. The principle of MVCC.

The implementation principle of mvcc mainly depends on three hidden fields in the record, undolog,read view.

Hidden field

In addition to our custom fields, row records also have fields such as DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID implicitly defined by the database.

DB_TRX_ID

6 bytes, recently modified transaction id, record the transaction id that created the record or last modified the record

DB_ROLL_PTR

7-byte, rollback pointer to the previous version of this record, used to match undolog, and point to the previous version

DB_ROW_JD

6 bytes, hidden primary key, if the data table does not have a primary key, then innodb will automatically generate a 6-byte row_id

Undo log

Undolog is called rollback log, which means that the log generated during the insert,delete,update operation is convenient for rollback. When the insert operation is performed, the generated undolog is only needed when the transaction is rolled back, and can be discarded immediately after the transaction is committed. When performing update and delete operations, the resulting undolog is needed not only when the transaction is rolled back, but also when the snapshot is read, so it cannot be deleted casually. Only when the snapshot read or transaction rollback does not involve the log will the corresponding log be cleared by the purge thread (when the data is updated and deleted, it is only to set the deleted_bit of the old record, not to really delete the obsolete record, because in order to save disk space, innodb has a special purge thread to clear the record whose deleted_bit is true, if the deleted_id of a record is true And the DB_TRX_ID is visible relative to the read view of the purge thread, so this record can be cleared at some point)

Read View

Read View is the read view produced during the snapshot read operation of the transaction. At the moment when the transaction performs snapshot read, it will generate a snapshot of the current data system, record and maintain the id of the current active transaction of the system, and the id value of the transaction is incremented.

6. The core idea of MVCC

The core idea of MVCC is that I can find the data that existed before my transaction started, even if it was modified or deleted later. I can't find the new data after my transaction.

MVCC lookup rule: you can only find data whose creation time is less than or equal to the current transaction ID and rows whose deletion time is greater than the current transaction ID (or not deleted)

As shown in the figure, insert two pieces of data in the Transaction1 transaction, commit the transaction, then read it in the Transaction2 transaction and read the two pieces of data

As shown in the figure, insert a piece of data for the old company in the Transaction3 transaction, and then read it in the Transaction2 transaction. According to the mvcc rule, the data inserted after the start of my transaction cannot be found. The creation ID of the old company is greater than 2, so only two pieces of data can be found.

As shown in the figure, delete the data with an id of 2 in the Transaction4 transaction, and then read it in the Transaction2 transaction. According to the mvcc rule, you can find the data inserted and deleted after the start of my transaction, and the boss can still find out, so there are still two pieces of data.

As shown in the figure, in the Transaction5 transaction, add a name= Tao data, delete the id=1 data, modify name= Tao's id to 1, and then read it in the Transaction2 transaction. According to the mvcc rules, you can find the data inserted and deleted after the start of my transaction. Lao Yan can still find out, so you can still find two pieces of data.

Through the above demonstration, we can see that through the control of the version number, the data queried by the Transaction2 transaction remains unchanged regardless of whether other transactions are inserted, modified, or deleted.

This is the end of the article on "what are the knowledge points of Mysql MVCC multi-version concurrency control". Thank you for reading! I believe you all have a certain understanding of "what are the knowledge points of Mysql MVCC multi-version concurrency control". If you want to learn more knowledge, you are welcome to follow the industry information channel.

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.