What are the knowledge points of Mysql MVCC multi-version concurrency control?

1. MVCC

Multi-Version Concurrency Control (MVCC) refers to.... MVCC is a concurrency control method. It is generally used in database management systems to achieve concurrent access to the database and to implement transactional memory in programming languages.

The implementation of MVCC in MySQL InnoDB is mainly to improve database concurrency performance and use a better way to handle read and write conflicts, so that even if there are > read and write conflicts, it can be done No locking, non-blocking concurrent reading.

2. Current reading

Operations such as select lock in share mode (shared lock), select for update; update, insert, delete (exclusive lock) are all current operations. Reading, why is it called current reading? That is, it reads the latest version of the record. When reading, it must also ensure that other concurrent transactions cannot modify the current record, and the read record will be locked.

3. Snapshot reading (improves the concurrent query capability of the database)

Select operation without locking is snapshot reading, that is, non-blocking reading without locking; the premise of snapshot reading is isolation The level is not the serial level. Snapshot reads at the serial level will degenerate into current reads; the reason why snapshot reads occur is based on the consideration of improving concurrency performance. The implementation of snapshot reads is based on multi-version concurrency control, that is, MVCC. It is considered that MVCC is a variant of row lock, but in many cases it avoids locking operations and reduces overhead; since it is based on multiple versions, that is, what the snapshot read may not necessarily read is the latest version of the data, but some It 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 in read and write operations , Snapshot read is a non-blocking read function of MySQL to implement MVCC. The specific implementation of the MVCC module in MySQL is implemented by three implicit fields, undo log, and read view.

5. MVCC implementation principle

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

Hidden fields

In addition to our custom fields, row records also have DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID and other fields implicitly defined by the database

DB_TRX_ID

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

DB_ROLL_PTR

7 bytes, rollback pointer, pointing to the previous version of this record, used to cooperate with undolog, pointing to the previous old 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 a rollback log, which represents a log that is generated during insert, delete, and update operations to facilitate rollback. When an insert operation is performed, the undolog generated is only needed when the transaction is rolled back, and it is The transaction can be discarded immediately after it is committed. When performing update and delete operations, the undolog generated is not only needed when the transaction is rolled back, but also when the snapshot is read, so it cannot be deleted casually. It can only be deleted when the snapshot is read or the transaction is rolled back. When the log is not involved, the corresponding log will be cleared uniformly by the purge thread (when data is updated and deleted, the deleted_bit of the old record is only set, and the outdated record is not actually deleted, because in order to save disk space , innodb has a dedicated purge thread to clear records with

deleted_bit 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, then this Records can be cleared at a certain time)

Read View

Read View is a read view produced when a transaction performs a snapshot read operation. When the transaction performs a snapshot read, At that moment, a current snapshot of the data system will be generated, and the ID of the current active transaction in the system will be recorded and maintained. The ID value of the transaction is increasing.

6. The core idea of ​​MVCC

The core idea of ​​MVCC is: I can check the data that existed before my transaction started, even if it was modified later Or deleted. I can't find the data newly added after my transaction.

MVCC search rules: 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, two pieces of data are inserted into the Transaction1 transaction, and the transaction is submitted, and then read in the Transaction2 transaction, and two pieces of data are read.

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

As shown in the figure, the data with id 2 is deleted in the Transaction4 transaction, and then read in the Transaction2 transaction. According to the mvcc rules, the data inserted and deleted after the start of my transaction can be found , Lao Chao can still find out, so he still finds two pieces of data

As shown in the figure, in Transaction5 transaction, add a piece of data with name=Brother Tao, delete id= 1 data, modify name=Brother Tao’s ID to 1, and then read it in Transaction2. According to the mvcc rules, the data inserted and deleted after the start of my transaction can be found. Lao Yan can still find it, so he still finds two Data

Through the above demonstration, we can see that through the control of the version number, no matter whether other transactions are inserting, modifying, or deleting, the data queried by the Transaction2 transaction will not change.

The above is the detailed content of What are the knowledge points of Mysql MVCC multi-version concurrency control?. For more information, please follow other related articles on the PHP Chinese website!