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 before that transaction started. [1] states that, "The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.", and [2] states for REPEATABLE READ: "All statements of the current transaction can only see rows committed 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 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: "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 concurrent transactions of the same level. For example, even a read only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch 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 "before the first query or data-modification statement was executed", as explained in [2]), I do not understand how this can lead to an inconsistent view. Of course, two other reading+writing transactions could create an inconsistent result, but such 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 READ ONLY transactions. I therefore conclude that the two levels REPEATABLE READ and SERIALIZABLE act different -- also in the READ ONLY case. However, [1] states that REPEATABLE READ is implemented as "Snapshots Isolation" as defined in [berenson95] ("A Critique of 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 scenario where 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-modification statement was executed in this transaction." Thus, what does SERIALIZABLE READ ONLY achieve that REPEATABLE READ READ ONLY does not? And what is SERIALIZABLE READ ONLY DEFERRABLE for? Any hints to make me better understand this issue are appreciated. Kind regards, Jan Behrens
