1

I have this table

create table "tasks" (id SERIAL PRIMARY KEY, user_id int REFERNCES "user"(id), title TEXT);

I also created index on "tasks"(user_id)

Then I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order

Transaction 1

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

Transaction 2

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 Isolation level serializable not working as expected?. But I have created an index on my column.

3
  • Even if you have an index, it still doesn't guarantee against false positive failures.
    – jjanes
    Apr 30, 2022 at 14:45
  • But why does the primary key still work? Apr 30, 2022 at 18:51
  • Please share EXPLAIN output for the SELECT statement. May 2, 2022 at 6:25

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.