When configuring query cache and second level object cache I came across a behavior which I don't know if it's a bug or a missing feature.
1. Assuming all caches all empty, we run a query. The ids of the returned objects are stored in the query cache and the objects are stored in the L2 object cache. Great so far.
2. Now, the query cache expires before the object cache does. The query is re-ran and the ids of the returned objects are cached in the query cache.
3. The object cache expires. The query cache is still valid, so the ids are returned from the query cache. Unfortunately, the objects are no longer cached and hibernate fetches them all one-by-one, which takes forever.
Now the question: Why doesn't the query cache update the objects returned in step 2? The objects were fetched form the database, so we have a fresher version of them and an update of the L2 object cache could be performed. This way, the object cache wouldn't expire in step 3.
This behavior forces the one-by-one fetching of objects:
When the query cache expires first, it won't cache the objects (as described), which will eventually lead to a situation where the query cache is valid and the object cache expired.
When the object cache expires with the query cache still valid, hibernate performs a one-by-one fetching of all objects that are returned by the query cache.
I can see 2 solutions to this problem:
1. Instead of fetching missing objects one-by-one, fetch them with an "in" statement so that only one query is executed.
2. Refresh L2 objects when the query cache expires and the query is ran.
Oracle 11g, Ehcache, Hibernate 4.1.9