2

Let's assume transaction A runs in SERIALIZABLE and transaction B runs in a more permissive isolation level, like READ UNCOMMITTED. Can Transaction A now suffer from any anomaly introduced by transaction B?

I found this SO question, but the answer doesn't give that information. I understand the referred manual page only for homogeneous scenarios:

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

I found these references in the SQL draft indicating that SERIALIZABLE transactions will indeed not suffer from any anomaly induced by other isolation level transactions:

CD 9075-1:200x(E) page 27:

The highest isolation level SERIALIZABLE, guarantees serializable execution, meaning that the effect of SQL-transactions that overlap in time is the same as the effect they would have had, had they not overlapped in time.

CD 9075-2:200x(E) page 124:

Changes made to SQL-data or schemas by an SQL-transaction in an SQL-session may be perceived by that SQL-transaction in that same SQL-session, and by other SQL-transactions, or by that same SQL-transaction in other SQL-sessions, at isolation level READ UNCOMMITTED, but cannot be perceived by other SQL- transactions at isolation level READ COMMITTED, REPEATABLE READ, or SERIALIZABLE until the former SQL-transaction terminates with a .

That's the standard. But how do the implementations do in reality? I'm particularly interested in the defined behaviour of Postgres and MySQL.

  • Cannot prove it currently, but in PostgreSQL A should return a serialization error if the serializable behaviour would be breached. Please note that PostgreSQL does not have a real READ UNCOMMITTED mode, fortunately. – dezso Sep 1 '16 at 21:29
  • Reading the postgres manual more carefully I noticed they use "Serializable" for the isolation level, and "serializable" for the schedule. Not the best way to document that, but it would confirm your assumption and align with the standard. Can you also agree to my conclusion? – Markus Malkusch Sep 2 '16 at 9:14
  • Do you have a particular situation where this issue caused trouble? – Rick James Sep 3 '16 at 0:46
  • There is no issue. I just want to do my research beforehand to avoid surprises. – Markus Malkusch Sep 3 '16 at 6:15
2

PostgreSQL wiki https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation states:

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.

In your example since transactions A and B are not both SERIALIZABLE, anomalies can happen.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.