On Fri, 17 Aug 2001, Heikki Tuuri wrote:
Problem resolved, thank you very much. It was indeed my missing a
concept, description below:
> it was not immediately clear to me what your application
> does. Does one connection update the table and
> another connection do the SELECT?
Yes, as it's two seperate programs. We've got a directory of dynamically
loadable modules for the second part of the functionality, and the first
part of the functionality takes too long to start up, so we've broken the
two steps into seperate programs, so that the second part can exit and
reload any time any of the loadable modules change, or a new one is added.
> You can advance your timepoint by committing your transaction and then doing
> another SELECT.
This is what was throwing me. As soon as I did a
::dbh->commit() regardless of whether I updated any records, the SELECT
timepoint was getting properly updated. This is the concept I was
missing. I've worked with DBs that supported transactions before, but not
multiversioning.
> 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 problem you might have is that ::dbh->commit() really does not do
> a commit. Have you tested it? If you do a ::dbh->commit() after inserting
> some rows with autocommit switched off, and then do ::dbh->do("ROLLBACK"),
> do the rows get inserted or not?
It's a real commit, ::dbh->do("ROLLBACK") didn't undo the ::dbh->commit(),
so the problem was almost definitely my lack of understanding of the
multiversioning of InnoDB.
> Eric, executing a commit through ::do->("COMMIT") is a perfectly
> valid way to do a commit.
Good to know. I wasn't expecting the autocommit mode to affect SELECTs,
so I was thrown off track when ::dbh->do("COMMIT") seemed to have
different side-effects than ::dbh->commit(). Nice to know that they are
equivelent, it was the AutoCommit on/off that made the difference.
---------------------------------------------------------------------
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
