In my application, I use PostgreSQL transactions with a SERIALIZABLE isolation level and nested save points (I call them sub-transactions). Sometimes transactions or sub-transactions do not go through due to serialization errors and then I try to repeat the actions that were performed inside them.

The problem is that when I try to repeat the actions from the sub-transaction (roll back to the last savepoint, create a new savepoint and execute the actions again), postgresql does not see the changes made in other threads, and therefore all subsequent attempts fall on the same serialization error .

For clarity, I will give a simple example (somewhat curtailed compared to real actions, but sufficient to reproduce the essence of the problem). In the first thread:

create table if not exists test(id int); truncate test; begin; set isolation level serializable; savepoint p1; select id from test where id = 1; # тут могут быть любые запросы, приводящие к ошибке сериализации rollback to p1; 

In the second thread:

 insert into test(id) values (1); 

In the first thread:

 savepoint p2; select id from test where id = 1; 

The last SELECT does not see the line inserted in another thread, and returns "0 lines". As I understand it, after the first SELECT has been executed, postgresql ignores any external changes that may change its results. But I do not want to ignore them. I want to forget about everything that happened inside the canceled sub-transaction and live on. And I do not want to overfulfill the entire transaction due to an error in a small sub-transaction.

Can you please tell me if there is a way to continue the execution of the transaction with the changes made in other threads?

  • As you can see, in the second thread, Insert is executed before select from savepoint 2 of the first thread? At the 'SERIALIZABLE' level, transactions seem to be performed in turn. Until the first one ends, the second one will not start. - Sergey
  • Postgres still has one feature. If there was an error in the transaction, then commit it is no longer possible. You can only roll back to the last successful point. And then start a new transaction. - Sergey
  • @Sergey Apparently, in what order? Opened two consoles with postgresql and performed the actions in the order of interest to me. And in tests I opened two independent connections to the database. - Yury Sitnikov
  • Regarding the second message - I am doing so. I roll back to the last savepoint. - Yury Sitnikov
  • In any case, SERIALIZABLE should not, in any case, see the changes made by other transactions. Result select id from test where id = 1; in savepoint p2 should not differ from the result in savepoint p1 - Sergey

0