pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction
3+ messages / 3 participants
[nested] [flat]
* BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction
@ 2025-10-09 15:44 PG Bug reporting form <noreply@postgresql.org>
2025-10-09 17:17 ` Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-10-09 15:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cuiziyu20@otcaix.iscas.ac.cn
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction
2025-10-09 15:44 BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction PG Bug reporting form <noreply@postgresql.org>
@ 2025-10-09 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-16 07:30 ` Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction 崔紫玉 <cuiziyu20@otcaix.iscas.ac.cn>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-10-09 17:17 UTC (permalink / raw)
To: cuiziyu20@otcaix.iscas.ac.cn; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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?
I don't see any particular contradiction here. At the time of T2's
second INSERT, the removal of the original (0, 0) row has already
committed, so there is no reason to disallow that INSERT.
REPEATABLE READ does not intend to promise no serialization anomalies.
SERIALIZABLE mode is a better approximation to that if you need it,
though it has its own downsides.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction
2025-10-09 15:44 BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction PG Bug reporting form <noreply@postgresql.org>
2025-10-09 17:17 ` Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-10-16 07:30 ` 崔紫玉 <cuiziyu20@otcaix.iscas.ac.cn>
0 siblings, 0 replies; 3+ messages in thread
From: 崔紫玉 @ 2025-10-16 07:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi,
Thank you for your explanation. However, for the UPDATE statement “UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 5;” in transaction 2, why does the UPDATE statement in transaction 2 in the first case fail to match the row that was changed to (5, 5) in the committed transaction 1? In the second case, why does the UPDATE statement in transaction 2 report an error “could not serialize access due to concurrent update”?
Best regards,
Ziyu Cui
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-16 07:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-09 15:44 BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction PG Bug reporting form <noreply@postgresql.org>
2025-10-09 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-16 07:30 ` 崔紫玉 <cuiziyu20@otcaix.iscas.ac.cn>
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