NHibernate - Query misses the query cache after saving a new entity

Question

I have NHibernate (with NHibernate.Linq and Fluent NHibernate) set up with query caching. Everything works fine until I do a session.Save(new Widget()) (i.e. SQL INSERT). After that point, all queries on that type Widget miss the query cache. Queries on other entity types are cached just fine.

using (ISession session = MySessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        // this INSERT screws things up
        var widget = new Widget {Name = "Foo"};
        session.Save(widget);

        var query = (from w in session.Query<Widget>().Cacheable()
                     where w.Name == "Bar"
                     select w);

        var fetched1 = query.FirstOrDefault();
        var fetched2 = query.FirstOrDefault(); // miss?!

        transaction.Commit();
    }
}

If I start a new Transaction, the problem persists. If I start a new Session, the problem goes away. This seems kind of strange, since my understanding was the second level cache gets reset per SessionFactory (not Session).

I don't think this matters, but I am using the HashtableCacheProvider, since I'm just testing right now.

It's not an error in the sense that exception was thrown. Rather, the second query (var fetched2 = query.FirstOrDefault();) skips the query cache and directly hits the database (which is not the expected behavior). — Joel Verhagen
Have you enabled the query cache? I think you have to do that separately from the entity cache. — Andrew Whitaker
Yes. Before doing a session.Save(...), the query caching works perfectly. — Joel Verhagen

Radim Köhler Radim Köhler · Accepted Answer · 2013-01-13T07:10:30

The behaviour you've described is correct (more here).

The update timestamp cache is not updated until you commit the transaction! This is to ensure that you will not read "uncommitted values" from the cache.

Whenever there is a change on a type which we've got in Cache - cached data are stale... until the complete transaction is commited.

Imagine that you've cached results of this filter:

 var query = (from w in session.Query<Widget>().Cacheable()
  where w.Name == "B*" // all names starting with B
  select w);

And later will add new Widget:

var widget = new Widget {Name = "Brigitte"};
session.Save(widget);
// explicit Flush is not needed, 
// but then, until Commit(), query will never return Brigitte
session.Flush(); // to immediately execute INSERT

If the query would be still cached, Brigitte will never appear...

And while in Transaction, queries with FirstOrDefault() are executed immediately - the write operations could wait for a Flush on Commit.

Because of the Transaction, all contained operations (insert, udpate, select) cannot profit from caching, because only Transaction as a batch makes sense. So until commit is called, no cache could be used.

Many detailed and very helpful information could be found here: First and Second Level caching in NHibernate

The timestamp cache is updated whenever a table is written to, but in a tricky sort of way:

When we perform the actual writing, we write a value that is somewhere in the future to the cache. So all queries that hit the cache now will not find it, and then hit the DB to get the new data. Since we are in the middle of transaction, they would wait until we finish the transaction. If we are using low isolation level, and another thread / machine attempts to put the old results back in the cache, it wouldn't hold, because the update timestamp is into the future.

When we perform the commit on the transaction, we update the timestamp cache with the current value.

NHibernate - Query misses the query cache after saving a new entity

1 Answers