Обсуждение: Transaction isolation level Repeatable Read Read Only vs Serializable Read Only

Dear all,

I have some questions regarding the transaction isolation level REPEATABLE READ and it's documentation at

[1] https://www.postgresql.org/docs/13/transaction-iso.html
and
[2] https://www.postgresql.org/docs/13/sql-set-transaction.html

As far as I understood, a read-only transaction with isolation level REPEATABLE READ will see only changes committed
beforethat transaction started. [1] states that, "The Repeatable Read isolation level only sees data committed before
thetransaction began; it never sees either uncommitted data or changes committed during transaction execution by
concurrenttransactions.", and [2] states for REPEATABLE READ: "All statements of the current transaction can only see
rowscommitted before the first query or data-modification statement was executed in this transaction."
 

I understand that in a read+write scenario, two concurrent transactions may still lead to a result that could not have
occurredif those two transactions were executed one after the other. However, in a read-only case, I do not see how
REPEATABLEREAD could differ from SERIALIZABLE. Yet [1] explains that:
 

"The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the
database.However, this view will not necessarily always be consistent with some serial (one at a time) execution of
concurrenttransactions of the same level. For example, even a read only transaction at this level may see a control
recordupdated to show that a batch has been completed but not see one of the detail records which is logically part of
thebatch because it read an earlier revision of the control record."
 

If a REPEATABLE READ READ ONLY transaction only sees data commited from transactions before it began (more precisely
"beforethe first query or data-modification statement was executed", as explained in [2]), I do not understand how this
canlead to an inconsistent view. Of course, two other reading+writing transactions could create an inconsistent result,
butsuch a result would also be read by a subsequent SERIALIZABLE READ ONLY transaction. Thus, what is the difference
between"REPEATABLE READ READ ONLY" and "SERIALIZABLE READ ONLY"?
 

There should be a difference, as [2] explicitly says that the "DEFERRABLE" option is available only for SERIALZABLE
READONLY transactions. I therefore conclude that the two levels REPEATABLE READ and SERIALIZABLE act different -- also
inthe READ ONLY case.
 

However, [1] states that REPEATABLE READ is implemented as "Snapshots Isolation" as defined in [berenson95] ("A
Critiqueof ANSI SQL Isolation Levels"), see:
https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf

In that paper, Table 4 on page 11 states that Snapshot Isolation allows only the A5B (Write Skew) anomaly, which is a
scenariowhere two transactions concurrently write. The A5A case (Read Skew) is explicitly prohibited. This also matches
[2],where it says: "All statements of the current transaction can only see rows committed before the first query or
data-modificationstatement was executed in this transaction."
 

Thus, what does SERIALIZABLE READ ONLY achieve that REPEATABLE READ READ ONLY does not? And what is SERIALIZABLE READ
ONLYDEFERRABLE for?
 

Any hints to make me better understand this issue are appreciated.

Kind regards,
Jan Behrens

On Thu, 26 Nov 2020 19:13:53 -0500
Mohamed Wael Khobalatte <mkhobalatte@grubhub.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
stillbe a third READ ONLY transaction, which has stricter requirements on isolation when retrieving data. SERIALIZABLE
READONLY will fail under certain circumstances in which case the retrieved information should be deemed void and
re-requestedin 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

On Thu, 2020-11-26 at 23:45 +0100, Jan Behrens wrote:
> I understand that in a read+write scenario, two concurrent transactions may still lead to
>  a result that could not have occurred if those two transactions were executed one after the other.
>  However, in a read-only case, I do not see how REPEATABLE READ could differ from SERIALIZABLE. Yet [1] explains
that:

There is an example in the Wiki:
https://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions

In that example, serializability is broken only because of a READ ONLY transaction.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com