(mysql.info.gz) InnoDB Consistent read example
Info Catalog
(mysql.info.gz) InnoDB Next-key locking
(mysql.info.gz) InnoDB transaction model
(mysql.info.gz) InnoDB Locks set
15.11.6 An Example of How the Consistent Read Works in `InnoDB'
---------------------------------------------------------------
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' will give 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 will 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."
User A User 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
In this example, user 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, you should use
either the `READ COMMITTED' isolation level or a locking read:
SELECT * FROM t LOCK IN SHARE MODE;
Info Catalog
(mysql.info.gz) InnoDB Next-key locking
(mysql.info.gz) InnoDB transaction model
(mysql.info.gz) InnoDB Locks set
automatically generated byinfo2html