mybatis, sqlserver, java -- does the isolation level apply across sessions?

Question

Let's say I have a method that is annotated with isolation.SERIALIZABLE; and this piece of code is invoked on different nodes on my cluster. Does the isolation level apply only to a single session? Or does it actually effect all the clients? My guess is that it runs across sessions (nodes) but wanted to get some feedback.

addition to the problem statement (as context): Let's say the method is annotated with isolation.SERIALIZABLE and does the following:

Read from table A
Based on the data read do something then update table B

Usually I do these things in a stored-proc but this is happening in java-code. My worry is that the db connection on machine M1 won't be aware of db connection acquired by machine M2 causing concurrency issues. I'm not sure if our connection pool is across machines - even if it were M1 and M2 could use different connections, no?

score 0 · Answer 1 · answered Jan 20 '14 at 18:47

0

Isolation level is associated with database connections.

Sessions are associated with the calling app.

So if your calling app shares connections from a pool, and isolation is set on the connections in the pool, you'll be fine as long as the level is set properly.

SERIALIZABLE is the safest but blocks the most.

answered Jan 20 '14 at 18:47

duffymo

305,152
44
369
561

but if the isolation level is annotated on the method...meaning one method is serializable where as another method is something else...then how does this happen at the connection level? – hba Jan 20 '14 at 19:21

score 0 · Answer 2 · answered Jan 23 '14 at 12:37

Isolation level is property of the transaction. So it affects only the transaction it is set for.

It doesn't matter what other transactions are doing or which isolation levels they use. The semantics of SERIALIZABLE isolation level provide some guaranties for data visibility regardless of other transactions. There is some impact of other transactions but it is non-functional that is depending on implementation SERIALIZABLE transaction may have performance penalties when there are concurrent transactions.

I'll explain what happens on the connection level if you are using spring declarative transaction management. Suppose you have method:

@Transactional(isolation = SERIALIZABLE)
public void myMethod() {
}

When execution reaches this method spring will create new transaction. First of all it get new connection either from connection pool or from DataSource. Then spring sets isolation level for this transaction.

mybatis, sqlserver, java -- does the isolation level apply across sessions?

2 Answers2