Sometimes, it's necessary to decrease the isolation level of certain queries to that of "uncommited read". It's common in highly concurrent enterprise systems like ERP, CRM, and other OLTP systems.
In these cases, the decision took in account the balance between transaction isolation protection and system's overall performance.

I was wondering why Hibernate hasn't provided an option to change isolation level on individual queries?

I know some architects and designers, who decided to go through the "individual uncommited read" approach, were forced to use native SQL instead HQL!
Once using this approach is somewhat dangerous, it's not recommended to setup "uncommitted read" by the whole session or transaction. The better option would be alter the default isolation level in those queries pointed by the software architect (a mix of business and technology specialist).
It's important to remember the feature that allows change isolation level used by individual tables on individual queries is present in several important commercial DBMS, including IBM DB2 (with UR sintax) and Microsoft SQLServer (with(nolock) sintax).

My suggestion is to make the architect's life easier by implementing in Hibernate features not so common but very much important from the sql generation point of view. We should avoid forcing people to use native SQL whenever HQL is found restrictive...

This is not a decision hibernate can make. I give you an example. A JBoss managed connection pool gives you the same connection for a thread and multiple database requests, whether not through hibernate, will go through the same connection. A commit is only issued at the end of a thread's life time (not exactly like this but you got the idea). At this point it is quite obvious it does not make much sense to change isolation level multiple times in scope of one transaction (I might be wrong here). I have used multiple data-sources with different isolation levels for different kind of works. That adds to complexity of your system but hey we still don't know any turn key solution for performance.


Farzad-

Dear peers, I'll give you an example from a typical ERP system:
A plenty of users are concurrently using the system for selling goods from a huge catalog.
The system has to present the quantity available of each product ordered by a given customer. If an ordered product isn't available the seller suggests an alternative one.
In the scenario presented above, the product quantities are being continuously queried and updated by the system. These queries could be optimized if the concurrent locks (caused by simultaneous updates/transactions on the same record set) were bypassed (low isolation level). In another moment, after the user confirm a purchase, the product availability will be re-checked with concurrent locks being respected (normal isolation level) in order to preserve the stock/supply integrity.
I presented here an actual application of uncommited read isolation level. Its use is restricted and highly controlled in order to avoid business problems with presenting dirty data to the user. That's the reason why we need to change isolation level to individual HQL queries. I never find useful to define such low isolation level as a default for a whole connection/transaction/session/thread...

I do understand it and I do agree with you. All I am saying here is that your best solution might be to use two different session factories. One is hooked up with a data source that give read uncommitted connection and the other one give a connection in a lets say read committed isolation level. For your catalog browsing you need to use the session factory that has a less isolation level. However, I am also interested to see if there is a better way to get around this. In any events, it looks totally unsafe to change a connections isolation level for a session that checks out connections from a data source. You may run into finicky problems that would take months to solve.



Farzad-