Rechercher dans le manuel MySQL
15.7.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.
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:
- -- Returns 0: no rows match.
- -- Deletes several rows recently committed by other transaction.
- -- Returns 0: no rows match.
- -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
- -- Returns 10: this txn can now see the rows it just updated.
You can advance your timepoint by committing your transaction
and then doing another SELECT
or
START TRANSACTION WITH
CONSISTENT SNAPSHOT
.
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.
If you want to see the “freshest” state of the
database, use either the READ
COMMITTED
isolation level or a
locking read:
With READ COMMITTED
isolation
level, each consistent read within a transaction sets and reads
its own fresh snapshot. With FOR SHARE
, a
locking read occurs instead: A SELECT
blocks
until the transaction containing the freshest rows ends (see
Section 15.7.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 andInnoDB
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. In this case, the transaction returns an error:ER_TABLE_DEF_CHANGED
, “Table definition has changed, please retry transaction”.
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 FOR SHARE
:
By default,
InnoDB
uses stronger locks and theSELECT
part acts likeREAD COMMITTED
, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.To use a consistent read in such cases, set the isolation level of the transaction to
READ UNCOMMITTED
,READ COMMITTED
, orREPEATABLE READ
(that is, anything other thanSERIALIZABLE
). In this case, no locks are set on rows read from the selected table.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-consistent-read.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.