Queue access to the database to avoid multiple cache items

Question

I have a music related ASP.NET web site which caches a lot of static information from the database on the first request.

Sometimes, the application is reset and cache is cleared while the application is on heavy load and then all http requests go to the database to retrieve that static data and cache it for other requests.

How can I ensure that only one request go to the database and cache the results, so that other request simply read that info from cache and not needlessly retrieve the same info over and over again.

Can I use thread locking? For example, can I do something like lock(this) { db access here }?

Answer 1

Yes, in your caching code, you'll want to put your database-accessing code inside a lock block. However, don't lock on this. Typically you'd do something like

private static readonly object staticObjectToLockOn = new object();

...

if (cache[cacheKey] == null)
{
   lock(staticObjectToLockOn)
   {
      // double-check the cache is still null inside the lock
      if (cache[cacheKey] == null)
      {
         // get data from the database, add to cache
      }
   }
}

Answer 2

Point is with using a generic lock, is that you are getting in serious trouble with alot of users, as the thread can be blocked for every cache item when your app starts. Furthermore will every thread still create it's own lock and query (and with 10 machines and 32 threads each, that's 320 requests!)

We us an approach like this (> 10 mio pageviews / day, and memcached):

When grabbing data from the database, we store an 'in progress' message in a similar cachekey (f.e. adding in-progress to the cachekey or something). When another thread cannot find the current item, it will check the in progress key. If the item is in progress, wait a couple of ms. and try to fetch again. If after a couple of moments (500 ms. ?) do the database action again from the current thread, to protect yourself from killing the app when an item keeps on 'in progress'.

This way you can create some sort of lock, but over multiple machines etc.

Without doing this, our CMS server will get like 200 of the same requests within the first second, so we really need cluster-wide locks.