Serializable isolation fails even for unrelated rows
I have this table
create table "tasks" (id SERIAL PRIMARY KEY, user_id int REFERNCES "user"(id), title TEXT);
I also created index on
Then I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order
begin; --T0 set transaction isolation level serializable; --T2 select * from "tasks" where user_id=1; --T4 insert into "tasks" (user_id, title, content, created_time) VALUES (1, 'abc'); --T6 end; --T8
begin; --T1 set transaction isolation level serializable; --T3 select * from "tasks" where user_id=2; --T5 insert into "tasks" (user_id, title, content, created_time) VALUES (2, 'abc'); --T7 end; --T9
Transactions 1 succeeds but transaction 2 fails. But if i change the
where user_id=?of the select statements to
where id=?, it works.
So does SSI allow only unrelated primary key changes and still fails with unrelated indexed columns?
The situation is similar to this question https://dba.stackexchange.com/questions/242035/isolation-level-serializable-not-working-as-expected . But I have created an index on my column.
Ok, turns out I needed to create more data for my test table, otherwise postgres will opt to use sequential scan instead of index scan and thus causes conflict.