Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

In an ordinary application, the same information may be retrieved from the database in several ways. If all those ways implement caching, you need to invalidate all those cache entries when updating an entry in the database.

Some pairing between updates and selects is easy to do. Let's take an example of a product 123 in the category 9 with a price of $12.50. If you select a product by ID:

select [...] from [Shop].[Product] where [ProductId] = 123

caching it with a key Product<Id:123>, it's not so difficult to invalidate this cache entry after updating a price of a product identified by its ID:

update [Shop].[Product] set [Price] = 14.99 where [ProductId] = 123

Things become more difficult when you have also to remember to invalidate the cache with the key MostExpensive<CategoryId:9> corresponding to the query:

select max([Price]) from [Shop].[Product] where [CategoryId] = 9

What is an effective way to keep track of cache entries? How to find, during development or code review, what entries must be invalidated when one or another piece of data is changed?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

MySQL invalidates its query caches based on the underlying table. If the table used in the query is edited with any INSERT, UPDATE, or DELETE, any query cached with references to that table is invalidated. (I know MySQL isn't the shining example of best practices, but in this case it works very well.)

So, in your example, if you ever update [Shop].[Product] you could invalidate all caches with references to it. You would need a second set of meta-data that relates every table to related cache entry keys, e.g. [Shop].[Product] => (Product<Id:123>, MostExpensive<CategoryId:9>).

A slightly more efficient route would be to NOT invalidate those keys that reference the table's primary key when those records are not updated. So if you update record with ProductId 345, do not invalidate key Product<Id:123>.

If you pass all of your queries through a caching layer that quickly grabs table names out of each query and handles all cache entries it's manageable. I feel like there's probably a completely different solution that's more direct, but I can't think of one at the moment.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.