Hi,
I found a SERIALIZABLE anomaly where a transaction reads a row, successfully
inserts another row with the same primary key, observes both rows, and commits.
Setup:
CREATE TABLE uq (
k int PRIMARY KEY,
j int,
v int
);
INSERT INTO uq VALUES (1, 1000000, 0);
INSERT INTO uq SELECT g, g, 0 FROM generate_series(100, 2000) g;
CREATE INDEX uq_j ON uq(j);
VACUUM ANALYZE uq;
The filler rows are needed so that the planner picks index scans, and so that
j = 1000000 and j = 2 fall on different leaf pages of uq_j.
Session 1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM uq WHERE k = 1;
-- 1 | 1000000 | 0
Session 2:
BEGIN ISOLATION LEVEL SERIALIZABLE;
DELETE FROM uq WHERE j = 1000000;
COMMIT;
Back in session 1:
INSERT INTO uq VALUES (1, 2, 1);
SELECT * FROM uq WHERE k = 1 ORDER BY j;
Result:
k | j | v
---+---------+---
1 | 2 | 1
1 | 1000000 | 0
Session 1 can then commit successfully.
There is no serial order for this result. Session 1 saw the original row, but
its INSERT was also allowed to rely on the other transaction's committed
deletion.
As a control, changing session 2 to:
DELETE FROM uq WHERE k = 1;
causes a serialization failure. The outcome therefore depends on whether the
deletion uses the primary-key index or the secondary index.
The closest prior work is fcff8a57519, which added SSI conflict checking before
reporting unique violations and noted that constraint reads do not fully
participate in SSI. Here no unique violation is reported, and the missing
dependency allows both transactions to commit.
I found no current thread discussing this SnapshotDirty uniqueness-check case.
The current SxactGlobalXmin SSI patch is unrelated.
Best,
Jacob Brazeal
