NHIbernate SysCache2 and SQLDependency problems

Question

I've set enable_broker on my SQL Server 2008 to use SQLDepndency

I've configured my .Net app to use Syscache2 with a cache region as follows:

  <syscache2>
      <cacheRegion name="BlogEntriesCacheRegion" priority="High">
        <dependencies>
          <commands>
            <add name="BlogEntries" 
                 command="Select EntryId from dbo.Blog_Entries where ENABLED=1" 
            />
          </commands>
        </dependencies>
      </cacheRegion>
  </syscache2>

My Hbm file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

  <class name="BlogEntry" table="Blog_Entries">

    <cache usage="nonstrict-read-write" region="BlogEntriesCacheRegion"/>
    ....
  </class>
</hibernate-mapping>

I also have query caching enabled for queries against BlogEntry

When I first query, the results are cached in the 2nd level cache, as expected.

If I now go and change a row in blog_entries, everything works as expected, the cache is expired, it get's this message:

2010-03-03 12:56:50,583 [7] DEBUG NHibernate.Caches.SysCache2.SysCacheRegion - Cache items for region 'BlogEntriesCacheRegion' have been removed from the cache for the following reason : DependencyChanged

I expect that. On the next page request, the query and it's results are stored back in the cache. However, the cache is immediately invalidated again, even though nothing has further changed.

DEBUG NHibernate.Caches.SysCache2.SysCacheRegion - Cache items for region 'BlogEntriesCacheRegion' have been removed from the cache for the following reason : DependencyChanged

My cache is constantly invalidated every subsequent time with no changes to the underlying data. Only a restart of the application allows the cache to operate again - but only the first time the data is cached (again, the first dirtying of the cache, causes it to never work again)

Has anyone seen this problem or got any ideas what this could be? I was thinking that syscache2 needs to handle the SQLDependency onChange event, which it probably is doing - so I don't understand why SQL Server keeps sending SQLDependency depedencyChanged.

thanks

Answer 1

We are getting the same problem on one database instance, but not on the other. It definitely seems to be some kind of permission problem on the database end, because the exact same NHibernate configuration is used in both cases.

In the working case the cache behaves as expected, in the other (which is a database engine which has much stricter permissions) we get the exact same behaviour you mentioned.