Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Change of Isolation Level in between two transactions.

 
Vishal Saxena
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I understand that the isolation level of a connection can not be changed when a transaction is in progress, but can it be changed between two transactions?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In SQL you can set the transaction isolation level explicitly before running a piece of SQL.

Not a good thing to do for the vast majority of normal database application operations. You'd need a fairly compelling special case before its worth considering. And if you are not care ful you'll end up with weird bugs coming from dirty reads, phantom reads etc.
 
Vishal Saxena
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank You for the reply.

What I am asking is this - Can it be closed between transactions by the setTransactionIsolation()? Will it always throw an exception, is it a bad practice or is it okay to do that?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What it will do is going to be DB specific. Remember that the four transaction isolation levels are not supported by all RDBMS implementations, so doing this you will be restricting which DBs you can use your code with in a less than obvious way. Remember also that calling setTransactionIsolation() can only ever be an attempt to change the isolation level. What it does is again implementation specific.

The big question you have to ask is why you think you need to do this. Changing isolation levels up so to speak (i.e from READ_COMMITTED to SERIALIZABLE) should be safe enough, but changing them down will probably result in buggy behaviour - since you start to allow dirty reads etc. Perhaps you could post what you are trying to achieve by doing this and maybe someone can suggest a better alternative?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic