Do READ ONLY transactions ever cause serialisation anomalies (or, is SERIALIZABLE ever useful for READ ONLY)?

Question

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.

Answer 1

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.