Mysql consistent reading and mysql consistency

Mysql consistent reading and mysql consistency
Consistent Nonlocking Reads

Consistent read means that InnoDB uses multiple versions to provide a snapshot for querying a database at a certain time point. This kind of query shows the changes in transaction commit before the current world point, but not the changes committed since then, and not the uncommitted changes. An exception to this rule is that it can see the changes in the same transaction before this query. This exception is caused: If you update the rows in a table, a SELECT statement can view the latest versions of these updated rows, and it can also see the older versions of any row, if another transaction updates the table at the same time, it means that you may see a certain state of the table, but this state does not actually exist in the database.

If the transaction isolation level is REPEATABLE_READ, all consistent reads in the same transaction will be read to the stable snapshot version that was first read in this transaction. If you submit the current transaction and execute the same query later, you will get the latest snapshot version.

Consistent read at the READ_COMMITTED and REPEATABLE_READ isolation levels is the default method for InnoDB to process SELECT. Consistent read does not set any lock on the table it accesses, so other transactions can modify the table at will while consistent read.

Assume that the transaction isolation level is REPEATABLE_READ. When you perform consistent read, InnoDB will give your transaction a time point based on the time you query the database. If another transaction is deleted and committed after your time point, you will not see this row because it has been deleted. Insert and modify are similar to this.

This is called multi-version concurrency control.

In the following example, only when B has submitted its insertion and A has submitted its insertion can A see the insertion of B.

 

Translation from https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html