Mysterious Cache Problem

[Carlos Conti]

Hi all,

I have a curious apparently cache problem, which in fact seems to be a not adequate dealing with transactions. I work with an Hibernate persistence layer and MySQL InnoDB engine, with the default REPEATABLE-READ tx isolation level.

The point is that I am finding many situations where data is effectively written to the db, but then not refreshed in the system, after a while it gets solved (and sometimes after a long while > 10min).

I have checked Hibernate cache and its statistics per session show no interaction with L2C (0 hits).

So I tried to dig in the storage engine to see if I found anything and I believe I have discovered something, which I expect someone that can properly interpret the results of a 'show engine innodb status' can confirm.

I have two main scenarios: 1) where I always enclose my operations inside application managed transactions (in mysql world I believe is a regular tx) and 2) where I don't do that, basically consisting of reading operations.

The point is that 'show engine innodb status' throws different outcomes depending on which scenario I'm in. For the first one, I get no additional read views (no started active transactions). For the second one I get always an additional active open transaction.

Since it happens only for reading operations, I don't understand why data is being cached (or better said staled), since update/delete operations happen within transactions which don't remain open (they're active but not started in the innodb storage engine after transaction execution, why???)

If anyone can help me understand what's really happening please. This apparently random caching problem is posing us serious problems.

Many thanks in advance for your attention and sorry for the long post.

Regards,

Carlos.