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
will see 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 may 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.
You can advance your timepoint by committing your transaction
and then doing another SELECT
.
This is called multi-versioned concurrency control.
In the following example, session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.
Session A Session B SET autocommit=0; SET autocommit=0; time | SELECT * FROM t; | empty set | INSERT INTO t VALUES (1, 2); | v SELECT * FROM t; empty set COMMIT; SELECT * FROM t; empty set COMMIT; SELECT * FROM t; --------------------- | 1 | 2 | --------------------- 1 row in set
If you want to see the “freshest” state of the
database, you should use either the
READ COMMITTED
isolation
level or a locking read:
SELECT * FROM t LOCK IN SHARE MODE;
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 13.6.8.3, “SELECT ... FOR UPDATE
and SELECT ... LOCK IN
SHARE MODE
Locking Reads”).
Consistent read does not work over DROP
TABLE
or over ALTER
TABLE
:
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
ALTER TABLE
works by making a
temporary copy of the original table and deleting 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.
InnoDB
uses a consistent read for select in
clauses like INSERT INTO
... SELECT
,
UPDATE ...
(SELECT)
, and
CREATE TABLE ...
SELECT
that do not specify FOR
UPDATE
or LOCK IN SHARE MODE
if the
innodb_locks_unsafe_for_binlog
option is set and the isolation level of the transaction is not
set to SERIALIZABLE
. Thus, no
locks are set on rows read from the selected table. Otherwise,
InnoDB
uses stronger locks and the
SELECT
part acts like
READ COMMITTED
, where each
consistent read, even within the same transaction, sets and
reads its own fresh snapshot.
User Comments
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 #1
----------
mysql> create table t1(a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (3);
Query OK, 1 row affected (0.02 sec)
Session #2 (fire up a separate window without closing the above)
-----------
mysql> select * from t1;
^CQuery aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted
(I had to kill the query after several seconds)
Setting "set session transaction isolation level read committed;" did not help.
Setting "set session transaction isolation level read uncommitted;" did not help.
Using lock tables on either side did not help.
Setting innodb_locks_unsafe_for_binlog=1 in my.cnf fixes the problem, but I guess the downside is that now I will not be able to use replication in this database.
*HOWEVER*
If you change the simple select statement to a CREATE TEMPORARY TABLE ... AS SELECT ..., and the target involves an index range, then even innodb_locks_unsafe_for_binlog does not help.
Dude, someone answered this on the forum:
http://forums.mysql.com/read.php?22,281645,287007#msg-287007
Add your own comment.