Re: Transaction isolation level Repeatable Read Read Only vs Serializable Read Only

On Thu, 26 Nov 2020 19:13:53 -0500
Mohamed Wael Khobalatte <mkhobalatte(at)grubhub(dot)com> wrote:

> >
> > Thus, what does SERIALIZABLE READ ONLY achieve that REPEATABLE READ READ
> > ONLY does not? And what is SERIALIZABLE READ ONLY DEFERRABLE for?
> >
>
> There is a nice walkthrough of the "control" and "batches/details" scenario
> mentioned in the docs, you can find it in the wiki,
> <https://wiki.postgresql.org/wiki/SSI#Deposit_Report> and I think it
> explains the difference well.

Thank you, it helped me a lot.

If there are two REPEATABLE READ READ WRITE transactions, which do *not* require SERIALIZABLE isolation, there could still be a third READ ONLY transaction, which has stricter requirements on isolation when retrieving data. SERIALIZABLE READ ONLY will fail under certain circumstances in which case the retrieved information should be deemed void and re-requested in a new transaction in order to fit the extra requirements on isolation of the read-only transaction.

Then "DEFERRABLE" makes sense too, as it will make the third transaction block for a certain time instead of risking cancellation.

Thanks
Jan