pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #17301: SELECT gets weird result while two transactions are submitted concurrently 2+ messages / 2 participants [nested] [flat]
* BUG #17301: SELECT gets weird result while two transactions are submitted concurrently @ 2021-11-27 18:14 PG Bug reporting form <noreply@postgresql.org> 0 siblings, 1 reply; 2+ messages in thread From: PG Bug reporting form @ 2021-11-27 18:14 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: dddinary@163.com The following bug has been logged on the website: Bug reference: 17301 Logged by: Dinary Dai Email address: dddinary@163.com PostgreSQL version: 14.0 Operating system: Ubuntu Description: /* 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`. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17301: SELECT gets weird result while two transactions are submitted concurrently @ 2021-11-28 16:51 Tom Lane <tgl@sss.pgh.pa.us> parent: PG Bug reporting form <noreply@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2021-11-28 16:51 UTC (permalink / raw) To: dddinary@163.com; +Cc: pgsql-bugs@lists.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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-11-28 16:51 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-11-27 18:14 BUG #17301: SELECT gets weird result while two transactions are submitted concurrently PG Bug reporting form <noreply@postgresql.org> 2021-11-28 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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