Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You are aware that you're using mysql and not a RDBMS that cares? –  Phil Mar 12 at 1:58
    
@Phil:Do you mean that the answer is DB-Specific? –  Jim Mar 12 at 7:14

1 Answer 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).

share|improve this answer
    
My interest in MySQL but from what you are saying it is implementation dependent and not standardized –  Jim Mar 12 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). –  Jarosław Błąd Mar 14 at 8:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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