Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 705
Tomasz Zieliński Avatar asked May 22 '14 11:05

Tomasz Zieliński


People also ask

Can different transactions have different isolation levels?

Transactions specify an isolation level that defines how one transaction is isolated from other transactions. Isolation is the separation of resource or data modifications made by different transactions. Isolation levels are described for which concurrency side effects are allowed, such as dirty reads or phantom reads.

How do I change the isolation level in PostgreSQL?

To set the default transaction isolation level (as opposed to individual transaction), use SET SESSION CHARACTERISTICS and specify either READ COMMITTED or SERIALIZABLE. Issuing a SET TRANSACTION command from within a transaction can override this default setting.

What is isolation levels in PostgreSQL?

There are four isolation levels defined by the standard: read uncommitted, read committed, repeatable read, and serializable. PostgreSQL doesn't implement read uncommitted, which allows dirty reads, and instead defaults to read committed.

Does Postgres support snapshot isolation?

Snapshot isolation has been adopted by several major database management systems, such as InterBase, Firebird, Oracle, MySQL, PostgreSQL, SQL Anywhere, MongoDB and Microsoft SQL Server (2005 and later).


1 Answers

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.

like image 78
arunpandianp Avatar answered Nov 15 '22 06:11

arunpandianp