advice about 'stale' data --> Ima, shared dbh handles, and txns

water has asked for the wisdom of the Perl Monks concerning the following question:

We're trying to make sense of some external code that was given to us. The code is a Mason web front-end to some mysql INNODB tables. Access to the tables is through Class::DBI (hence Ima). The code is running from mod_perl 1.99 on Apache 2. The code uses transactions.

Here's the semi-mystery: within one browser window, the app changes the DB, and those changes are seen. From a different browser window, however, those changes are sometimes not seen -- the other window (be it on a different client box, or a different browser on the same client box), it is as if the database doesn't see the txn, or the commit.

Anyone have any clues as to what this might be, as we explore the code and start ex-post-facto building tests for it? There do appear to be Table->dbi_commit in the right places, but, we're just diving into this morass....

Comment on advice about 'stale' data --> Ima, shared dbh handles, and txns Download Code

Replies are listed 'Best First'.
Re: advice about 'stale' data --> Ima, shared dbh handles, and txns by perrin (Chancellor) on May 26, 2004 at 02:22 UTC
This is happening because of the isolation level that InnoDB tables use by default. If you change the isolation level to "repeatable read", the problem will go away. See my message about this on the mod_perl list for more. I'm planning to add this to the mod_perl documentation, since it seems like something that others will get bitten by.	[reply]
Re: advice about 'stale' data --> Ima, shared dbh handles, and txns by eclark (Scribe) on May 26, 2004 at 02:45 UTC
Are your Mason components caching output?	[reply]
Re: advice about 'stale' data --> Ima, shared dbh handles, and txns by water (Deacon) on May 26, 2004 at 09:37 UTC
The knowledge in this community is simply amazing. Many many thanks, Perrin, and thanks eclark -- it looks like both mason caching and innodb 'stale data' were occurring -- we're not mason experts, but the mason issue seemed to revolve around the difference between code in `<once>` blocks vs. `<init>` block, and yes, also, innodb was showing 'old' data to the second client as it hadn't committed in a while.	[reply] [d/l] [select]