14.2.10.4. Consistent Non-Locking Read
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 see the changes made by
those transactions that committed before that point of time, and
no changes made by later or uncommitted transactions. The
exception to this rule is that the query sees the changes made
by earlier statements within the same transaction.
If you are running with the default REPEATABLE
READ isolation 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.
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 users are free to modify those
tables at the same time a consistent read is being performed on
the table.
Note that consistent read does not work over DROP
TABLE and over ALTER TABLE.
Consistent read does not work over DROP TABLE
because MySQL can't use a table that has been dropped and
InnoDB destroys the table. Consistent read
does not work over ALTER TABLE because it is
executed inside of the transaction that creates a new table and
inserts rows from the old table to the new table. When you
reissue the consistent read, it will not see any rows in the new
table, because they were inserted in a transaction that is not
visible in the snapshot read by the consistent read.
