Mysql-innoDB-Multi-version concurrency control (MVCC), mysql-innodb-mvcc

Mysql-innoDB-Multi-version concurrency control (MVCC), mysql-innodb-mvcc

The MVCC of InnoDB is saved after each record line.ThreeThe hidden columns are used to save the creation time of the row and the expiration time (or deletion time) of the row ). Of course, not the actual time value is stored, but the system version number ):

1. DB_TRX_ID: a 6-byte transaction ID. The value of each transaction is automatically + 1. The aforementioned "creation time" and "deletion time" records indicate the value of DB_TRX_ID, DB_TRX_ID is the most important one. You can check it through "show engine innodb status ".

2. DB_ROLL_PTR: 7 bytes in size, pointing to an undo log record written to rollback segment (rollback segment) (record the row value before update in the update operation)

3. DB_ROLL_ID: The size is 6 bytes. This value increases monotonically with the insertion of new rows,When innodb automatically generates a clustered index, the clustered index includes the value of DB_ROW_ID. Otherwise, the clustered index does not include this value (I understand that if the clustered index is not automatically generated, the value of DB_ROLL_ID will not exist ).This is used in the Index
MVCC only works at the repeatable read and read committed isolation levels. The other two isolation levels are not compatible with MVCC. Next, let's take a look at how MVCC operates at the repeatable read isolation level.

　　SELECT

• • InnoDB only looks for data rows whose versions are earlier than the current transaction version (that is, the system version number of the row must be earlier than or equal to the transaction version). This ensures that all rows read by the current transaction already exist before the transaction, or created or modified by the current transaction.
  • The version of the row deletion operation must be undefined or later than the version of the current transaction. The row is not deleted before the current transaction starts.

The query results can be returned only when the preceding two points are met.

　　INSERT

InnoDB records the current system version number for each new row as the creation ID

　　DELETE

InnoDB saves the current system version number for each row to be deleted as the row deletion identifier

　　UPDATE

　　　　InnoDB inserts a new row, saves the current system version number as the row version number, and saves the current system version number to the original row as the row deletion identifier.

Refer:

[1] High-Performance MySQL (Third edition), Baron Schwartz, translated by Ninghai yuan, Electronic Industry Press, 2013

Blog, http://www.cnblogs.com/chenpingzhao/p/5065316.html

Blog, https://www.percona.com/blog/2014/12/17/innodbs-multi-versioning-handling-can-be-achilles-heel/

Blog, http://blogread.cn/it/article/5969

Blog, http://blog.csdn.net/chen77716/article/details/6742128

[6] blog, http://blog.chinaunix.net/link.php? Url = http://forge.mysql.com % 2 Fwiki % 2FMySQL_Internals