mysql-innodb-Multi-version concurrency control (MVCC)

The MVCC of InnoDB is achieved by saving three hidden columns after each row of records, one of the two columns that saved the creation time of the row, the expiration time (or deletion time) of a saved row. Of course, it is not the actual time value that is stored, but the system version number :

1, db_trx_id:6 bytes of transaction ID, each processing a transaction, its value automatically +1, the above said "Create Time" and "Delete Time" record is this db_trx_id value, db_trx_id is the most important one, you can through the "show engine InnoDB Status "to check

2, Db_roll_ptr: Size 7byte, point to write to rollback segment (rollback segment) of an undo log record (update operation, record the row value before the update)

3, db_roll_id: The size is 6 bytes, the value with new lines insert monotonically increase, when the clustered index is automatically generated by InnoDB, the clustered index includes this db_row_id value, otherwise the clustered index does not include this value (I understand that if the clustered index is not automatically generated, There will be no db_roll_id this value). This is used in the index
　　MVCC only works under the two isolation levels of repeatable read and Read Committed. The other two isolation levels are incompatible with MVCC. Let's take a look at the REPEATABLE read isolation level, how MVCC is specifically operated

　　SELECT

• • InnoDB only finds data rows that are earlier than the current version of the transaction (that is, the system version number of the row must be less than or equal to the transaction), which ensures that the current transaction reads the rows that existed before the transaction, or was created or modified by the current transaction.
  • The version of the delete operation for a row must be undefined or greater than the version number of the current transaction. Determined that the row was not deleted before the current transaction started

The results of the query can only be returned if the two points above are met.

　　INSERT

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

　　DELETE

InnoDB saves the current system version number for each row deleted as a row delete identity

　　UPDATE

　　　　InnoDB to insert a new row of records, save the current system version number as the line version number, the colleague saves the current system version number to the original line to delete the identity as a row

Reference:

[1] "High performance MySQL" (third edition), Baron Schwartz, Ninghai Yuanhao, etc., Electronic industry press, 2013

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

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

[4] Blog, http://blogread.cn/it/article/5969

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

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

mysql-innodb-Multi-version concurrency control (MVCC)