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@pgb : 285650 =# begin isolation level serializable;
BEGIN
[20:15:01] postgres@pgb : 285650 =# update rollover
set n = n + (select n from rollover where id = 2)
where id = 1;
UPDATE 1
[20:15:06] postgres@pgb : 285650 =#
[20:15:23] postgres@pgb : 285650 =#
[20:15:23] postgres@pgb : 285650 =# commit;
COMMIT
Session B:
[20:14:57] postgres@pgb : 286411 =# begin isolation level serializable;
BEGIN
[20:15:14] postgres@pgb : 286411 =# update rollover set n = n + 1 where id = 2;
UPDATE 1
[20:15:17] postgres@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.