From: Tom Lane <tgl@sss.pgh.pa.us>
To: dddinary@163.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17301: SELECT gets weird result while two transactions are submitted concurrently
Date: Sun, 28 Nov 2021 11:51:06 -0500
Message-ID: <708274.1638118266@sss.pgh.pa.us> (raw)
In-Reply-To: <17301-61fbc4ef6a49bf89@postgresql.org>
References: <17301-61fbc4ef6a49bf89@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> /* init */ create table t(a int primary key, b int);
> /* init */ insert into t values (1, 2), (2, 3)
> /* t1 */ begin;
> /* t1 */ set transaction isolation level repeatable read;
> /* t1 */ select * from t where a = 1;
> /* t2 */ begin;
> /* t2 */ set transaction isolation level repeatable read;
> /* t2 */ delete from t where a = 2;
> /* t2 */ commit;
> /* t1 */ update t set a = 2 where a = 1;
> /* t1 */ select * from t where a = 2; -- [(2, 3), (2, 2)]
> /* t1 */ commit;
> The final SELECT statement gets result [(2, 3), (2, 2)], which violates the
> primary key constraint on column `a`.
This is operating as designed. There are only three plausible behaviors
in this situation:
1. Fail t1's last SELECT (or, perhaps, its UPDATE). You'll get that
if you use SERIALIZABLE mode.
2. In t1's last SELECT, don't show the committed-dead (2,3) row.
This violates the premise of REPEATABLE READ: t1 could see that
row at the start of its run, and it hasn't modified it, so it
should still see it.
3. In t1's last SELECT, show both rows.
We choose #3. If you want #2, you should be using READ COMMITTED
mode, while if you want #1, you should be using SERIALIZABLE.
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: tgl@sss.pgh.pa.us, dddinary@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17301: SELECT gets weird result while two transactions are submitted concurrently
In-Reply-To: <708274.1638118266@sss.pgh.pa.us>
* 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