8

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.

Tomasz Zieliński
  • 16,136
  • 7
  • 59
  • 83
  • No, that should not be a problem (but I have no hard reference for that) –  May 22 '14 at 11:10
  • @a_horse_with_no_name Same here - I suspect that's what databases are for but I couldn't find any concrete reference :-) – Tomasz Zieliński May 22 '14 at 11:11
  • You probably get better answers if you post that to the Postgres mailing list. Most of the major developers "hang out" there. If someone can answer that, that's them. –  May 22 '14 at 11:12
  • @a_horse_with_no_name OK, I see, I'll wait a bit here and if no (definitive) answer appears I'll try there. – Tomasz Zieliński May 22 '14 at 11:14
  • I came here to find an answer if a serializable transaction might suffer from anomalies introduced by other level transactions. As I find your question more broader and the answer doesn't provide that information, I asked that particular question in [DBA](http://dba.stackexchange.com/questions/148598/is-an-anomaly-possible-if-a-serializable-transaction-interfers-with-another-isol). – Markus Malkusch Sep 01 '16 at 21:58

2 Answers2

2

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.

arunpandianp
  • 106
  • 1
  • 8
1

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.

pozs
  • 34,608
  • 5
  • 57
  • 63
  • 2
    I cannot see how your conclusion relates to your quotation. I discussed this question in [DBA](http://dba.stackexchange.com/questions/148598) and found that the important detail in the manual is the distinction between **S**erializable referring to the isolation level and **s**erializable referring to the schedule. Understanding that, leads then to the conclusion that `Serializable` transactions are safe as defined in the ANSI standard explicitly. – Markus Malkusch Sep 02 '16 at 09:29
  • 3
    This sounds dangerous, not safe. It means your serializable transaction can make wrong assumptions if concurrent non-serializable ones interfere. Say you begin a serializable xact A and do some "INSERT... WHERE NOT EXISTS", then a non-serializable xact B inserts a row matching that WHERE clause and commits, A will still commit, and now maybe your application worker using xact A did something wrong. – sudo Oct 06 '19 at 00:43