Hi there, I've tried searching but haven't found any posts that relates to the problem I'm having at the moment.

I was wondering if we can make hibernate generate "nolock" strings in the queries? We've written our application and it works great at the moment but the DBA insists we put the nolock hints into our queries. There doesn't seem to be an easy way to do this.

Can anyone suggest something?

thanks,

dave

It must be possible to implement it in JDBC wrapper, fork code from some pool to hack it.

That's definitely one way forward. I'll keep that in mind. Thanks... :)

Hmmm, however, is there another way?

It's a pity that there's no cleaner hook into the hibernate query engine...

Well, the trouble is that "nolock" (ie. dirty reads) is actually quite dangerous, especially if you use a second-level cache, or optimistic locking. It's a really bad idea.

I've *considered* adding a new LockMode, but whenever we've seen this requirement in practice, we've told people to go back and ask their DBA *again*, explaining the fact that this is an OLTP application, which

(1) does not access many rows in a single transaction,
(2) is running in read committed, where the lock is just a momentary lock and
(3) uses extremely short transactions anyway,

and it seems that the requirement is not correct after all.

My understanding is that you usually get this kind of thing from DBAs who are thinking in terms of batch jobs and the issues that affect offline processing, not OLTP. However, I am not myself a DBA, and would be curious to hear a compelling justification for this decision.

Gavin, I totally agree with you.

In our application, we have to do a daily deployment (essentially a bulk loader) which can be invoked outside of the application. I'm no DBA either, but apart from caching, this could pose a problem for the application's availability if during the load, our queries lock up.

Personally, I don't think the collisions are that bad. I think our DBA is coming from a historic angle - previously a deployment could take 20 mins. Our new loader much more streamlined (also built on Hibernate - thanks!) does it in a couple of minutes.

I'm recommending that we ignore this issue until it really becomes an issue.

However, I can see other systems that could rely on bulk loads over long transactions. Isn't that going to pose a problem?

I'm not sure how easy it is to do it but I would think Hibernate can benefit a lot if there are hooks into the query engine. This way developers can extend the language as needed.

For example, what about:

1) calling functions and stored procedures?
2) non supported aggregate functions?
3) keywords like Oracle's decode?

To my limited knowledge, HQLs are translated to SQLs and then object instances are inflated. Hooks into the HQL that allows custom population of the object instances would make Hibernate more extensible/flexible.

I can also see some systems having a 95% fit for Hibernate, but then there's that 5% high performance stuff that needs stored procedure/function calls or batched updates/deletes but I don't want to circumvent the nice stuff I've done to model the application.

Maybe Hibernate can already do this... I'll be happy to hear it if it can and how/where other people are using it.

Well, a few points:

(1) you are not going to run into problems unless there is another simultaneous batch process doing write operations (read committed means only momentary locks for read operations!).

(2) For the batch process, why not just set the JDBC isolation level to read uncommitted?

(3) It is quite legal to call SQL functions, including DECODE from HQL.

(4) HQL queries are not the only place that queries are generated in Hibernate, and I don't really know what an API for SQL customization would look like. Its kinda what native SQL queries are for.

(5) For the 5% cases, just don't use Hibernate in that small part of the system, or use native SQL queries or the new support for handwritten SQL that comes with Hibernate3. (You are aware of that stuff, right?)

I had similar issues in our MS SQL Server Hibernate application, which were fatal, we were getting deadlock conditions in the database, causing one of the transactions to be rolled back.

However this was not due to an external batch job, but to another thread of our application running a different query/update.

Our application uses Stateless Session EJB

We also have a same situation, we are using MS SQL Server and hibernate doesn't put "nolock" so it's causing slow performance in the application.

Can you send me the code where you did the hibernate fix?
My Email,
suresh_sr@yahoo.com

I ended up setting the isolation level to READ UNCOMMITTED - according to MS, READ UNCOMMITTED has the exact same effect as nolock on all tables.

I did a trace at the database level just to make sure that this was set and it was. If this solution is appropriate, then it is done simply via:



Put this before you start a transaction if you do start a transaction.

I have not noticed any lock on the application since. It has the added benefit of making the queries run faster since they don't have to wait for locks to be released.

Good luck.

dave