ASP.NET C#: Caching Database Results, When/How Invalidate (on per-record basis)

Question

Hello, my application shows contact information (from approx. 2000 contact records with various detail information (names, phone numbers etc.) that form the base of a contact grid) which typically rarely changes.

A good number of users have the authority to edit this information though, and if it is edited, it is required that the change be visible immediately to everybody.

At the same time I'd like to limit refetching this information from the database every time anybody views this information, especially because days might go by without any change happening.

I could refetch the entire list every time a minute change is made, but that doesn't seem the right way either. So I'm looking for a way to cache everything, invalidate & refetch only a part that is changed (say, one record) while keeping everything else cached. The page would primarily feed off the cache then every time it is viewed, and fetching data becomes an exception.

How could I go about this design & code wise? Your help is very much appreciated.

Answer 1

On each insert and edit operation, you should replace your cache entry, then write to the database. Prior to sending an edit to the db, you should also check to ensure that the entry hasn't been invalidated (i.e. you got a dirty read), leading to data loss.

If you are really concerned about data integrity, you could keep a transaction log of the cache & db operations so you have a way of knowing what was in progress at the time of an outage.

Answer 2

It sounds like you need to keep a timestamp of every update. Every record gets a timestamp associated with it.

The first application load gets every record, and the app records the maximum timestamp.

Anytime a change is saved, the record's timestamp is updated. Then, when other users need to load changes, you can ask for anything updated after the client's last known maximum timestamp. The other users will get only the changes.

Answer 3

MS Enterprise Library Caching Block has good features to let you set up cache dependencies and invalidation based on Time based and Notification Based invalidation(including ones raised from a data store). Check it out

Answer 4

We have used Enterprise Library Caching block to do this. However, we use it without the backing store as described by Abhijeet Patel.

The cache is structured as a key / value pair.

When the user tries to get a record we check if it is in the cache, if yes return from cache, if no get from database and update the cache.

When the user changes a row, update the cache at the same time as the database is updated.

Note that this works when you have control of how the data is updated. You also have to do a little more work if the cache is stored on more than one servers.

Answer 5

On each insert and edit operation, you should replace your cache entry, then write to the database

I wouldn't do it this way. If there are multiple concurrent updates, you won't be sure that the cache replacements are done in the same order as the database updates (unless the cache update is part of the same transaction as the database update, which seems overkill).

Instead I would:

• Store items in the cache with a cache key derived from the primary key.

• On each update operation, simply remove the cache entry after the database update. In this way the cache will be refreshed with up-to-date data next time it's accessed.