Query cache doesn't invalidate

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

Hibernate version:
NHibernate 1.2.1

Name and version of the database you are using:
SQL Sever 2000

I have a few hql and sql statements of which the results should be saved in the cache. That works without problems. But if I insert/update/delete an entity where the corresponding db table is involved in these queries, the query cache keeps the old results. when I run one of these queries again, I retrieve the old results.
With the query cache disabled everything works as aspected, but I like to use the cache, because these queries are executed very often.

My configuration:

Code:

<property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider,NHibernate.Caches.SysCache</property>
<property name="cache.use_second_level_cache">true</property>
<property name="cache.use_query_cache">true</property>

The query execution:

Code:

ArrayList l2 = (ArrayList)Session.CreateSQLQuery(
                "SELECT ... FROM RIGHTS, ...")
                .SetCacheable(true)
                .SetCacheRegion("SecurityManager")
                .List();

The corresponging class to the table RIGHTS is Right. When I insert a new Right (the session is flushed and the entity is persisted successfully!) and then execute the above query again, I get the results from the query cache, without the new Right :-(

Suggestions are welcome!! Thanks!

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Sometimes Hibernate needs a little "goosing" in order to realize changes have been made to the database.

Try flushing the session after your insertion and before calling the query again.

If that doesn't work, also try wrapping the inserting piece explicitly inside a transaction (a code transaction, independently of whatever declarative transaction you might have in your environment) and flushing the session before calling the query.

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

Thanks for your reply! But I'm already doing this. Here is my method to save objects:

Code:

public virtual void Insert(T entity)
        {
            if (entity == null)
                throw new ArgumentNullException("entity");

            entity = SecurityManager.FilterInput<T>(entity, Permission.Insert);

            using (Repository<T> rep = new Repository<T>())
            {
                try
                {
                    rep.BeginTransaction();
                    rep.Save(entity);
                    rep.CommitTransaction();
                }
                catch (Exception ex)
                {
                    rep.RollbackTransaction();
                    log.Error("object of type " + typeof(T).Name + " could not be saved", ex);
                    throw;
                }
            }
            Refresh(entity);
        }

The methods BeginTransaction(), Save(), CommitTransaction() and RollbackTransaction() do as they say... Refresh(entity) also calls the equal named method of the current NHibernate session.

Any other ideas? Is this an issue in NHibernate's query cache or is it my mistake?

sallyxi · **Joined:** Wed May 07, 2008 8:18 am **Posts:** 3

Yeah,good method it is,pretty detailed...

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

I found the problem! The query spaces used in my queries are invalidated properly!
But I use custom SQL queries to retrieve some scalar values. Here NHibernate has a problem! NHibernate doesn't recongnize the query spaces in sql queries with scalar return values.
Now I added a new method to ISQLQuery and SqlQueryImpl:

public ISQLQuery AddQuerySpaces(string[] spaces);

With this method I manually add the used query spaces in my queries and in this way it works.

The next issue I found in NHibernate is that ISession.Timestamp is used for the query cache. This timestamp stays the same as long as the session exists! This timestamp should not be used in query cache - this approach works only with short sessions, not for long living ones.
So I changed the class StandardQueryCache and use Timestamper.Next() in the Put() method.

NHibernate should automatically recognize the query spaces in custom sql queries. If this is impossible, my AddQuerySpaces() method perhaps is an option...

Thanks for reading ;-)