Re: Confusions regards serializable transaction

On Fri, 2020-09-04 at 10:20 +1000, Hannah Huang wrote:
> I’m confused about what will trigger the serializable error. My test environment is PG v12.
>
> This is how you can produce the test table:
> create table rollover (id int primary key, n int not null);
> insert into rollover values (1,100), (2,10);
> I then
> 1. start serializable transaction A
> 2. do an update
> 3. start serializable transaction B
> 4. Do an update
> 5. Commit update of transaction B
> 6. Commit update of transaction A.
>
> I would be expecting an error throw out from transaction A commit as the data has been changed in transaction B, however, both transactions are executed successfully.
>
> Session A:
>
> [20:14:59] postgres(at)pgb : 285650 =# begin isolation level serializable;
> BEGIN
> [20:15:01] postgres(at)pgb : 285650 =# update rollover
> set n = n + (select n from rollover where id = 2)
> where id = 1;
> UPDATE 1
> [20:15:06] postgres(at)pgb : 285650 =#
> [20:15:23] postgres(at)pgb : 285650 =#
> [20:15:23] postgres(at)pgb : 285650 =# commit;
> COMMIT
>
>
>
> Session B:
>
> [20:14:57] postgres(at)pgb : 286411 =# begin isolation level serializable;
> BEGIN
> [20:15:14] postgres(at)pgb : 286411 =# update rollover set n = n + 1 where id = 2;
> UPDATE 1
> [20:15:17] postgres(at)pgb : 286411 =# commit;
> COMMIT
>
> I then did a bit of change in the SQL statement executed in both transactions by add a select * from rollover table,the commit of transaction A failed as expected. I don’t know WHY…

Even though session A committed after session B, it happens "logically" before B.

You could first execute session A, then session B so that they don't overlap,
and the result would be the same.

That means that there is an equivalent serial execution, so the two transactions
are serializable.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com