MySQL InnoDB Multi-Versioning Original translation

I have nothing to do with MVCC. I have read a bunch of Chinese on the Internet, and each statement is different. I searched on google and found that there is a description in the MySQL User Guide. I 'd like to translate the original text for everyone to learn.

Please refer to the previous article before reading this article to help you understand

Because InnoDB is a multi-versioned storage engine, it must keep information about old versions of rows in the tablespace. this information is stored in a data structure called a rollback segment (after an analogous data structure in Oracle ).

InnoDB is a multi-version storage engine, so it must save the old version of row in the tablespace. The old versions of these rows are stored in a data structure called rollback segment.

Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. each row also contains a 7-byteDB_ROLL_PTR field called the roll pointer. the roll pointer points to an undo log record written to the rollback segment. if the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. if InnoDB generates a clustered index automatically, the index contains row ID values. otherwise, the DB_ROW_ID column does not appear in any index.

To support MVCC, InnoDB adds three fields to each row during internal implementation:

1. 6 byte DB_TRX_ID: indicates the last Transaction Identifier to insert or update the row. The delete operation is also used as an update operation, but a special bit is added as a flag to the row.

2. 7 byte DB_ROLL_PTR: The linked list pointing to the undo log. If the row is updated, the original content in the row will be moved to the undo log as the front undo log node.

3. 6 byte DB_ROW_ID: when a new row is added to the table where the row is located, DB_ROW_ID in the new row will be added...

InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read.

Undo log has two functions: the first node of the undo log linked list can be used to restore data during rollback. Other nodes in the undo log linked list are used to provide consistent read results for the active transactions when REPEATED_READ is performed.

Undo logs in the rollback segment are divided into insert and update undo logs. insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read cocould need the information in the update undo log to build an earlier version of database row.

Undo logs are classified into insert and update logs. Insert undo log is generated when a new insert row is created. These undo logs are only used to delete the row in the previous insert when the Transaction rollback is used, as long as the Transaction is a commit or discard, the insert undo log is discarded. We mentioned above that the update undo log function is the two functions of the "undo log" mentioned above, if the Transaction corresponding to not the first node in the undo log linked list is no longer available, is there any value for this node? Because there is no tranaction to read it.

Recommended reading:

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration