DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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