innodb-非锁定一致性读

非锁定一致性读

1.在RR和RC隔离级别下一致性读是innodb默认的模式，一致性读不会加任何锁，不影响其他事务来修改相关数据

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

2.在RR级别下，一个事务建立快照是以第一次读取操作时作为时间点。如果想更新快照，需要提交当前事务然后再开启一个事务执行查询操作。

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.

3.可以提前建立快照的两种方式：1.提交当前事务然后再执行select 2.以"START TRANSACTION WITH CONSISTENT SNAPSHOT"的方式开启一个事务

You can advance your timepoint by committing your transaction and then doing another SELECT or START TRANSACTION WITH CONSISTENT SNAPSHOT.

4.可以看到最新的数据库状态的两种方式：1.使用RC隔离级别 2.使用锁定读方式

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read

这里解释下为什么使用锁定读方式可以看到数据库最新状态：
使用锁定读，可以防止数据被修改或者范围内插入新的数据，因为锁定读有两种方式:select for update(排他锁)、select lock in share mode(共享锁)，不管是哪种方式都是和排他锁互斥的。

5.在特殊场景下一致性读是不能工作的：1.drop table 2.alter table

1.Consistent read does not work over DROP TABLE, because MySQL cannot use a table that has been dropped and InnoDB destroys the table.
2.Consistent read does not work over ALTER TABLE, because that statement makes a temporary copy of the original table and deletes the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction’s snapshot was taken. In this case, the transaction returns an error: ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

6.在一些场景下不需要锁定读也会刷新快照，例如：insert…select、update…(select)、create…select

The type of read varies for selects in clauses like INSERT INTO … SELECT, UPDATE … (SELECT), and CREATE TABLE … SELECT that do not specify FOR UPDATE or LOCK IN SHARE MOD