[pgpool-general: 8344] Re: Stale data in simultaneously transactions
Tatsuo Ishii
ishii at sraoss.co.jp
Mon Jul 18 07:35:54 JST 2022
> 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
More information about the pgpool-general
mailing list