Did you know that
you can achieve table-level cache invalidation with ASP.NET today? Here's how:
The stock ASP.NET 1.0 CacheDependency class can monitor a local or network file. As the file changes, the appropriate cache entries are evicted from the cache. We can leverage this to achieve near-realtime cache invalidation as database data changes.
- Write a trigger for each table you want to monitor. The trigger will touch a file on the database's filesystem. The filename will correspond to the table name. This can be achieved by invoking the FileSystemObject scripting object.
CREATE PROCEDURE TouchFile(@FileName varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 RAISERROR ('could not create FileSystemObject',16,1)
-- touch the file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 2, 1
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
- Share the relevant folder in the database's filesystem.
- Setup CacheDependency instances to watch the appropriate files for changes. Use methods on the Response object to interoperate with page-level output caching. Use the raw caching API for arbitrary data caching.
This technique offers a number of advantages over the proposed polling mechanism in Whidbey (see earlier post).
- The invalidation will occur more quickly than with a polling mechanism.
- No polling is used - the caching API uses the FileSystemMonitor component, that in turn leverages a callback mechanism to detect filesystem changes.
Other notables about this approach:
- Like the Whidbey mechanisms, it is web garden and web cluster friendly.
- An extremely narrow race condition might exist. It occurs when two or more triggers attempt to touch the same file. One will succeed, others will fail. Will this result in stale data? Given the asynchronous nature of FileSystemMonitor, it is extremely unlikely. Nonetheless, you might want to bat around the scenarios in your head.
- If the file cannot be touched, the procedure will nonetheless complete. That is, the race does not defeat the transaction and is safe.
- This is a hack.