Multiple versions of InnoDB and InnoDB

Multiple versions of InnoDB and InnoDB

InnoDB is a multi-version storage engine. To support some features of transactions, such as concurrency and rollback, InnoDB maintains the old version of the modified row. This information is stored in a tablespace called "rollback segments" (similar to the rollback segments in Oracle ). InnoDB uses this information in the rollback segment to perform the undo operation to support transaction rollback. It also uses this information to construct earlier versions of rows to support consistent reading.

Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field is used to insert or modify the transaction identifier of the last transaction in this row. Delete is treated as update internally, that is, it marks the row's special location to indicate that this row has been deleted. Each row also contains a 7-byte DB_ROLL_PTR field called "roll pointer. This roll pointer is directed to an undo log written in the rollback segment. If the row is modified, the information contained in the undo log must be modified before the row is modified. A 6-byte DB_ROW_ID field contains a monotonically increasing row ID when a row is inserted. If InnoDB automatically generates a clustered index, the index contains the row ID value. Otherwise, the DB_ROW_ID column will not appear in any index.

The undo logs in the rollback segment is divided into insert undo logs and update undo logs. The insert undo logs is used only when the transaction is rolled back, and is discarded after the transaction is committed. Update undo logs is used for consistent read.

Submit your transactions on a regular basis, including those with consistent reads. Otherwise, InnoDB cannot discard data from the update undo logs, and the rollback segment may increase greatly to fill up your tablespace.

In multiple versions of InnoDB, When you delete an SQL statement, this row is not physically deleted from the database immediately. InnoDB physically deletes this row and Its Index records only when update undo logs is discarded for deletion. This delete operation is called purge, And it is fast.

Multi-Versioning and Secondary Indexes

InnoDB multi-version concurrency control (MVVC) treats secondary indexes and clustered indexes differently. The clustered index of a record is updated in the original, and the hidden system columns for executing the undo log will be re-constructed. Unlike clustered index records, secondary index records do not contain hidden system columns and are not updated in the original.

When a secondary index record is updated, the old secondary index record is marked as deleted, the new index record is inserted, and the index record marked as deleted is eventually purge. When a secondary index record is marked as deleted or the secondary index page is updated by a new transaction, InnoDB uses a clustered index to search for database records. In the clustered index, the DB_TRX_ID field of the record is checked, and the correct version of the record is retrieved from the undo log, even if the record is modified after the transaction is initially read.

 

Summary:

1. InnoDB maintains the old version information of the modified row in the rollback segment.

2. The undo logs in the rollback segment can be divided into insert undo logs and update undo logs. In addition, write of undo logs takes precedence.

3. Internally, delete is treated as update. It marks the old row as deleted and inserts a new row.

4. This record is not deleted from the database immediately when the SQL statement executes the DELETE operation. Only when update undo logs is discarded will the record and its index record be physically deleted.

5. InnoDB adds three fields to each record in the database

• DB_TRX_ID: indicates the transaction identifier of the last transaction that inserts or modifies this row.
• DB_ROLL_PTR: point to an undo log record in the rollback segment
• DB_ROW_ID: monotonic increasing row ID. (PS: If no clustered index is clearly defined, a clustered index is automatically generated. The value of the clustered Index automatically generated at this time is DB_ROW_ID)

6. The secondary index under MVCC is different from the clustered index. Secondary index records neither contain hidden system columns nor are updated in the original position. In contrast, clustered index records contain hidden system columns pointing to undo log records, and the clustered index is updated at the original position.

7. When a secondary index column is updated, the old secondary index record is marked as deleted and a new secondary index record is inserted.

8. It can be seen that undo logs not only contains the record's undo log, but also contains the index record's undo log.

 

Reference https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html