Home > Uncategorized > Refreshing a Cache using SqlCacheDependency & CacheItemRemoved callback, Infinite Loops & StackOverFlowException

Refreshing a Cache using SqlCacheDependency & CacheItemRemoved callback, Infinite Loops & StackOverFlowException

Recently I ran into a strange issue with SqlCacheDependency implementation.
Last sprint (not the agile term) has been mercilessly 12 days long without a break and has taken the steam out of me I guess.
Tired minds make mistakes, And last couple of days there has been quite a few of them.
Anyway, back to the issue on hand. I was trying to do a sample appplication to solve a simple problem.
The Problem is that the CacheItemRemoved callbacks do not have the HttpContext.Current.
As a work around / hack we were calling the RefreshCache in a webservice which has access to HttpContext.Current.
Recently I read somewhere that if we use HttpRuntime.Cache we can avoid the webservice call.
I set out yesterday evening to make a sample application to assert the fact that it works.
I was using SqlCacheDependency to invalidate the cache.
The code snippet I was using in the application.

namespace CacheDependencyDemo
{
public class CacheManager
{
public void SetupCacheDependency()
{
using (SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT au_lname from authors where au_id =’172-32-1176’"
cmd.CommandType = CommandType.Text;

SqlCacheDependency cacheDependency = new SqlCacheDependency(cmd);

string lastName = (string)cmd.ExecuteScalar();
HttpRuntime.Cache.Insert("LastName", lastName, cacheDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, this.RegionIDChangedCallback);

}
}
}

public void RegionIDChangedCallback(String key, object value, CacheItemRemovedReason removedReason)
{
if (removedReason == CacheItemRemovedReason.DependencyChanged)
{
SetupCacheDependency();
}
}
}
}

namespace CacheDependencyDemo
{
public class Global : System.Web.HttpApplication
{
protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString);
CacheManager cacheManager = new CacheManager();
cacheManager.SetupCacheDependency();
}

protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Start(WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString);
}
}
}

I was noticing a strange problem here. The Cache.Insert call was also resulting in a immediate CacheItemRemoved callback with CacheItemRemovedreason as DependencyChanged. The callback sets up the cache again thus going into an infinite loop. I decided to get to the bottom of the issue. The actual issue was that I had to use owner qualified table names in the select query. "SELECT [dbo].[authors].[au_lname] from [dbo].[authors] where [dbo].[authors].[au_id] =’172-32-1176’" did the trick. After a good sleep I could debug the issue in just 10 minutes.

CacheDependencies are really a cool feature. But I dont know why its been designed in a virtually non debuggable manner. Agreed that this has been documented, but throwing an exception would have did the trick and saved a couple of hours. Poor API Design choice IMHO.

The article "Using and Monitoring SQL Server 2005 Query Notification"
http://www.simple-talk.com/sql/sql-server-2005/using-and-monitoring-sql-2005-query-notification/ had been of great help to debug SQL Cache Dependency issues.

Advertisements
Categories: Uncategorized
  1. thiru
    June 4, 2007 at 12:08 pm

    hi da sendhil..i keep an eye on your blog (RSS feeds) – so, you do post personal updates occasionally – which is good. I am in middle of things between study and work – so, will email u once cross the bridge!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: