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?