MySQL InnoDB Multi-Versioning原文翻译

InnoDB是一个多版本的存储引擎，所以它必须在表空间中保存row的旧的版本。这些row的旧的版本被保存在称为rollback segment的数据

没事看MVCC，看网上中文一堆堆的，各个说法都不一样，上google搜索了一下，发现MySQL User Guide里面有描述，还是把原文给翻译一下，，供大家学习学习

请阅读本文前先参考前一篇文章，有助于理解

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是一个多版本的存储引擎，所以它必须在表空间中保存row的旧的版本。这些row的旧的版本被保存在称为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.

为了支持MVCC, InnoDB内部实现的时候为每row增加了三个字段：

1. 6 byte DB_TRX_ID: 代表最后插入或者更新该行的Transaction Identifier. 删除操作也被当做更新操作，只是在该行上加了一个特殊比特作为标志。

2. 7 byte DB_ROLL_PTR：这个指向undo log的链表，如果该row被更新了，该row中原先内容将会被移到undo log中作为最前面的undo log的节点

3. 6 byte DB_ROW_ID：当该row所在表新增加一行时， 新增row中的DB_ROW_ID会增加 。。。

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有两个作用：undo log链表的第一个节点可以用来rollback的时候恢复。undo log链表中其他节点用来在REPEATED_READ的情况下给哪些活的Transaction提供一致读的效果的。

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 could need the information in the update undo log to build an earlier version of a database row.

Undo log分为insert和update两种。insert undo log当新insert row的时候产生的，这些undo log只有在Transaction rollback的时候用来删除前面insert的行，只要这个Transaction一commit或者discard,这个insert undo log就被丢弃掉了。 我们前面提的都是update undo log作用，就是前面说的“undo log”的两个作用，你想想，如果undo log链表中不是最前面的节点对应的Transaction已经不在了，这个节点还有存在的价值吗？因为没有tranaction去读它了。

推荐阅读：

InnoDB存储引擎的启动、关闭与恢复

MySQL InnoDB独立表空间的配置

MySQL Server 层和 InnoDB 引擎层 体系结构图

InnoDB 死锁案例解析

MySQL Innodb独立表空间的配置