"High-performance MySQL" Reading notes-multi-version concurrency control algorithm

1.AUTOCOMMIT

MySQL uses default autocommit and can be viewed and modified by the following commands:

mysql> SHOW VARIABLES like ' autocommit ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| autocommit | On |
+---------------+-------+
1 row in Set (0.00 sec)
mysql> SET autocommit = 1;

2. Implicit lock-out lock

InnoDB acquires an implicit lock when the transaction is turned on, releases the lock when the transaction commits or rolls back, and InnoDB automatically locks as needed, depending on the isolation level.

But InnoDB also supports explicit locks:

SELECT ... For UPDATE

SELECT ... LOCK in SHARE MODE

This is implemented at the server level, regardless of the storage engine. This book recommends that, in addition to disabling autocommit, you can use Lock_tables and do not display the lock TABLES at any time, no matter what storage engine is used.

3. Multi-version concurrency control (multiversion Concurrency controll MVCC)

1th:

MVCC is not unique to MySQL, oracle,postgresql and so on are used.

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

The basic principles of MVCC are:

The implementation of MVCC is achieved by saving the snapshot of the data at a certain point in time. This means that a transaction can see a consistent view of the data in the same transaction , no matter how long it takes to run . Depending on the time at which the transaction started, it also means that the data in the same table as seen by different transactions at the same time may be different.

Basic features of MVCC:

• Each row of data has a version that is updated every time the data is updated.
• Copy out the current version at random changes, no interference between the transactions.
• Compare version number on save, overwrite original record if successful (commit), failure to discard copy (rollback)

InnoDB Storage Engine MVCC implementation strategy:

Save two additional hidden columns in each row of data: The version number at the time the current row was created and the version number when it was deleted (possibly empty). The version number here is not the actual time value, but the system version number. The system version number is automatically incremented for each new transaction that starts. The system version number at the start of a transaction is used as the version number of the transaction to compare with the version number of the query for each row of records.

Each transaction has its own version number, so that the data versioning is achieved by comparing version numbers when performing CRUD operations within a transaction. See below for specific practices.

　　

MVCC specific operation is as follows:

SELECT: InnoDB checks each row of records according to the following two criteria:

1) InnoDB only finds data rows that are earlier than the current version of the transaction (that is, the system version number of the line is less than or equal to the system version number of the transaction), which ensures that the transaction reads only the rows that existed before the start of the transaction, either by the transaction itself or by a modification.

2) The deleted version of the row is either undefined or larger than the current transaction version number. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.

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 deleted as a row delete identity.

update : innodb to insert a new row of records, save the current system version number as the line version number, while saving the system's version number as the original row as the delete identity.

Save these two additional system version numbers so that most operations can be unlocked. This design makes the data manipulation simple, performs well, and ensures that only rows that conform to the standard are read. The disadvantage is that each row of records requires additional storage space, more row checking is required, and some additional maintenance work.

MVCC only works under the two isolation levels of repeatable read and read commited, and the other two isolation levels and MVCC are incompatible.

InnoDB implementation is not MVCC, because there is no multi-version of the core coexistence, the content of undo log is only the result of serialization, the process of recording multiple transactions, does not belong to multi-version coexistence. But the ideal MVCC is difficult to implement, and when a transaction modifies only one row of records using the ideal MVCC pattern is no problem and can be rolled back by comparing the version number, but when the transaction affects multiple rows of data, the ideal MVCC is powerless.
For example, if Transaciton1 executes the desired MVCC, the modification Row1 succeeds, and the modification Row2 fails, the Row1 is rolled back, but because Row1 is not locked, the data may be modified by Transaction2, and if the contents of Row1 are rolled back at this time, Damage to the Transaction2 results, causing Transaction2 to violate acid. The fundamental reason why the ideal MVCC is difficult to achieve is the attempt to replace the two-paragraph submission with optimistic locking. Modifying two rows of data, but to ensure consistency, is no different from modifying the data in two distributed systems, while two-segment submissions are the only means of ensuring consistency in this scenario at this time. Two paragraph of the nature of the submission is locked, the essence of the optimistic lock is to eliminate the lock, the contradiction between the two, so the ideal MVCC difficult to really be applied in practice, InnoDB just borrowed MVCC the name, provides read non-blocking only.

"High-performance MySQL" Reading notes-multi-version concurrency control algorithm