9

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.

pioto
  • 2,472
  • 23
  • 37
  • Hello Pioto, see if this helps https://groups.google.com/forum/#!searchin/mybatis-user/stale$20results|sort:relevance/mybatis-user/vhYQ6_QaMsI/ETGW-8cZStsJ – Naveen Garg Apr 10 '15 at 06:49
  • That link seems to be broken, at least for me. Would you care to at least paraphrase what you found there in an answer? – pioto Apr 10 '15 at 14:37

2 Answers2

0

If you are doing select, insert, select in the same SqlSession, then the SqlSession cache is causing this issue. You will need to clear the cache manually after the insert: sqlSession.clearCache().

Naveen Garg
  • 91
  • 3
  • I don't see any SqlSession in my classpath... there is no explicit session or transactional stuff happening here, we just have an object that operates on a single SqlMapClient object, calling insert() and such... but, it may be possible that I recently changed things so that the SqlMapClient ends up possibly persisting longer than it did before (moving my Dao object from being a method-local one to a private class field of my controller)... – pioto Apr 10 '15 at 14:51
  • So you are reading configuration information from file and creating SQLMap object for use in your application, is that the correct understanding? – Naveen Garg Apr 10 '15 at 15:02
  • Correct; we do `SqlMapClientBuilder.buildSqlMapClient(reader);`, or the equivalent using a Spring bean, `...` – pioto Apr 10 '15 at 16:42
0

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" />
pioto
  • 2,472
  • 23
  • 37