Hi!
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, the 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 multiversioned 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 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;
The advantage in multiversioning is that consistent reads do not set any
locks. See also the manual at http://www.innodb.com.
Hope this clarifies the issue.
Heikki Tuuri
Innobase Oy
Copied message:
...................
Hello,
does InnoDB fully supports multiple transactions ? ie =
transactions over several tables with multiple connexions.
My problem is that I do not get a consistent view of the tables after =
the inserts or delete : records are missing, or seem to be still =present.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
