High-performance MySQL Reading Notes-Multi-version concurrency control algorithm, high-performance mysql

High-performance MySQL Reading Notes-Multi-version concurrency control algorithm, high-performance mysql
1. AUTOCOMMIT

Mysql uses automatic submission by default. You can view and modify it using the following command:

Mysql> show variables like 'autocommit ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Autocommit | ON |
+ --------------- + ------- +
1 row in set (0.00 sec)
Mysql> set autocommit = 1;

2. Implicit lock explicit lock

When InnoDB starts a transaction, it obtains the implicit lock and releases the lock when the transaction is committed or rolled back. InnoDB automatically locks the lock as needed at the isolation level.

However, InnoDB also supports explicit locks:

SELECT... FOR UPDATE

SELECT... LOCK IN SHARE MODE

This is implemented at the server layer and has nothing to do with the storage engine. In this book, we recommend that you do not explicitly execute lock tables unless AUTOCOMMIT is disabled and LOCK_TABLES can be used, no matter What storage engine is used.

3. Multi-version Concurrency control (Multiversion Concurrency Controll MVCC)

First:

MVCC is not unique to MySql. It is used in Oracle and PostgreSQL.

MVCC does not simply use row-level locking, but uses row-level locking ).

The basic principle of MVCC is:

MVCC is implemented by saving snapshots of data at a certain time point. This means that no matter how long a transaction runs, the view of Data Consistency can be seen in the same transaction. Depending on the start time of the transaction, it also means that the data in the same table seen by different transactions at the same time may be different.

Basic Features of MVCC:

• Each row of data has a version, which is updated every time the data is updated.
• When the modification is made, the current version is copied and modified without any interference between transactions.
• Compare the version number when saving. If it succeeds (commit), it overwrites the original record; if it fails, it discards copy (rollback)

Implementation Policy of InnoDB Storage engine MVCC:

Save two hidden columns in each row: the version number when the current row is created and the version number when it is deleted (it may be blank ). The version number here is not the actual time value, but the system version number. The system version number increases automatically each time a new transaction starts. The system version number at the start of the transaction is used as the transaction version number to compare with the version number of each row record.

Each transaction has its own version number, so that when performing the CRUD operation in the transaction, the version number is compared to achieve the purpose of data version control. For more information, see the following section.

　　

The specific operations of MVCC are as follows:

SELECT: InnoDB checks each row of records based on the following two conditions:

1) InnoDB only looks for the data row whose version is earlier than the current transaction version (that is, the system version number of the row is smaller than or equal to the system version number of the transaction), so that the row read by the transaction can be ensured, it only exists before the start of the transaction, or it is inserted or modified by the transaction itself.

2) The row deletion version is either undefined or later than the current transaction version. This ensures that the row read by the transaction is not deleted before the transaction starts.

INSERT: InnoDB saves the current system version number as the row version number for each newly inserted row.

DELETE: InnoDB saves the current system version number for each row to be deleted as the row deletion identifier.

UPDATE: InnoDB inserts a new row, saves the current system version number as the row version number, and saves the row with the original version number as the deletion identifier.

Save the two additional system versions so that most operations do not need to be locked. This design makes the counting data operation very simple, with good performance, and ensures that only the rows meeting the standards can be read. The disadvantage is that each row of records requires additional storage space, more row checks, and some additional maintenance work.

MVCC only works at the repeatable read and read commited isolation levels. The other two isolation levels are not compatible with MVCC.

The implementation of Innodb is not MVCC, because it does not implement multi-version coexistence of the core. The content in the undo log is only the result of serialization and records the process of multiple transactions, does not belong to multi-version coexistence. However, the ideal MVCC is hard to implement. When a transaction only modifies one row of records and uses the ideal MVCC mode, the transaction can be rolled back by comparing the version number; however, when a transaction affects multiple rows of data, the ideal MVCC data is powerless.
For example, if Transaciton1 executes the desired MVCC and Row1 is modified successfully, but Row2 fails to be modified, Row1 needs to be rolled back. However, because Row1 is not locked, its data may be modified by transaction2, if the Row1 content is rolled back at this time, the modification result of Transaction2 will be damaged, resulting in Transaction2 violating ACID. The root cause of the difficulty in implementing the ideal MVCC is the attempt to replace the second commit with an optimistic lock. To ensure the consistency of the two rows of data, it is no different from modifying the data in the two distributed systems. The second commit is the only way to ensure consistency in this scenario. The essence of the second commit is locking. The essence of optimistic locks is to eliminate locks, which are in conflict. Therefore, the ideal MVCC cannot be applied in practice. Innodb only uses the MVCC name, read is not blocked.