[pgpool-general: 8344] Re: Stale data in simultaneously transactions

[Tatsuo Ishii]

> Hi,
> 
> I found an issue in simultaneously transactions.
> 
> Do the following -
> 
> Transaction A:
> Select * from city where city_id = 1;
> 
> Transaction B:
> Update city set city_name=‘City’ where city_id=1;
> 
> Transaction B: commit;
> 
> Transaction A: commit;
> 
> 
> Select * from city where city_id = 1;  -> will return from cache with stale
> data.

Yes, that's a limitation in Pgpool-II query cache. Pgpool-II
invalidates cache when any modification is made to the relevant
tables. But in this case when

> Update city set city_name=‘City’ where city_id=1;

is executed, the cache for "Select * from city where city_id = 1;" is
not created yet because transaction A is not committed yet. So
transaction B cannot remove the cache for it.
When transaction A commits, the cache is created.

I think there's no way to avoid it. The only workaround is setting
memqcache_expire.

Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp