How does PostgreSQL handle parallel transactions with different transaction isolation levels?

Question

I have a typical Java Spring + Postgres environment (the project is legacy). A persistence layer of backend has a mix of declared isolation levels - some of them are the default i.e. READ COMMITED, others are REPEATABLE READ and there are some which are SERIALIZABLE.

Sometimes, the same DB tables are accessed from parallel transactions with different isolation levels.

Are there some strict rules for such transactions' interactions?

I can, more or less, understand situation, when all the transactions have the same isolation level and some of them declare explicit locks in sake of fine-grained avoiding of undesirable read phenomenas, but not the case described above.

• Vérace: I think it might be better if you expanded your question to show actual examples of what's going on - the overview is good, but I think more detail is required.

Answer 1

It is no problem to mix different isolation levels, and each transaction will behave according to specifications. For example, in a REPEATABLE READ transaction, you will see a stable snapshot of the database, no matter which isolation levels other transactions have.

The big exception here is SERIALIZABLE. If you want to guarantee serializability, all involved transactions must be serializable, so that they take the required predicate locks. If you mix isolation levels, it is no longer guaranteed that there is an equivalent serial execution.

If you think about it, it makes sense: serializability is a constraint on the whole workload, not just on a single transaction.

• Nikita Glukhov: Correct me please if I got you wrong: we either use only SERIALIZABLE transactions or don't use SERIALIZABLE at all, because mixing with parallel transactions of lower isolation levels ruins the whole concept of serializability
• Laurenz Albe: That's exactly it.
• cstork: This should really be part of the official documentation!