CODEWITHSUNDEEP
postgresqltransactionsisolation-level
Tomasz Zieliński
Tomasz Zieliński

Reputation: 16356

Mixing isolation levels in PostgreSQL

Does it matter for a SERIALIZABLE transaction if any other session uses e.g. autocommit or the READ COMMITED isolation level?

In other words is there any danger in mixing isolation levels (& autocommit) when accessing a database from multiple processes/threads (or anything else to watch out for)?

Note that I'm aware of the "ordinary" issues, like SERIALIZABLE transactions asking for a retry etc. I'm asking for anything non-obvious that can happen when one is mixing different isolation levels.

EDIT:

From http://www.postgresql.org/docs/9.4/static/transaction-iso.html:

Consistent use of Serializable transactions can simplify development. The guarantee that any set of concurrent serializable transactions will have the same effect as if they were run one at a time means that if you can demonstrate that a single transaction, as written, will do the right thing when run by itself, you can have confidence that it will do the right thing in any mix of serializable transactions, even without any information about what those other transactions might do.

That could indicate that mixing isolation levels is not a good idea. On the other hand it merely says that consistent use of the SERIALIZABLE level is good, and not that mixing isolation levels is bad.

Upvotes: 11

Views: 2363

Answers (2)

arunpandianp
arunpandianp

Reputation: 136

Postgres wiki https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation states this

Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

So, SERIALIZABLE guarantees won't hold when mixing isolation levels.

Upvotes: 5

pozs
pozs

Reputation: 36244

SERIALIZABLE

All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error.

That means, a SERIALIZABLE transaction only act differently, when running against another SERIALIZABLE transaction(s). If they run against non-SERIALIZABLE transactions, they should act as they were REPEATABLE READ transactions. That suggests, it's completely safe to mix these transaction isolation levels.

Upvotes: 0

Related Questions