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?
