How the InnoDB storage engine MVCC Works

How the InnoDB storage engine MVCC Works

InnoDB the row structure of the storage engine

MySQL Official manual https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

Internally,InnoDB adds three fields to each row stored in the database.A 6-bytedb_trx_idfield 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 was set to mark it as deleted. Each row also contains a 7-bytedb_roll_ptrfield called the roll pointer. The roll pointer points to a undo log record written to the rollback segment. If The row is updated, the Undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-bytedb_row_idfield contains a row ID that increases monotonically as new rows is inserted.If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the db_row_id column does not appear on any index.

The above paragraph means that the InnoDB storage engine has three fields on each row of records.

1. db_trx_id

2. Db_roll_ptr

3. db_row_id

And of course there is a delete bit. db_trx_id represents the update and insertion of the last transaction. Db_roll_ptr points to the undo log information for the current record entry. DB_ROW_ID identifies the ID of the new data row that was inserted.

Read_view: Visibility of Row Records

Here it is necessary to explain what is the visibility of the row records, as described above, MVCC implements multiple concurrent transactions to update the same row of records when multiple record versions are generated, and the question is, what version should the newly started transaction get to query this row of records? That is, which version is visible to this transaction. This is the problem with the visibility of the row record.

Is the structure of the read_view_struct:

The two variables that are related to visibility are low_limit_id and up_limit_id, which, according to the annotations, indicate that row records with a transaction ID greater than this value are not visible, which indicates that row records with a transaction ID smaller than this value are visible. The specific explanations are given below.

Visibility implementation of Row Records

Build Read_view: Each transaction starts with a Read_view, based on the active transaction list of the current system, creating the process:

In the MySQL client window, run the following command: Important information after the deletion is as follows,

Mysql> show engine innodb status \g*************************** 1. row  ***************************  type: innodb  name:status:======================== =============2015-09-13 23:26:42 12cf39000 innodb monitor output===================== ================------------Transactions------------trx id counter 13099purge done  For trx ' s n:o < 13097 undo n:o < 0 state: running  but idlehistory list length 380list of transactions for each  session:---transaction 0, not startedmysql thread id 15, os  thread handle 0x12cf39000, query id 940 localhost root initshow  Engine innodb status---transaction 0, not startedmysql thread id 14,  os thread handlE 0x12cf7d000, query id 936 localhost root cleaning up---TRANSACTION  13084, not startedMySQL thread id 3, OS thread handle  0x12ce71000, query id 837 localhost 127.0.0.1 root cleaning  Up---transaction 0, not startedmysql thread id 2, os thread  Handle 0x12ce2d000, query id 863 localhost 127.0.0.1 root cleaning  up---Transaction 13098, active 19901 secmysql thread id 13, os  thread handle 0x12cde9000, query id 937 localhost root cleaning  uptrx read view will not see trx with id >= 13099,  sees < 13089---Transaction 13089, active 20415 secmysql thread  id 12, os thread handle 0x12cef5000, query id 938 localhost root cleaning uptrx  read view will not see trx with id >= 13090, sees  < 13090--------1 row in set  (0.00 sec)

Can see that there is a

History list Length 380

This is the list of currently active transactions. As shown below,

Ct-trx---trx11---trx9--trx6---trx5;

Ct-trx represents the ID of the current transaction, corresponding to the above Read_view data structure as follows,

read_view->creator_trx_id = Ct-trx;

read_view->up_limit_id = TRX3;

read_view->low_limit_id = trx11;

Read_view->trx_ids = [Trx11, trx9, trx6, trx5, TRX3];

Read_view->m_trx_ids = 5;

According to the above data organization and row hidden columns, as well as the relevant information in the Undo log to achieve the visibility of the row, how to achieve the visibility of the row, the following analysis

LOW_LIMIT_ID is the maximum ID of the "High water level", the active transaction at that time, and if read to the row's db_trx_id>=low_limit_id, the data that precedes it is not committed, as described in the comment, and the data is not visible.

if (trx_id >= view->low_limit_id) {return (FALSE);}

UP_LIMIT_ID is the minimum transaction ID for the "Low water level" that is the active transaction list at that time, and if the row's db_trx_id<up_limit_id, the data is already committed when the transaction was created with the ID, as described in the note, which is visible.

if (trx_id < view->up_limit_id) {return (TRUE);}

Between the two limit_id we need to compare each other from small to large:

N_ids = view->n_trx_ids;for (i = 0; i < n_ids; i++) {trx_id_t view_trx_id = read_view_get_nth_trx_id (view, N_ids    –I–1);    if (trx_id <= view_trx_id) {return (trx_id! = view_trx_id); }}

So we're going to get the data rows in the transaction, we can judge whether this version of the data is visible in the transaction based on the row db_trx_id of the data rows and the Read_view of the current transaction.

If the data is not visible where do we need to go to find the version of the data?

is through the 7BIT db_roll_ptr just mentioned to find out the undo log information, and then determine whether this version of the data is visible, and so on.

Also, for example, we have the following information on show engine InnoDB status above

---TRANSACTION 13098, ACTIVE 19901 secmysql thread ID, OS thread handle 0x12cde9000, query ID 937 localhost root cleani Ng Uptrx Read view would not see Trx with ID >= 13099, sees < 13089

Won't see trx_id >= 13099 and sees <13089

That is, the current transaction id=13098, you will not see trx_id >= 13099 (low_limit_id) records, and you will see trx_id < 13089 (up_limit_id) records.

Also, for example,

---TRANSACTION 13089, ACTIVE 20415 secmysql thread ID, OS thread handle 0x12cef5000, query ID 938 localhost root cleani Ng Uptrx Read view would not see Trx with ID >= 13090, sees < 13090

is the same truth.

Now make an example to infer (just infer, after all, did not look at the MySQL InnoDB source),

Table structure

CREATE TABLE t2 (a int primary key, b int not null);

Session A

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM t2;+----+----+| A | b |+----+----+| 10 | 10 | | 20 | 20 | | 30 | |+----+----+3 rows in Set (0.00 sec)

Row trx_id < Read_view-up_limit_id visible.

Session B

Read_view-Ct_trx = trx11

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM t2;+----+----+| A | b |+----+----+| 10 | 10 | | 20 | 20 | | 30 | |+----+----+3 rows in Set (0.00 sec) mysql> Delete from t2 where a = 10; Query OK, 1 row affected (0.01 sec) Mysql> commit; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM t2;+----+----+| A | b |+----+----+| 20 | 20 | | 30 | |+----+----+2 rows in Set (0.00 sec)

Because the DELETE statement was executed, only two data were read out here.

Row trx_id < Read_view-up_limit_id visible.

Session A

As you might think, the following

Mysql> SELECT * FROM t2;+----+----+| A | b |+----+----+| 10 | 10 | | 20 | 20 | | 30 | |+----+----+3 rows in Set (0.00 sec)

In session a transaction, it is repeatable to read. The repeatable readings here are based on the InnoDB visibility rules that we've described above.

Although the data row of a = 10 is deleted at session B, a db_roll_ptr pointer points to the data row in the Undo log, which is visible in session a because the trx_id of the data row conforms to the visibility condition.

PostScript: According to the basic information to make inferences, presumably this, do not do too much in-depth research, probably understand the principle, after all, did not read the source code.

Resources:

http://libisthanks.blog.163.com/blog/static/23527612320141016111027592/

http://www.xdata.me/?p=289

http://hedengcheng.com/?p=148

=======end=======

How the InnoDB storage engine MVCC Works