7.5.9.6 An Example of How the Consistent Read Works in InnoDB

Suppose you are running on 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. Thus, if transaction B deletes a row and commits after your timepoint was assigned, then you will not see the row deleted. Similarly with inserts and updates.

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    |
              ---------------------

Thus user A sees the row inserted by B only when B has committed the insert, and A has committed his own transaction 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 a locking read:

SELECT * FROM t LOCK IN SHARE MODE;

User Comments

COMMIT after SELECT is not needed for user A since
SELECT statement must always return the last
consistent snapshot of database (WITHOUT LOCK
ROW COMMAND) considering
previously commited changes from all other users.
That way you can improve much better locking
functionality, and leave users out of intensive need
for explicite locking !

What you said is Oracle way which is
"statement level"
read consistency by default. MySQL seems use
"Transaction
level" read consistency, a session only see data
commited before transaction began. In Oracle, if
usea A issues "set transaction read only", Oracle
will use "Transaction level"
read consistency, and the behavior will be same
to MySQL
one.

Add your own comment.