I am getting an exception in my code which I did not anticipate and could not find sufficient documentation to clear this issue. I have a rather simple table (Table_A) with two columns (Text id, Integer number). If I do the following set of commands on two separate psql consoles, I get a read/write exception.
T1: begin transaction isolation level serializable;
T2: begin transaction isolation level serializable;
T1: update Table_A set number = 1 where id = 'ID1';
T2: update Table_A set number = 1 where id = 'ID2';
T1: commit;
T2: commit;
T2 throws a 40001 exception. Note that both transactions operate on distinct rows in my table. Then why am I getting an exception? I would greatly appreciate any pointers.
Best guess - you didn't define a PK on the id column so both updates sequentially scan the table and read the record of the other transaction. Then when you go to commit T2 the read record is seen to have changed by T1 and bam!. Add a PK and you should be good.
