Re: Database Caching

On Sat, 2 Mar 2002, Justin Clift wrote:

> Hi guys,
>
> Stephan Szabo wrote:
> <snip>
> > The question is, when it's invalidated, how does it become valid again?
> > I don't see that there's a way to do it only by query string that doesn't
> > result in meaning that the cache cannot cache a query again until any
> > transactions that can see the prior state are finished since otherwise
> > you'd be providing the incorrect results to that transaction. But I
> > haven't spent much time thinking about it either.
>
> i.e. Lets take a web page where clients can look up which of 10,000
> records are either .biz, .org, .info, or .com.
>
> So, we have a database query of simply:
>
> SELECT name FROM sometable WHERE tld = 'biz';
>
> And lets say 2,000 records come back, which are cached.
>
> Then the next query comes in, which is :
>
> SELECT name FROM sometable WHERE tld = 'info';
>
> And lets say 3,000 records come back, which are also cached.
>
> Now, both of these queries are FULLY cached. So, if either query
> happens again, it's a straight memory read and dump, no disk activity
> involved, etc (very fast in comparison).
>
> Now, lets say a transaction which involves a change of "sometable"
> COMMITs. This should invalidate these results in the cache, as the
> viewpoint of the transaction could now be incorrect (there might now be
> less or more or different results for .info or .biz). The next queries
> will be cached too, and will keep upon being cached until the next
> transaction involving a change to "sometable" COMMITs.

But, if there's a transaction that started before the change committed,
then you may have two separate sets of possible results for the same query
string so query string doesn't seem unique enough to describe a set of
results. Maybe I haven't read carefully enough, but most of the proposals
seem to gloss over this point.