Database Cache Invalidation, In ASP.NET 1.x

posted on 2004-10-09 at 23:35:31 by Joel Ross

We've come up to a problem in my current project. We have two applicatons sharing a database. One will be a public facing website, and the other will be an internal administration website. They share a business layer, but not physically. The processing is data intensive, so we have a need to cache the data that needs to be retrieved.

The cache will be built in each application, but only updated in the admin application. So how do we expire the data in our web application? We came up with a few distinct options.

1. Use a database table, which holds the cache keys and the date they were last updated. When the cache is accessed, the application would check the date it cached the data agains the last updated date in the database, and know if it had been updated. This is (from what I remember) similar to how Whidbey's database cache invalidation would work with SQL Server 2000.

2. Have the web app expose a web service that the admin application could call and pass a cache key that would expire that cache item.

3. The web application would use a timer based expiration strategy. For more dynamic items, the time would be shorter. For items changed less, the timer would be longer.

4. Build a Cache Manager component that each application would use, and each website would remote to it to get it's data.

Now, the downside of each:

1. Overhead, both in writing the database access stuff, and the database hit for each cache access (what cache tries to eliminate). Yes, the hit is smaller, but it's still a hit.

2. This probably would work, but there are some items that cached that are updated directly in the database, and not through the admin web site, meaning the web service would have to be called manually. Too much human error.

3. Data would just about be gauranteed to be stale at some point. Not really acceptable.

4. This is a good solution, but we felt the overhead of writing this would be too much, and not easily maintained.

So we got to thinking, "Someone has to have written a solution to this problem!" On to Google. And a solution. It's an admited hack, but it should work. Basically, you set your cache to based on a file, using the FileSystemMonitor. Then, write a trigger on the cached tables that touch the file the cache is based off of.

Why is this better? Well, the cache invalidation takes place instantly, and the database hit doesn't happen on every cache access. Also, the FileSystemMonitor works on another thread, so the file access (which may be on a different service), which may be slow, won't be on the same thread as the current page.

We haven't implemented it yet, but that's the plan right now. If you have any reasons this won't work, or you have a better solution, let me know. We're always open for suggestions!

Categories: ASP.NET