Shulou(Shulou.com)06/01 Report--

1. Introduction of the principle

Data multi-version (MVCC) is one of the main ways for MySQL to achieve high performance. By not locking the ordinary SELECT and directly using MVCC to read the value of the finger version, it avoids the process of repeatedly locking the data. InnoDB supports multiple versions of MVCC, in which RC and RR isolation levels are supported by consistent read view, that is, taking a snapshot of the transaction system at a certain time to record all active read and write transaction ID, and then the read operation compares the transaction ID with the transaction ID in the snapshot to determine visibility.

2. InnoDB data row structure

In the row structure, in addition to the user-defined columns, there are three system columns: DATA_ROW_ID, DATA_TRX_ID, DATA_ROLL_PTR. If the table does not have a primary key defined, then DATA_ROW_ID is the primary key column, otherwise there is no DATA_ROW_ID column in the row structure. Where:

DATA_TRX_ID: the IDDATA_ROLL_PTR of the transaction that modified the data of the row: a pointer to the rollback segment of the row.

The key to the whole MVCC implementation depends on these two fields.

3. The principle and flow of READ-VIEW

4. READ-VIEW interpretation

1) read view is bound to the SQL statement and is requested or obtained before each SQL statement is executed (RR isolation level: the first select request of the transaction, followed by this; RC isolation level: each select is applied for)

2) read view structure

Struct read_view_t {ulint type; / *!

< VIEW_NORMAL, VIEW_HIGH_GRANULARITY */ undo_no_t undo_no;/*!< 0 or if type is VIEW_HIGH_GRANULARITY transaction undo_no when this high-granularity consistent read view was created */ trx_id_t low_limit_no; /*!< The view does not need to see the undo logs for transactions whose transaction number is strictly smaller (= this value. In other words, this is the "high water mark". */ trx_id_t up_limit_id; /*!< The read should see all trx ids which are strictly smaller (read_view为NULL，从而重新申请。对于RR隔离级别，则SQL语句结束后不会删除read_view，从而下一个SQL语句时，使用上次申请的，这样保证事务中的read view都一样，从而实现可重复读的隔离级别。 4）对于可见性判断，分配聚集索引和二级索引。聚集索引： 记录的DATA_TRX_ID < view->

Up_limit_id: when the read view is created, the transaction that modifies the record has been committed and the record is visible

DATA_TRX_ID > = view- > low_limit_id: the record is modified after the current transaction starts, and the record is not visible

DATA_TRX_ID is located at (view- > up_limit_id,view- > low_limit_id): you need to find out whether the trx_id exists in the array of active read and write transactions, and if so, the record is not visible to the current read view.

Secondary index:

Because the secondary index of InnoDB only stores the trx_id last updated by page, when querying with the secondary index, if the trx_id of page is less than view- > up_limit_id, you can directly judge that all records of page are visible to the current view, otherwise you need to go back to the clustered index to judge.

5) if the record is not visible to the view, the current view visible version data needs to be constructed by traversing the history list with the recorded DB_ROLL_PTR pointer

6) after the execution of the start transaction and begin statements, transaction ID, rollback segment, read_view, and putting the transaction into the read-write transaction list are not allocated in the innoDB layer. This operation needs to be completed by calling the function trx_start_low in the first SQL statement, which should be noted.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.