My DBA came to me just before integration testing and said that I should put "with UR" to the end of my queries. Almost all of the queries I am doing are done using Criteria object. I am using the DB2 Dialect.

Where would I have to go to extend Hibernate to put "with UR" at the end of the SELECT queries

I am looking an answer for the same question, if u get an answer, please let me know. Thanks.

I have a number of things to say here:

(1) this recommendation seems to be usually made by DBAs who have a lot of experience with batch processing and don't think about the fact that online applications use VERY short transactions, and usually aren't running queries that lock up huge numbers of rows. Remember, in read committed isolation level, locks held by SELECTs are momentary locks only.

(2) dirty reads are very dangerous, especially if you have a second-level cache .. NEVER use "with ur" for a query that could put data in the second-level or query caches.

(3) why not just set the JDBC isolation level if you absolutely insist that you need read uncommitted isolation.

Now, I am most certainly NOT a DBA, but I have come into contact with this on a couple of customer sites and, after talking stuff through withthe DBAs, it turned out that uncommitted read was probably not appropriate for the Hibernate application.

What do you think?

Hi,

Thanks for your input. How do I set the JDBC isolation in hibernate. I looked at the Query, Session and Transaction classes' methods and none had such option. Please give some insight. Thanks.

Either set hibernate.connection.isolation or set it on your JTA datasource, if you are in an appserver.

But unfortunately this is a global (session-factory-level) setting. I guess you would want to set this at the level of a particular transaction. You would need two sessionfactory instances, probably.

I think we need a way to specify an isolation level when opening a session. Perhaps you should submit a feature request to JIRA.

Thanks. Here is the issue I'm facing... I have a unit of work that have many selects and about 15 inserts. There are about 4000 such unit of works in the whole transaction. I am committing after one unit of work. One SQL is throwing a "Non-Cursor Hold Sections Exception" at 120th iteration, don't know why. If I comment out this sql then the 4000 iterations go through fine. I am completely lost as to what's causing and the DBA said it's not on the database side. Any help would be appreciated. My dilemma has been in identifying what the problem is. Thanks.