mysql - MySQL MVCC 用于构建先前快照的高级算法

Question

来自 MySQL manual关于 InnoDB 多版本控制:

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-byte DB_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.

但是，我找不到任何有关如何使用这些隐藏列(DB_TRX_ID、DB_ROLL_PTR 和 DB_ROW_ID)进行构建的信息上一张快照，算法是什么？

other page在关于只读事务的手册中声明如下:

InnoDB can avoid the overhead associated with setting up the transaction ID (TRX_ID field) for transactions that are known to be read-only. A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE. Eliminating unnecessary transaction IDs reduces the size of internal data structures that are consulted each time a query or data change statement constructs a read view.

考虑到上述陈述，由于只读事务没有关联的 TRX_ID，因此应该有与当前事务相关的其他内容与 DB_TRX_ID 进行比较code> 现有行的值，以便能够确定该特定行是否应包含在构建的快照中。

请描述高级算法以及只读事务的情况(如果它使过程有所不同)。

Answer 1

如果有多个连接修改同一行，则该行的“历史列表”中存在该行的多个化身。 TRX_ID 控制可见性:如果化身早于 X，则连接可以“看到”它。否则，它是一个对该连接尚不可见的版本(想想 MVCC 中的 V)。 (注意:transaction_isolation 级别会纳入“可见性”。)

我怀疑仅当请求ROLLBACK(或崩溃调用它)时才需要DB_ROLL_PTR(认为ROLLBACK)。

我猜测只读事务使用TRX_ID，但不会创建新的，因为它不会创建任何新值来保存在历史更改中，或回滚.

有关更多血淋淋的细节(以及检查我所说内容的有效性)，请参阅 blogs by JCole .