(mysql.info) innodb-consistent-read-example
Info Catalog
(mysql.info) innodb-next-key-locking
(mysql.info) innodb-transaction-model
(mysql.info) innodb-locks-set
14.2.10.7 An Example of Consistent Read 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' 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.
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) innodb-next-key-locking
(mysql.info) innodb-transaction-model
(mysql.info) innodb-locks-set
automatically generated byinfo2html