[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3031) Include "FOR READ ONLY" hint in non-locking DB2 statements

Include "FOR READ ONLY" hint in non-locking DB2 statements ---------------------------------------------------------- Key: HHH-3031 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3031 Project: Hibernate Core Issue Type: Improvement Components: core Affects Versions: 3.2.5 Environment: 3.2.5, DB2/UDB 8.1+ Reporter: Steve Finch Attachments: HHH-3031.patch Original Estimate: 16h Remaining Estimate: 16h As per the DB2 documentation: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.i... The FOR READ ONLY clause indicates that the result table is read-only and therefore the cursor cannot be referred to in Positioned UPDATE and DELETE statements. FOR FETCH ONLY has the same meaning. For result tables in which updates and deletes are allowed, specifying FOR READ ONLY (or FOR FETCH ONLY) can possibly improve the performance of FETCH operations by allowing the database manager to do blocking. 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. A read-only result table must not be referred to in a Positioned UPDATE or DELETE statement, whether it is read-only by nature or specified as FOR READ ONLY (FOR FETCH ONLY). The aforementioned improvement can be achieved by applying "FOR READ ONLY" when other locking hints are not specified. Patch to be uploaded soon.