PostgreSQL – Rollback to Savepoint with Repeatable Read Isolation

postgresql

I want to use savepoints to do subtransactions that I can rollback and retry within a transaction.

I have the following scenario when using repeatable read isolation on PostgreSQL 11.

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Another transaction updates this record.
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails (as expected) with a serialization error.

-- Rollback and try again.
ROLLBACK TO SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails unexpectedly with a serialization error.

My understanding is that rolling back to a savepoint restores the transaction to the state it was in when the savepoint was created.

I expected that I could ROLLBACK TO SAVEPOINT x and retry the SELECT/application logic/UPDATE cycle. However the SELECT still returns the old data and I still get a serialization error even though the transaction had not seen the relevant record before the savepoint.

Of course, one could also argue that as we are still in the same transaction and we are using repeatable read isolation, the database should return the old data and thus fail the UPDATE with a serialization error.

I would like to know if this a feature or a bug? Is this behavior per the standard or is it PostgreSQL specific?

Best Answer

In PostgreSQL, a REPEATABLE READ transaction uses a fixed snapshot of the database.

So if a concurrent data modification caused a serialization error, then restoring to a savepoint and repeating the actions will always result in the same serialization error. You will still see the same state of the database, and if you update a row that has been modified after the snapshot was taken, that will still be the case.

You actually have to roll back and retry the whole transaction.

This is working as designed.

The SQL standard doesn't describe when exactly serialization errors should be thrown, because it does not deal with the implementation specifics of individual RDBMS. It says:

The execution of a <rollback statement> may be initiated implicitly by an SQL-implementation when it detects the inability to guarantee the serializability of two or more concurrent SQL-transactions. When this error occurs, an exception condition is raised: transaction rollback – serialization failure.

So a database is at liberty to throw a serialization error whenever it thinks fit.

Related Question