0

I am using PostgreSQL. I had attempted to build a system that utilized SERIALIZABLE transactions exclusively, in the expectation that it would simplify my application development. I had marked exclusively read-only transactions with READ ONLY. However, my writes frequently ran into contention with SIReadLocks acquired by read-only queries, turning writes into continuous retries. I have since made the read-only queries run with READ COMMITTED, and everything runs smoothly now.

Now I wonder - if a transaction is READ ONLY, say if I run my query at isolation level REPEATABLE READ, what serialization anomalies could have potentially happened, and are prevented by SERIALIZABLE (and its associated costs)? I struggle to think of an example of the benefit provided in this case.

3
  • I can't reproduce this with a simple test case. What messages are you getting in the db log file when the retries are happening?
    – jjanes
    Dec 9, 2023 at 16:01
  • @jjanes I should mention that some of the read-only queries, touched many tables, were not very optimised, and ran for up to ten seconds (the queries gathered the entire visible-state of the application for a given user). However, given that I have resolved to using READ COMMITTED for my read-only queries anyway, my remaining question is really more on the conceptual side, regarding what I am losing supposedly for my performance tradeoff. Even with more optimised read-only queries in a similar situation, surely you should be able to observe the SIReadLocks during the read transactions. Dec 9, 2023 at 16:19
  • For your information, during the retries I recall the messages were something along the lines of 'serialization failure'. It was a while ago. Dec 9, 2023 at 16:24

1 Answer 1

4

Now I wonder - if a transaction is READ ONLY, say if I run my query at isolation level REPEATABLE READ, what serialization anomalies could have potentially happened, and are prevented by SERIALIZABLE (and its associated costs)? I struggle to think of any example of the benefit provided in this case.

It is rare, and for a long time it was not thought to be possible. The Postgres wiki on Serializable Snapshot Isolation (SSI) does contain a section dedicated to this, with a couple of examples. The section begins with the following statement:

While a Read Only transaction cannot contribute to an anomaly which persists in the database, under Repeatable Read transaction isolation it can see a state which is not consistent with any serial (one-at-a-time) execution of transactions. A Serializable transaction implemented with SSI will never see such transient anomalies.

The examples are too long and complex to reproduce here.


You might also be interested in the related behaviour under Snapshot Isolation (not SSI) as reported in "A Read-Only Transaction Anomaly Under Snapshot Isolation" by Alan Fekete, Elizabeth O'Neil, and Patrick O'Neil.

The abstract for the paper is:

Snapshot Isolation (SI), is a multi-version concurrency control algorithm introduced in [BBGMOO95] and later implemented by Oracle. SI avoids many concurrency errors, and it never delays read-only transactions. However it does not guarantee serializability. It has been widely assumed that, under SI, read-only transactions always execute serializably provided the concurrent update transactions are serializable. The reason for this is that all SI reads return values from a single instant of time when all committed transactions have completed their writes and no writes of non-committed transactions are visible. This seems to imply that read-only transactions will not read anomalous results so long as the update transactions with which they execute do not write such results. In the current note, however, we exhibit an example contradicting these assumptions: it is possible for an SI history to be non-serializable while the sub-history containing all update transactions is serializable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.