Hibernate query returns stale data

Question

I have a hibernate query (hibernate 3) that only reads data from the database. The database is updated by a separate application and the query result does not reflect the changes in the database.

With a bit of research, I think it may have something to do with the Hibernate L2 cache (I don't think it's the L1 cache since I always open a new session and close it after it's done).

Session session = sessionFactoryWrapper.getSession();
List<FlowCount> result = session.createSQLQuery(flowCountSQL).list();
session.close();

I tried disabling the second-layer cache in the hibernate config file but it's not working:

<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.cache.use_query_cache">false</property>
<propertyname="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

I also added session.setCacheMode(CacheMode.Refresh); after Session session = sessionFactoryWrapper.getSession(); to force a refresh on the L1 cache but still not working...

Is there another way to pick up the changes in the database? Am I doing something wrong on how to disable the cache? Thanks.

Update: I did another experiment by monitoring the database query log:

1. Run the code the 1st time. Check the log. The query shows up.
2. Wait a few minutes. The data has changed by another application. I verified it through MySql Workbench. To distinguish from the previous query I add a dummy condition.
3. Run the code the 2nd time. Check the log and the query shows up.

Both time I'm using the same query but since the data has changed, the result should be different but somehow it's not...

Answer 1

Just in case it IS the 1st Level Cache

Can you show the query you make ?

See following Bugs:

https://hibernate.atlassian.net/browse/HHH-9367

https://jira.grails.org/browse/GRAILS-11645

Additional:

http://howtodoinjava.com/2013/07/01/understanding-hibernate-first-level-cache-with-example/

http://www.dineshonjava.com/p/cacheing-in-hibernate-first-level-and.html#.VhZ7o3VElhE

Repeatable finder problem caused by Hibernates 1st Level Cache

To be clear, both test succeed - not logically at all:

userByEmail('foo@bar.com').email != 'foo@bar.com'

Complete Test

@Issue('https://jira.grails.org/browse/GRAILS-11645')
class FirstLevelCacheSpec extends IntegrationSpec {
    def sessionFactory

    def setup() {
        User.withNewSession {
            User user = new User(email: 'test@test.org', password: 'test-password')
            user.save(flush: true, failOnError: true)
        }

    }

    private void updateObjectInNewSession(){
        User.withNewSession {
            def u = User.findByEmail('test@test.org', [cache: false])
            u.email = 'foo@bar.com'
            u.save(flush: true, failOnError: true)
        }
    }

    private User userByEmail(String email){
        User.findByEmail(email, [cache: false])
    }

    def "test first update"() {
        when: 'changing the object in another session'
        updateObjectInNewSession()

        then: 'retrieving the object by changed identifier (no 2nd level cache)'
        userByEmail('foo@bar.com').email == 'foo@bar.com'
    }

    def "test stale object in 1st level"() {
        when: 'changing the object after pulling objects to cache by finder'
        userByEmail('test@test.org')
        updateObjectInNewSession()

        then: 'retrieving the object by changed identifier (no 2nd level cache)'
        userByEmail('foo@bar.com').email != 'foo@bar.com'
    }
}

Answer 2

Hibernate's built-in connection pooling mechanism is bugged.

Replace it with a production quality alternative like c3p0.

I had the exact same issue where stale data was returned until I started using c3p0.

Answer 3

How old of a change is the query reflecting? If it is showing the changes after sometime, it might have to do with how you obtain your session.

I am not familiar with the SessionFactoryWrapper class, is this a custom class that you wrote? Are you somehow caching the session object longer than it is necessary? If so, the query will be reusing the objects if it has already been loaded in the session. This is the idea behind the repeatable read semantics that Hibernate guarantees.

You can clear the session before running your query and it will then return the latest data.

Answer 4

In order to force a L1 cache refresh you can use the refresh(Object) method of Session.

From the Hibernate Docs,

Re-read the state of the given instance from the underlying database. It is inadvisable to use this to implement long-running sessions that span many business tasks. This method is, however, useful in certain special circumstances. For example

• where a database trigger alters the object state upon insert or update
• after executing direct SQL (eg. a mass update) in the same session
• after inserting a Blob or Clob

Moreover you mentioned that you added session.setCacheMode(CacheMode.Refresh) to force a refresh on the L1 cache. This won't work because, CacheMode doesn't have to do anything with L1 cache. From the Hibernate Docs again,

CacheMode controls how the session interacts with the second-level cache and query cache.

Answer 5

Without second-level cache and query cache, hibernate will always fetch all data from database in a new session.

You can check which query exactly is executed by Hibernate by enabling DEBUG log level for org.hibernate package (and TRACE level for org.hibernate.type if you want to see bound variables).