Confusions regards serializable transaction

From: Hannah Huang <hannah(dot)huang(dot)y(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Confusions regards serializable transaction
Date: 2020-09-04 00:20:27
Message-ID: AD1ECF70-71F3-404B-84A4-A074D7F08ED3@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

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…

Thanks a lot!
Hannah.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Peter Geoghegan 2020-09-04 04:21:57 Re: Confusions regards serializable transaction
Previous Message Tom Lane 2020-09-01 17:56:16 Re: Implications of resetting Postgres service in Windows