MySQL Forums
Forum List  »  Performance

Mysterious Cache Problem
Posted by: Carlos Conti
Date: October 29, 2015 12:47AM

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.



Options: ReplyQuote

Written By
Mysterious Cache Problem
October 29, 2015 12:47AM
October 29, 2015 03:04AM
October 30, 2015 06:16PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.