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 "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 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2