From: 李可强 <52194501011@stu.ecnu.edu.cn>
To: tgl <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re:Fwd: BUG #17017: Two versions of the same row of records are returned in one query
Date: Wed, 19 May 2021 13:04:07 +0800
Message-ID: <tencent_53E42BE16C22D6872833143B@qq.com> (raw)
In-Reply-To: <CACig7B6=D8NDExtZaBGrU=DpF=6esy+hJEDQ+vmDw6g4b2ponw@mail.gmail.com>
References: <17017-c37dbbadb77cfde9@postgresql.org>
<3229567.1621354699@sss.pgh.pa.us>
<CACig7B6=D8NDExtZaBGrU=DpF=6esy+hJEDQ+vmDw6g4b2ponw@mail.gmail.com>
Hi Tom,
I think the repeatable read transaction should give priority to see the new version written by itself, e.g., the insertion of the above example. There is no need to return the old version written by committed transactions. The query result fo Session B is very confusing!
------------------ Original ------------------
From: "刘沛源"<lpy.henu@gmail.com>;
Date: Wed, May 19, 2021 09:05 AM
To: "52194501011"<52194501011@stu.ecnu.edu.cn>;
Subject: Fwd: BUG #17017: Two versions of the same row of records are returned in one query
---------- Forwarded message ---------
发件人： Tom Lane <tgl@sss.pgh.pa.us>
Date: 2021年5月19日周三 00:18
Subject: Re: BUG #17017: Two versions of the same row of records are returned in one query
To: <lpy.henu@gmail.com>
Cc: <pgsql-bugs@lists.postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> Schema and Initial data:
> Create Table t(a int primary key, b int);
> Insert into t values(1,2);
> Insert into t values(2,3);
> [Time0, SessonA]
>> Begin;
>> set transaction isolation level repeatable read;
>> Select * from t where a=1;
> [Time1, SessonB]
>> Begin;
>> set transaction isolation level read committed;
>> Delete from t where a=2;
>> Commit;
> [Time2, SessonA]
>> Insert into t values(2,4);
>> Select * from t where a=2;
> Here, we expect PostgreSQL Server to return a row:
> 2 3
> However, it returns two rows:
> 2 4
> 2 3
In repeatable read mode, this doesn't surprise me. Session A
surely must return the (2,3) row, since it isn't supposed to
"see" the results of Session B's commit yet. And it would be
mighty surprising for it not to see its own insertion, so you
get (2,4) as well.
In serializable mode, I'd expect this situation to throw a
serialization error, and it does (but you must run BOTH
transactions in serializable mode; there are not guarantees
with a mix of serializable and non-serializable transactions).
So I think the answer is "if you care about this, use
serializable mode". Repeatable read mode meets the
requirements of the SQL spec, but it doesn't guarantee
no anomalies.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: 52194501011@stu.ecnu.edu.cn, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re:Fwd: BUG #17017: Two versions of the same row of records are returned in one query
In-Reply-To: <tencent_53E42BE16C22D6872833143B@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox