1

I read that I can have different isolation levels per connection and in the server.
The default isolation level is REPEATABLE-READ.
So if I have a transaction that issues a SERIALIZED to the client connection, how does it interact with other transactions from other client connections? Do they all end up being serialized? From set transaction seems not:

With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session.

But this seems useless to me. I mean if I set serialized to a client connection then I would want all currently running transactions to be sequential, right? Otherwise what's the point?

2
  • You are aware that you're using mysql and not a RDBMS that cares?
    – Philᵀᴹ
    Mar 12, 2014 at 1:58
  • @Phil:Do you mean that the answer is DB-Specific?
    – Jim
    Mar 12, 2014 at 7:14

1 Answer 1

1

Every connection will work with its own transaction isolation level. Setting SERIALIZABLE level for one connection doesn't change isolation level of other connections (new or existing).

However interactions between transactions performed on different isolation level are quite complicated. Everything depends on many factors:

  • If different transactions operate on the same table/tables?
  • How your database engine implements transaction isolation levels (pessimistics versus optimistics locking)?
  • What is characteristics of access from your app to particular tables?

If you have to work with SERIALIZABLE level for some connections in your app you should know answers for above questions.

For example. If you have database with pessimistics locking (e.g. IBM DB2) and you have long running transactions with SERIALIZABLE level it is very probable that your system would hang (or slow down).

2
  • My interest in MySQL but from what you are saying it is implementation dependent and not standardized
    – Jim
    Mar 12, 2014 at 21:38
  • Yes, implementation details of isolation level aren't covered by SQL standard. However the standard say how exactly particular isolation level must work from application perspective - in terms of transactions behavior of course. One important thing is that SERIALIZABLE doesn't mean serial/sequential executing of transaction. SERIALIZABLE means that phantom reads can't occur and other anomalies covered by previous isolation levels (REPEATABLE READ, READ COMMITED). Mar 14, 2014 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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