MySQL MVCC mechanism

This article was also published in HTTPS://GITHUB.COM/ZHANGYACHEN/ZHANGYACHEN.GITHUB.IO/ISSUES/68

Row structure

Each line contains an additional three hidden fields:

• DB_TRX_ID: Transaction ID. This value is the record of the creation time and the deletion time of the row.
• Db_roll_ptr: The undo information that points to the current record entry.
• DB_ROW_ID:: A field that is monotonically incrementing as new rows are inserted. When a clustered index is automatically generated by InnoDB, the clustered index includes the value of this db_row_id, otherwise the value is not included in the clustered index.
• When the insert operation is created, the time = db_row_id, when delete time is undefined.
• When the update operation is made, the "Create Time" =db_row_id of the new row is copied, the deletion time is undefined, the old data row "creation time" is unchanged, and the deletion time = db_row_id of the transaction.
• When the delete operation, the "creation time" of the corresponding data row is not changed, the deletion time = db_row_id of the transaction.

• The select operation does not modify both, and it reads only the corresponding data.

  Read View

    dulint    low_limit_id;    /* 事务号 >= low_limit_id的记录，对于当前Read View都是不可见的 */    dulint    up_limit_id;    /* 事务号 < up_limit_id ，对于当前Read View都是可见的 */    ulint    n_trx_ids;    /* Number of cells in the trx_ids array */    dulint*    trx_ids;    /* Additional trx ids which the read should                not see: typically, these are the active                transactions at the time when the read is                serialized, except the reading transaction                itself; the trx ids in this array are in a                descending order */dulint    creator_trx_id;    /* trx id of creating transaction, or                (0, 0) used in purge */

About LOW_LIMIT_ID,UP_LIMIT_ID's understanding:
UP_LIMIT_ID: The currently committed transaction number + 1, transaction number < UP_LIMIT_ID, is visible for the current read view. It is understood that when a read view is created, the transaction that was previously committed is definitely visible to the transaction.
LOW_LIMIT_ID: The current maximum transaction number + 1, the transaction number >= low_limit_id, is not visible for the current read view. It is understood that the transaction created after the creation of the read view is definitely not visible to the transaction.

In addition, Trx_ids is the list of active transaction IDs, which is the list of transactions that are not currently committed when read view is initialized. Therefore, when the RR is read, the transaction in Trx_ids is not visible to the transaction (except for its own transaction, the modification of its own transaction for the table is of course obvious to itself). It is understood that when the RV is created, the current active transaction ID is recorded, and subsequent submissions are not visible to this transaction even if they are submitted.

Example

Steps	1	2	3
One	Begin
Two		Begin
Three	INSERT into Test (score) values (1607); Assuming the transaction number 21 at this point
Four		INSERT into Test (score) values (1607); At this point, transaction number 22
Five	Create read view at this time, up_limit_id = +, low_limit_id = 23 Active transaction list is (21,22)
Six			INSERT into Test (score) values (1620); Transaction number is 23
Seven			INSERT into Test (score) values (1621); Transaction number is 24
Eight			INSERT into Test (score) values (1622); Transaction number is 25
Nine			SELECT * from Test; At this time the up_limit_id is 21,low_limit_id to 26, the active transaction list is (21,22), so 21,22 is not visible in the active transaction list
Ten		SELECT * from Test; At this point the low_limit_id is 26,up_limit_id to 21, and the active transaction list is (21,22) 22 The transaction itself is visible. 21 is not visible in the active transactions list. 23,24 not in active transaction list, visible
Eleven	SELECT * from Test; Within a transaction, Readview is unchanged, low_limit_id = 23,up_limit_id = 21, active transaction List (21,22). So 21 itself is visible and 22 is not visible in the active transactions list. >=23 are not visible.

Note the points:

• The read view is created before the RR reads, not when the transaction was just begin created. If the read view is created at the begin time of the transaction, then the read view of transaction 22 in step four is settled (up_limit_id = 21,low_limit_id = 23), then the data submitted in 3 is not visible in step ten. Because the transaction number 23,24,25 is greater than or equal to the transaction 22.low_limit_id
• The read view within a transaction does not change once it is created.
• In the tenth step, as I previously understood, the insert data in 3 is inserted after the begin of 2, which is supposed to mean that 2 is not visible in the 3 insert data. But the transaction guarantees that the data of the two select is consistent, so the read view is created at the first select, so the data for the insert in 3 is visible in 2.

Reference: http://hedengcheng.com/?p=148

MySQL MVCC mechanism