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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply