Transaction isolation level Repeatable Read Read Only vs Serializable Read Only

Поиск
Список
Период
Сортировка
От Jan Behrens
Тема Transaction isolation level Repeatable Read Read Only vs Serializable Read Only
Дата
Msg-id 20201126234509.b856ad06c6c532f843163a57@magnetkern.de
обсуждение исходный текст
Ответы Re: Transaction isolation level Repeatable Read Read Only vs Serializable Read Only  (Mohamed Wael Khobalatte <mkhobalatte@grubhub.com>)
Re: Transaction isolation level Repeatable Read Read Only vs Serializable Read Only  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Huan Ruan
Дата:
Сообщение: Re: Potential BRIN Index Corruption
Следующее
От: Mohamed Wael Khobalatte
Дата:
Сообщение: Re: Transaction isolation level Repeatable Read Read Only vs Serializable Read Only