1

Quick background story:

I work on a very old application that has recently been having issues with locks on the database. The app is written in Java and uses Hibernate. One of the issues we identified are transactions that are kept alive unnaturally long while also having isolation levels changed between READ_COMMITED and READ_UNCOMMITED frequently. While we acknowledge that the clear solution is refactoring the code so that transactions are smaller, this would be an enormous effort that we cannot afford entirely right now (most used parts of the app are being migrated to a new system but this procedure is relatively slow).

So - because we use READ_UNCOMMITED for all our Select operations and READ_COMMITED for everything else, a DBA that has been helping us, identified a possible solution in changing the isolation level to a global READ_COMMITED and changing all select queries to include the hint 'with (NOLOCK)'. He says functionally there should be no difference in the way data is retrieved (since we use dirty reads right now with no problem) while providing us with an advantage in not having to frequently change isolation level within the transaction. I believe his idea also comes in regards to recent reports we've been having about database locks being caused by isolation level changes.

So - Can we (and if so, how?) tell hibernate to add a 'with (nolock)' hint on all queries being automatically generated by the usage of mapped java objects and HQL (and maybe even existing SQL being passed to hibernate, though this seems like pushing it :) ) WITHOUT changing the isolation level?

Final side notes: we are using an older version of hibernate, v3.5 and right now an upgrade is unlikely, some incredibly 'smart' people decided to taint it at some point, inserting some of their own code that the application uses. Upgrading has been tried and failed multiple times.

Also: i have checked quite a few related threads, the general idea seems to be: don't use nolock, change isolation level, which - as stated - we're not looking to do.

Edit1: Since the app has been continuously developed in the past 12 years, there are loads of modules that haven't been even once glanced over by the current dev team, the ideal solution would be something that doesn't require the identification of every single bit of Java code that uses persisted objects.

Edit2: A possible way to go about this - should Hibernate allow it - would be to add a form of Interceptor that receives the formatted SQL query before being passed to the db driver. I would then take care of adding the hints myself, using some form of regex.

Thank you very much in advance.

Apostu Alexandru
  • 13
  • 1
  • 5

1 Answers1

0

You cannot use (NOLOCK) with HQL. You can however with native SQL if you decide to change your queries. Something like:

getCurrentSession().createSQLQuery("select * from table with(NOLOCK)").list();
Narain Mittal
  • 1,150
  • 11
  • 25