From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: cuiziyu20@otcaix.iscas.ac.cn
Subject: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction
Date: Thu, 09 Oct 2025 15:44:15 +0000
Message-ID: <19081-3745d3e571c420de@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19081
Logged by: Ziyu Cui
Email address: cuiziyu20@otcaix.iscas.ac.cn
PostgreSQL version: 18.0
Operating system: Ubuntu-20.04
Description:
At the Repeatable Read isolation level, the target rows seen by UPDATE and
INSERT within the same transaction are inconsistent.
Steps to reproduce:
/* init */ CREATE TABLE t0 (c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t0 (c1, c2) VALUES (0, 0);
-- TRANSACTION 1;
BEGIN;
UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
COMMIT;
-- TRANSACTION 2;
BEGIN;
INSERT INTO t0 (c1, c2) VALUES (2, 2);
INSERT INTO t0 (c1, c2) VALUES (0, 12);
UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 5;
COMMIT;
-- Submit Order
/* T1 */ BEGIN;
/* T1 */ UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
/* T2 */ BEGIN;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (2, 2);
/* T1 */ COMMIT;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (0, 12);
/* T2 */ UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 5;
/* T2 */ COMMIT;
SELECT * FROM t0;
c1 | c2
----+----
5 | 5
2 | 2
0 | 12
After transaction 1 was committed, the second INSERT in transaction 2
successfully inserted the value (0, 12) because transaction 1 had updated
the row (0, 0). However, the UPDATE in transaction 2 did not update the row
(5, 5) that were updated in transaction 1.
The final database result seems to indicate that the second INSERT in
transaction 2 can see the update made by transaction 1, but the UPDATE in
transaction 2 cannot see the update made by transaction 1. Why do these two
statements show different effects when exposed to the influence of
transaction 1?
Another case:
/* init */ CREATE TABLE t0 (c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t0 (c1, c2) VALUES (0, 0);
-- TRANSACTION 1;
BEGIN;
UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
COMMIT;
-- TRANSACTION 2;
BEGIN;
INSERT INTO t0 (c1, c2) VALUES (2, 2);
INSERT INTO t0 (c1, c2) VALUES (6, 12);
UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 0;
COMMIT;
-- Submit Order
/* T1 */ BEGIN;
/* T1 */ UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
/* T2 */ BEGIN;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (2, 2);
/* T1 */ COMMIT;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (6, 12);
/* T2 */ UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 0;
-- ERROR: could not serialize access due to concurrent update
/* T2 */ COMMIT;
SELECT * FROM t0;
c1 | c2
----+----
5 | 5
Although transaction 1 has been committed, the UPDATE in transaction 2
still conflicts with transaction 1 and reports an error "could not serialize
access due to concurrent update". Why was transaction 2 affected by the
UPDATE in transaction 1 at this time?
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, cuiziyu20@otcaix.iscas.ac.cn
Subject: Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction
In-Reply-To: <19081-3745d3e571c420de@postgresql.org>
* 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