Automatically maintaining cache consistency

[Perrin Harkins]

On 8/29/07, Mohammad J <desertland at gmail.com> wrote:
> At the worldcomp '07 conference, there was a method presented that is
> available online at http://cacheconsistency.com.  In short, by defining
> dependency information in your source code whenever you set a cache element,
> you can analyze the source code at deploy-time to identify all the
> dependencies.  Then, whenever a SQL query is issued to update the underlying
> data, if necessary, all the appropriate cache elements will also be
> invalidated.

It can't be done, at least not in a precise way.  There is no way to
know exactly which data a SQL statement will affect without actually
being able to fully execute the statement the same way your database
would, at which point all the speed advantage is gone.

What you can do is err on the side of safety and delete more data than
is affected in order to avoid having to calculate the exact data to
delete.  That's the approach taken by things like the MySQL query
cache, which deletes the cache for a whole table every time a query
that affects that table is run.

- Perrin