update strategy

[mrot]

Alex Stapleton wrote:

> Update your DB access layer (if you can) to check the affected row  
> count after every query. If the query affected any rows then either  
> update the cache or delete the item so it gets updated on the next  
> cache miss. You might be able to do it at that level if your lucky, I  
> don't know anything about your caching system. Personally i'd do it  
> in the application rather than in the database though if possible.
>

I also prefer to change application than database.
The problem is that searching key (which is SQL select statement) is 
different than
database update statement (SQL update ....).
So the process which updates the data in database must in some way map
the query to searching key to update/delete appriopriate entry in the cache.
Eg. database table looks as:
id              value
.....           ..........
2             somevalue1
......
Corresponding cache fragment looks as:
key                                       value
.......                                      ............
select from tbl where id=2     somevalue1
......                                       ...........

The readers do:
key="select from tbl where id=2";
result=memcache->get(key);
if (result==NULL) {
    result=database->get(key);
    memcache->set(result);
}

But the writers do:
query="update tbl set value='somevalue2' where  id=2";
database->execute(query);

Now we have inconsistence between databse and cache.
I agree that the simplest solution is to clean up the whole cache but 
then it's
no differnece between memcache and database cache.
This simple insert query can be mapped easily to select query.
But what in case of more complicated queries ????

Iam not experienced memcache user so maybe I make some mistake,
maybe the key shouldn't be SQL queries....

Regards