For most Web applications, caching relatively static database data in memory can boost application performance several fold. Caching not only takes some of the load off your database server, but it can also eliminate a network round-trip. Often, a Web application stores its cache in a memory location (either in-process or out of process) on a Web server or an application server; optimally, you'd store the cache in the Web application's local memory. In ASP.NET, the System.Web.Caching.Cache class is stored in local memory, which can greatly increase application performance. ASP.NET has made data-cache implementation so easy and safe that many people have switched their applications to ASP.NET, usually for the performance gains they can get from caching.
Most developers understand that caching is good for performance, but it has one major drawback. Namely, when the cache's underlying data changes, you can have stale data in your cache. No matter how fast your application can retrieve the data from the cache, if the data isn't current, it probably does users no good. ASP.NET lets you create time-based dependencies or dependencies between items in the cache and files in the file system. For example, if you use the ASP.NET Cache object to cache an XML file into memory and have cache and file dependencies set up, then when the file changes, the object automatically invalidates the cache. Or, if you have a time dependency defined, the file's contents would expire after the specified time constraint. Caching combined with cache dependencies is very powerful because it lets you cache data on your Web server without having to worry about it becoming stale. The only limitation on cache dependencies in Microsoft .NET Framework 1.1 and 1.0 is that these versions have no support for database cache dependencies. But what if you aren't developing in ASP.NET? What if you support Web applications on multiple platforms and still need database cache dependencies? Let's look at an example application with a flexible, generic design that lets you gain all the performance advantages of caching in your applications without having to worry if your cache has become out of sync with your database.
In this example scenario, you have Web applications operating alone or as part of a Web-server farm that consumes data from a relational database. You need to be able to invalidate Web-application data caches when the database tables that the caches are derived from change. The solution is to associate caches and their dependencies in a data store, essentially providing the framework to link a cache and its associated database tables. Also, you can define callbacks from the information stores that are connected with local memory caches within Web applications. When updates happen on the table that a cache is based on, a trigger on that table asynchronously invalidates the Web applications' local memory caches. Having the invalidation occur asynchronously is important so that SQL Server doesn't hold locks on the updated table while invalidating the cache.
The advantage of using this design is that Web applications can reap all the benefits of caching database data and still be able to invalidate their local caches when the database data changes. The downside to this design is that it requires extra processing on both the Web server and the database server to set up and invalidate the caches. You shouldn't use this design in scenarios where the database data will be constantly changing; the extra cache-invalidation processing will defeat the purpose of the cache.
This implementation uses SQL Server to track the cache dependencies and notify an application that its cache has expired when the underlying table's data changes. To facilitate the implementation of cache dependencies, I created the database structure that Figure 1 shows. This structure lets you associate more than one database table with the same Web-application cache. (For other solutions to this problem, see the sidebar "Related Solutions.")
Database Cache Dependencies Old and New
In the upcoming .NET Framework 2.0, Microsoft adds support for database cache dependencies, requiring either SQL Server 2000 or 7.0 or SQL Server 2005 (formerly code-named Yukon). The .NET database cache dependency support doesn't work with other databases. Table 1 shows the functional differences between using SQL Server 2000/7.0 and Yukon for database cache dependencies in .NET Framework 2.0. But Microsoft probably won't release .NET Framework 2.0 and Yukon to production for several months at least. So if you need to implement caching now or you need to add this functionality to Web applications that run on platforms other than ASP.NET, this article's solution is for you.
I wanted to develop a solution that works for Active Server Pages (ASP), ASP.NET, Common Gateway Interface (CGI), Cold Fusion, or any other Web-application platform, so I looked for a generic design. My next goal was to build a scalable solution that I could use in a production environment. With performance and scalability in mind, I then implemented the design, using SQL Server 2000 to track database cache dependencies and to asynchronously inform Web applications that a database cache has expired when the cache's underlying table data has changed. (I didn't deal with time dependencies in this solution, however.)
Note that this solution doesn't require SQL Server 2000; you could implement it with almost any relational database management system (RDBMS) that supports triggers, a way of making HTTP calls, and (optionally) asynchronous programming. SQL Server is my database of choice, so I used SQL Server 2000 as the cache-dependency server.
In the other solutions I've seen, the cache invalidations inside the trigger all happen synchronously. This synchronous invalidation could lead to serious performance degradation in a production environment. Even though the triggers in the other solutions don't do much work, they have to call an extended stored procedure. One of the procedures makes an HTTP call to a Web server, and if any delays occur during that HTTP call, either on the Web server or across the network, the trigger will block the table's transaction. Also, if it encounters an error during the HTTP call, SQL Server rolls back the UPDATE action on the table. The transaction on the table might have been legitimate, but because of an error during cache invalidation, nobody can update the table.
I wanted to avoid this problem and decouple the callback logic so that the cache invalidation would happen asynchronously. My solution dynamically creates a SQL Server job to invalidate the cache, then sets the job to execute a few seconds after creation and deletes itself if execution is successful. This way, the callback executes on a different thread from the trigger on the database table and doesn't block the current update transaction. Figure 2 uses a sequence diagram to show the flow of actions when the trigger fires. As Figure 2 shows, the table trigger first creates a job, then adds a job step to call the sp_ProcessCacheExpiryQueue stored procedure to invalidate the cache items. The SQL Server Agent service then executes the job, which calls the stored procedures that make an HTTP call back to the Web server to expire the cache.
Prev. page
[1]
2
3
next page 
