Thread

1. 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-09T15:44:15Z

   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?
   
   

2. 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-09T17:17:52Z

   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
   
   
   
   

3. Re: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction

   崔紫玉 <cuiziyu20@otcaix.iscas.ac.cn> — 2025-10-16T07:30:10Z

   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