Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: MS SQL Server nolock in the generated queries?
PostPosted: Fri Sep 03, 2004 2:27 am 
Newbie

Joined: Fri Sep 03, 2004 2:20 am
Posts: 7
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 03, 2004 2:42 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
It must be possible to implement it in JDBC wrapper, fork code from some pool to hack it.


Top
 Profile  
 
 Post subject: Cleaner way?
PostPosted: Fri Sep 03, 2004 3:03 am 
Newbie

Joined: Fri Sep 03, 2004 2:20 am
Posts: 7
baliukas wrote:
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...


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 03, 2004 3:46 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
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.


Top
 Profile  
 
 Post subject: I agree
PostPosted: Sun Sep 05, 2004 9:13 pm 
Newbie

Joined: Fri Sep 03, 2004 2:20 am
Posts: 7
gavin wrote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 06, 2004 12:20 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
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?)


Top
 Profile  
 
 Post subject: Deadlock Issue in Application
PostPosted: Fri Sep 10, 2004 9:50 am 
Newbie

Joined: Fri Sep 10, 2004 9:46 am
Posts: 10
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


Top
 Profile  
 
 Post subject: Same issue
PostPosted: Wed Nov 24, 2004 4:52 pm 
Beginner
Beginner

Joined: Tue Nov 11, 2003 5:15 pm
Posts: 23
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 02, 2004 12:46 am 
Newbie

Joined: Fri Sep 03, 2004 2:20 am
Posts: 7
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:

Code:
session.connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);


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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.