CODEWITHSUNDEEP
hibernatedb2aopspring-transactionswebsphere-8
AnMi
AnMi

Reputation: 413

How to override transaction isolation in Spring with WebSphere and DB2

Best way to override transaction isolation in spring with WebSphere and DB2

Hi All,

It seems that WebSphere doesn't let Spring to programmatically override transaction isolation level which is configured in server.xml (in WebSphere datasource). So What is the best option when there is a need to override transaction isolation in such situation?

Let's say by default isolation level is set to REPITABLE_READ there is a method... huge method with many selects and couple updates at the end. Let's pretend there is no way to refactor this method anyhow, but we know that somewhere in a middle of this method there are couple selects which can be used outside RR isolation as they are huge, with many joins and while we run these selects, we are locking rows in database for other transaction. The problem is that extracting them into separate method which is annotated by @Transactional(isolation=READ_COMMITED) will not change situation as this isolation is ignored by WebSphere or database driver.

So, is it okay to get Connection out of Hibernate Session and change isolation level to READ_COMMITED exactly at the required place (around these two methods) and return it back to REPITABLE_READ at the method finish? I assume that Connection is used per thread and there should not be any side effects changing isolation level on connection?

Or is it better to add a dedicated WebSphere datasource with purposely configured isolation level to READ_COMMITED and create an aspect with annotation which would allow to substitute datasource for those two methods?

Also, would be really thankful if someone could explain how exactly isolation overriding mechanism works at @Transaction level and how many isolation levels is possible in Spring + Database interaction? Or maybe someone have a good link with this answer.

Thanks to All!

Upvotes: 0

Views: 785

Answers (1)

AnMi
AnMi

Reputation: 413

It seems there is no way to change isolation level which is set on DataSource in server.xml by means of @Transactional annotation. Though it is possible to intercept DAO method invocation by aspect and set isolation level in directly on a connection through the setTransactionIsolation(), once the transaction is started there is no way to return isolation back as trying to set the isolation level on a shareable connection that runs in a global transaction is not allowed...

So possible solutions - add separate datasource with read committed isolation and use it wherever it is required. Other solution is - In DB2 it is possible to specify the isolation level clause directly in a SELECT statement: WITH UR, CS, RS, RR

Ex: SELECT name FROM cities WITH CS;

Upvotes: 0

Related Questions