Обсуждение: BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction

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?

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

Hi,

Thank you for your explanation. However, for the UPDATE statement “UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 5;” in
transaction2, 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