Today, I’ll show you how transaction isolation is implemented in MySQL’s InnoDB storage engine.

Each transaction in InnoDB has a unique transaction ID called transaction ID. It is applied to InnoDB’s transaction system at the beginning of a transaction and is applied in a strict ascending order.

For each row in the database, there are three hidden fields: db_trx_ID (transaction ID), DB_ROLL_pt (rollback pointer), and delete_flag(delete flag). In fact, delete_flag is in the header information, here is for easy to understand

For DML operations:

  • INSERT: Creates a piece of data,db_trx_idIs the current transactionid.db_roll_pt 为 null 。
  • UPDATE: Copies a row of data, copying the current rowdb_trx_idSet to the current transactionid.db_roll_ptIt’s a pointer to the one before the copy.
  • DELETE: Copies a row of data, copying the current rowdb_trx_idSet to the current transactionid.db_roll_ptIt’s a pointer to the one before the copy. And thedelete_flagSet totrue 。

We will use this statement to build tables and initialize data for the following example:

CREATE TABLE `t` (
  `id` int(11) NOT NULL.`k` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB;

insert into t(id, k) values(1.1);
Copy the code

The initial data status is:

Repeatable Read Repeatable Read

If the current isolation boundary is Repeatable Read, the following is the execution process of SQL:

So what should be the result of the query for rows 8, 10, 12, and 13?

The results should be: 2,1,3,1.

Here’s how MySQL implements this process step by step:

  • Line 1: Represents the ID number of each transaction, where the read View takes an array of all currently active transaction ids. Active refers to transactions that have been opened and whose transaction ids have been generated but have not been committed. Max ID takes the maximum transaction ID by far, whether committed or not. We also call the smallest value in the Read View array min ID.

  • Lines 2-4: Note that I do not use begin/ Start transaction to start a transaction, because they do not create the read View and Max ID immediately. It is created after the first SELECT statement is executed.

  • The fifth action changes the value of k by increasing it by 1 according to the above rules, after modification:

  • On line 6, commit the transaction with ID 102.

  • Line 7 performs a simple query that does not manually start the transaction, but automatically starts it and generates read view: [100, 101] and Max id: 103, respectively

The search is performed according to the following query rules:

  1. If the value db_trx_id < min ID, the data has been committed before the current transaction is started.

  2. If the value db_trx_id > Max ID is used, the data was generated after the transaction started and the content is not visible.

  3. If the min id of the data <= db_trx_id <= Max id, there are two cases:

    3.1 If db_trx_id is in the read View array, this version is not visible because it was generated by a transaction that has not committed yet, but it is visible if it is its own transaction.

    3.2. If the version is not in the data, it is visible that the version was generated by a committed transaction.

The schematic diagram is as follows:

The consistency view of the current transaction is read View: [100, 101], Max ID: Db_trx_id = 102, db_trx_id = 102, db_trx_id = 102, db_trx_id = 102

  • Line 8, the consistency view for the current transaction is read View: [100, 101], Max ID: 101 If the value of the first db_trx_id is 102, the value is not visible in rule 2. If the value of the first db_trx_id is 102, the value is visible in rule 1.

  • In line 9, change the value of k by increasing it by 1, following the rules described above:

  • In line 10, the consistency view for the current transaction is read View: [100], Max ID: Db_trx_id = 100 the first datum is in the read View array where the transaction id is visible and the result is 3.

  • In line 11, the consistency view for the current transaction is read View: [100, 101], Max ID: Db_trx_id = 100; in read view, db_trx_id = 100; If the value db_trx_id is 102, the value cannot be found. If the value is 10, the value can be found. If the value is 10, the value can be found.

  • Lines 12-13 are the commit transaction statement.

Read Committed The Read is Committed

The above rules can also be applied in Read Committed, but consistent views: Read Views and Max ids are created at the time of each SELECT statement. Based on the above content, you can try to read the submitted by yourself.