Re: Serializable Isolation Level

On Wed, May 24, 2017 at 5:37 PM, Fran G <poupou1980(at)gmail(dot)com> wrote:

> Hi all,
>
> 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.

David J.
​