14.8.2.3 Consistent Nonlocking Reads

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 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. This exception causes the following
anomaly: 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.

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.

With READ COMMITTED isolation
level, each consistent read within a transaction sets and reads
its own fresh snapshot.

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.

Suppose that you are running in the default
REPEATABLE READ isolation
level. When you issue a consistent read (that is, an ordinary
SELECT statement),
InnoDB gives your transaction a timepoint
according to which your query sees the database. If another
transaction deletes a row and commits after your timepoint was
assigned, you do not see the row as having been deleted. Inserts
and updates are treated similarly.

Note

The snapshot of the database state applies to
SELECT statements within a
transaction, not necessarily to
DML statements. If you insert
or modify some rows and then commit that transaction, a
DELETE or
UPDATE statement issued from
another concurrent REPEATABLE READ
transaction could affect those just-committed rows, even
though the session could not query them. If a transaction does
update or delete rows committed by a different transaction,
those changes do become visible to the current transaction.
For example, you might encounter a situation like the
following:

With READ COMMITTED isolation
level, each consistent read within a transaction sets and reads
its own fresh snapshot. With LOCK IN SHARE
MODE, a locking read occurs instead: A
SELECT blocks until the transaction
containing the freshest rows ends (see
Section 14.8.2.4, “Locking Reads”).

Consistent read does not work over certain DDL statements:

Consistent read does not work over DROP
TABLE, because MySQL cannot use a table that has
been dropped and InnoDB destroys the
table.

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.

Programmer beware, "Consistent Nonlocking Reads" are not automatic or even very easy to accomplish reliably. In my own experience I've found blocking reads (and the resulting lock timeouts) to be quite common with innodb.

The following very simple test locks out a reader in innodb. Tested in 5.0.67. I reported it as a bug and was told it's normal, expected behavior:

Session #2 (fire up a separate window without closing the above)-----------mysql> select * from t1;^CQuery aborted by Ctrl+CERROR 1317 (70100): Query execution was interrupted(I had to kill the query after several seconds)