【MySQL】InnoDB 《Consistent Nonlocking Reads》实验

原文地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

在 REPEATABLE READ 隔离级别的几种表现形式

1）在同一个事务中多次查询，看到的数据一致

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions.

InnoDB使用的是某个时间快照的方式实现的读一致。在同一个事务中，查看只能看到这个事务前面的事务提交的变更。看不到没有提交的事务或者当前事务之后的事务所作的变更。

实验1

结论：在同一事务中，多次查询数据是一致的。

2) 获取快照的时间点

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

实验2

结论：获取快照的时间点是从第一次查开始，而不是声明事务。

3）在同一个事务中，修改表的数据，查看可以看到最新的版本数据。

If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

实验1

结论：T1，第一次查询时，T2还没插入数据。在T2，插入时间并commit后，T1,执行update操作后，再查询可以最新提交记录。

实验2

结论：在同一个事务中，只有发生变更表，才能查看最新版本数据，其它还是第一次查询时快照数据。