3

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.

1
  • 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.
    – Vérace
    Commented Jun 4, 2021 at 6:14

1 Answer 1

3

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.

3
  • 1
    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 Commented Jun 4, 2021 at 18:04
  • 1
    That's exactly it. Commented Jun 5, 2021 at 1:03
  • This should really be part of the official documentation!
    – cstork
    Commented Jun 6, 2023 at 14:22

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.