Search code examples
javamysqlspringcachingibatis

Why is iBATIS giving stale results, even with caching disabled?

I have a web application which I've been slowly migrating from iBATIS 2 to JPA with Spring Data.

For the most part, things have been going well, with me just migrating the DAO for one domain object at a time. However, an issue that's been brought to my attention recently is that stale result lists are being show in some parts of the site.

For example, I have a "ticket" section, which shows a list of open tickets, and lets you view specific tickets on separate pages. When I create a new ticket, I can view that ticket on its specific page correctly. However, the open tickets list doesn't seem to show this new ticket until some time later.

Things I've tried to rule out:

  • I see this issue even on a system with MySQL's query cache disabled
  • I see this issue even when I set cacheModelsEnabled="false" in the iBATIS config.
  • I see this issue even when I completely remove the <cacheModel> element and cacheModel="x" attributes from my sqlMap file.
  • As soon as I restart the application, I see the up-to-date results.
  • When I execute the query iBATIS should be running here in a MySQL client, I do see the new ticket which is missing from iBATIS' results.
  • When I mocked up a simple ticket list using Spring MVC and Spring Data JPA, I do see the new ticket.

I've also tried to rule out some sort of weird transaction state with iBATIS, but it doesn't seem that any transaction is being used here at all.

What am I missing? Is there anything else I should be trying to figure this out? Or, should I just prioritize replacing the iBATIS layer completely with Spring Data JPA, which seems to be immune from this problem?

UPDATE

I've now gone through a lot of my recent changes with git bisect, and I've narrowed it down to a change that introduced Spring's org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.

So, it would seem that some transaction is living longer than it should. I'll add more logging to see if I can confirm this, and then look for a way to avoid using that filter.

Solution

  • So, it seems a combination of things ended up happening here:

    1. Most of my code was not explicitly using transactions.
    2. I had at some point changed to use Tomcat's JDBC Connection Pool, which does not reset autocommit by default when a connection is returned to the pool. I expect that my older DBCP-based stuff did this implicitly, though.
    3. The introduction of OpenEntityManagerInViewFilter may have caused a SET autocommit=0 to be called at some point, with no corresponding SET autocommit=1 later, if nothing had changed.
    4. By chance, or perhaps some design, the code that inserted a new record into the database and then immediately retrieved and showed it, seemed to get a different Connection than the code that showed my list of records.
    5. The default MySQL transaction isolation level of REPEATABLE-READ meant that my listings were showing the old results.

    The fix I've found, which seems to work in my testing so far, is to add these defaultAutoCommit and jdbcInterceptors attributes to my connection pool config:

    <Resource name="jdbc/DB" auth="Container" type="javax.sql.DataSource"
    factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    ...
    defaultAutoCommit="true" jdbcInterceptors="ConnectionState;StatementFinalizer" />