Consistent nonlocking read refers to the fact that InnoDB storage engine reads the data of rows in the current execution time database through multi versioning. If a row is being read by a DELETE or UPDATE operation, InnoDB does not wait for the row lock to be released. Instead, InnoDB reads a snapshot of the row.

The following figure visually illustrates consistent unlocked row reads:

This is called an unlocked read because there is no need to wait for the release of the X lock on the accessed row. Snapshot data refers to the data of the previous version of the row, which is implemented through the undo section. The undo segment is used to roll back data in this transaction, so the snapshot data itself has no additional overhead. In addition, snapshot data does not need to be locked because there are no transactions that need to modify historical data.

As you can see, the non-locked read mechanism greatly improves the concurrency of the database. Under the default Settings of the InnoDB storage engine, this is the default read mode, i.e. reads do not occupy and wait for locks on tables. However, different transaction isolation levels are read in different ways, and non-locked consistent reads are not always used at each transaction isolation level. In addition, the definition of snapshot data varies, even though non-locked consistent reads are used.

Figure 6-4 shows that snapshot data is the historical version of the current row. Each row may have multiple versions. As Figure 6-4 shows, a row record may have more than one snapshot data, which is commonly referred to as a row multi-version technique. The resulting concurrency control, called multi version concurrency control (MultiVersionConcurrencyControl MVCC).

InnoDB storage engine uses non-locked consistent reads at READ COMMITTED and REPEATABLE READ(the default transaction isolation level of InnoDB storage engine). However, the definition of snapshot data is different. At the READ COMMITTED transaction isolation level, for snapshot data, non-consistent reads always READ the latest snapshot data of the locked row. In REPEATABLE READ transaction isolation level, for snapshot data, inconsistent reads always READ the version of the row data at the start of the transaction (the key is isolation between transactions). SQL > select * from session A of the current MySQL database;

# Session A
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

Sql> SELECT * FROM parent WHERE id =1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
Copy the code

Session A started A transaction by explicitly executing the command BEGIN and reading data with ID 1 in the parent table, but the transaction did not end. At the same time, the user starts another session B to simulate the concurrency situation, and then performs the following operations on session B:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE parent SET id=3 WHERE id=l;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 warnings: 0
Copy the code

Select * from parent where id=1; select * from parent where id=3; If A record with ID 1 is READ again in session A, InnoDB storage engine uses non-locked consistent reads at transaction isolation levels COMMITTED and REPEATETABLE READ. Mysql > SELECT * FROM parent WHERE ID =1; mysql > SELECT * FROM parent WHERE ID =1; mysql > SELECT * FROM parent WHERE ID =1;

mysql> SELECT FROM parent WHERE id =l;
+----+
| id |
+----+
| 1  |
+----+
1 row in set (0.00 sec)
Copy the code

Because the current data with ID =1 has been modified once, there is only one row version of the record. Next, the last transaction is committed in session B.

# Session BMysql > commit Query OK, 0 rows affected (0.01sec)Copy the code

Mysql > SELECT * FROM parent WHERE ID =1; mysql > SELECT * FROM parent WHERE ID =1; SQL > READ COMMITTED and REPEATABLE; For the READ COMMITTED transaction isolation level, it always reads the latest version of the row, and if the row is locked, the latest snapshot (fresh Snapshot) of that version is READ. In the above example, because session B has already COMMITTED a transaction, the READ COMMITTED transaction isolation level gives the following result:

mysql>SELECT @@tx_isolation\G;
**************************** 1.row ****************************
@@tx_isolation: READ-COMMITTED
1 row in set (0.00 sec)

mysql> SELECT FROM parent WHERE id=1:
Empty set (0.00 sec)
Copy the code

For the transaction isolation level of REPEATETABLE, the row data at the start of the transaction is always read. Thus, for the REPEATETABLE READ transaction isolation level, it yields the following:

mysql> SELECT @@tx_isolation\G;
**************************** 1.row ****************************
@@tx_isolation: REPEATABLE-READ
1 row in set(0.00 SEC) mysql> SELECT parent FROM parent WHERE id=1; +----+ | id | +----+ | 1 | +----+ 1 rowin set (0.00 sec)
Copy the code

Table 6-8 shows the process of the previous demonstration in terms of time. It is important to note that for READ COMMITTED transaction isolation levels, it violates the property of I in transaction ACID, namely isolation, from a database theory perspective.

This article is adapted from inside MySQL Technology :InnoDB Storage Engine, 2nd edition

Personal wechat official Account:

Individual making:

github.com/jiankunking

Personal Blog:

jiankunking.com