-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Pessimistic DBs (support of FOR READ ONLY and WITH NO LOCK)
PostPosted: Fri Jan 28, 2005 4:36 pm 
Newbie

Joined: Fri Jan 28, 2005 3:26 pm
Posts: 1
Short version: How do we utilize FOR READ ONLY and WITH NO LOCK in DB2/U and MSSQL?

Some general background:
We're currently using Hib 2.1.6 on DB2/UDB 8.1, in a web application using the HTTPFilter based thread local Hibernate session pattern.

We've found that without Hibernate appending "FOR READ ONLY" to select queries, we can only support around half a dozen concurrent users with 60-120 second (not milli) web page response times - not to mention lock timeouts. However, we rigged up a JDBC connection wrapper that appends "FOR READ ONLY" to all select statements that aren't marked "FOR UPDATE". With that hack in place, the application supports several hundred concurrent users with page response times in 250-500ms range. Granted, these timings cover a lot more than just hibernate/JDBC usage, I only include them to demonstrate the relative speed difference with and without the FOR READ ONLY hack.

I've found the following in IBM's DB2 online docs: "For example, in programs that contain dynamic SQL statements without the FOR READ ONLY or ORDER BY clause, the database manager might open cursors as if the FOR UPDATE clause were specified. It is recommended, therefore, that the FOR READ ONLY clause be used to improve performance, except in cases where queries will be used | in positioned UPDATE or DELETE statements."
(extracted from http://publib.boulder.ibm.com/infocente ... 000879.htm)

Looking at the 2.1.7 source for the dialects and QueryTranslator.applyLocks(String, Map, Dialect) I see direct support for FOR UPDATE, but haven't found any logical place to even hack in support for FOR READ ONLY. Nor do I see anything in the HEAD code for Hib3 to indicate support is on the way there either.

Given all that, and that searching around the forums and JIRA seems to indicate a fair number of DB2 and MSSQL users, I'm wondering - has everyone had to rig up a way to push in FOR READ/FETCH ONLY or WITH NO LOCK, or are we missing something obvious in Hibernate to support pessimistic DB's?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

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.