High-performance MySQL learning notes-Multi-version Concurrency Control Algorithm

Autocommit

MySQL is automatically submitted 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;

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 handles the lock at the isolation level.

However, InnoDB also supports explicit locks:

Select... for update

Select... lock in share mode

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:

The snapshot that stores data in a transaction. This means that a view of Data Consistency can be seen in a transaction without worrying about how long the transaction will run, it also means that the data in the same table may be different for different transactions at the same time.

Basic Features of MVCC:

• Each row of data has a version, which is updated each time the data is updated.
• When the modification is made, the current version is copied and modified without 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:

Two additional hidden fields are saved in each row: the version number when the current row is created and the version number when it is deleted (may be blank ). 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.

 

However, there is another saying on the Internet:

The InnoDB implementation method is as follows:

• The transaction modifies the original data in the form of exclusive locks.
• Store the data before modification in the UNDO log, and associate it with the master data through the rollback pointer.
• If the modification succeeds (COMMIT), nothing is done. If the modification fails, the data in the UNDO log is restored (rollback)

The most essential difference between the two is: Do I have to lock the data when I modify it? Isn't it MVCC if I lock it? The implementation of InnoDB is really 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. Two rows of data are modified, but to ensure consistency, there is no difference with modifying the data in the two distributed systems. 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.