Writing in the front

In the previous article on Mysql transaction isolation level and locking mechanism, we saw that Mysql can guarantee high isolation of transactions under repeatable read isolation level. The multi-version Concurrency Control (MVCC) mechanism ensures this isolation. By default, the read and write operations on a row are not mutually exclusive.

Undo log version chain and read View mechanism in detail

The undo log version chain means that after a row of data has been modified by multiple transactions successively, after each transaction is modified, Mysql will keep the undo rollback log of the data before modification, and use two hidden fields trx_ID and roll_pointer to connect these undo logs together to form a historical version chain.

As shown in the figure: 在Repeatable read isolation levelThe current transaction will be generated when any query SQL is executed when the transaction is enabledConsistency view read-view.This view is generated during the first select and does not change until the end of the transaction.The committed isolation level is regenerated each time the query (SELECT) SQL is executed if it is read,read-veiw is used when executing a queryThe smallest id in the array of uncommitted transaction ids is min_idandThe maximum transaction ID (max_id) that has been createdThe results of any SQL query in a transaction need to be compared with the latest data from the corresponding version chain (above) to get the final snapshot result.

Version chain alignment rules:

1. If the trx_id of the row falls in the green area (trx_id<min_id), the version was generated by a committed transaction, and this data is visible;

2. If the trx_id of a row is in the red area (trx_id>max_id), the version has not been started and is not visible.

Min_id <=trx_id<=max_id; min_id<=trx_id; min_id<= max_id;

A. If the trx_id of a row is in the view array (read-veiw), this version is not visible because it was generated by an uncommitted transaction.

B. If the trx_id of row is not in the view array, this version is generated by a committed transaction.

For example:

Analysis:

As shown in figure 1-13, transaction 80(trx_id 80), transaction 100(trx_id 100), transaction 200(trx_id 200), transaction 300(trx_id 300), Query 1, query 2 are performed at different times (1-13). Trx_id 80 can be considered a committed transaction.

Moment 1: Start transaction begin;

Time 2: trx_id 80 commits a transaction, trx_id 100 performs an update, and the transaction is not committed.

Time 3: trx_id 200 update operation, transaction not committed;

Time 4, 5: trx_id 300 performed an update and committed a transaction;

Read -view:([100,200],300), ([100,200]), (trx_id 300), (trx_id 300)According to the version chain comparison rules: The record version chain is as follows:



If trx_id is 300, read-view([100,200],300), trx_id is not in [100,200]. The result of the query is name300.

Moments 7, 8: trx_ID 100 update twice, but the transaction has not committed. The version chain is as follows:



Time 9: SELECT 1 performs a query operation on theRepeatable read isolation levelBecause, inPoint 6Read -view ([100,200],300),According to the version chain comparison rules: Select 1 (trx_id 100, name (name1)) from read-view; select 1 (trx_id 100, name (name1)) from read-view; select 1 (trx_id 100, name (name1)) from read-view; Similarly, it is invisible to the current transaction, and then it searches down to find the record trx_id 300, whose name is 300. By comparison, it is found that 300 is not in [100,200], that is, it is visible to the current transaction, so the query result is name300.

Time 10, 11: trx_id 100 committed the transaction, trx_id 200 performed a secondary update, but the transaction has not committed. The version chain record is as follows:



Read -view ([100,200],300) at time 12: select 1 has performed a query operation before, so the read-view is still ([100,200],300). Read -view([200],300);According to the version chain comparison rules: Trx_id 100 < trx_id 200 trx_id 100 < trx_id 200 trx_id 100 < trx_id 200 trx_id 100 = name4 Note This version is generated by committed transactions and is visible to the current transaction. That is, the query result is name2.

Time 13: Trx_ID 200 The commit operation is performed. The version chain is shown in the first figure of this article.

summary

In the delete case, which can be considered as a special case of update, a copy of the latest data in the version chain is made, and the trx_id is changed to the trx_id of the delete operation, and the (deleted_flag) bit in the record header is set to true. If the value of delete_flag is true, it indicates that the record has been deleted, and no data is returned. Note: The begin/start transaction commands are not the starting point of a transaction. The transaction is not actually started until the first modification operation is performed on the InnoDB table. The transaction IDS are assigned to mysql strictly in the order in which transactions are started. The MVCC mechanism is implemented through the read-view mechanism and the undo version chain comparison mechanism, so that different transactions can read different versions of the same data in the version chain according to the data version chain comparison rule.

Innodb engine BufferPool cache mechanism

Schematic diagram:



The bufferPool flow diagram in MySQL looks like the figure above. Specific details are not very detailed, and will be supplemented after further study and understanding of the content of this piece.