Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Include "FOR READ ONLY" hint in non-locking DB2 statements

Description

As per the DB2 documentation

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.

Attachments

2

Activity

Christian BeikovDecember 31, 2024 at 4:03 PM

If this really makes a difference from a performance POV, I’m ok to add this. Should be easy now to do that in the SqlAstTranslator for the respective DB2 variants.

eciApril 11, 2014 at 7:51 AM

Hello every one,

I voted for this issue. Please, do so.

I had a look at the modification provided in HH-3644. I use hibernate 4.3.1-Final and the modification is not included in it.

May be you need someone to validate the modification on hibernate 4.3.1. I could validate it if you provide me with a Jar file containing the modification, on a new hibernate version. I have DB2 9.7 and DB2 10 available.

Thank you
Eddy

Karol KowalczykJuly 8, 2011 at 10:10 AM

I changed hibernate classes from hibernate-3.2.6.ga library to add FOR READ ONLY by new method DetachedCriteria.forClass(Any.class).setForReadOnly();

tushar netakeJuly 5, 2011 at 11:51 AM

Hello,
Does this issue is resolved yet ?.. i have to implemented dielect for our database and we need to implement the "for browse access" which is same as "for read only" on locked rows.i found work in "HHH-3644 Add support for "WITH UR" isolation clause on DB2" by Ricardo Fernandes
but i think build in support support will really helpful.
hibernate.connection.isolation set the isolation level on entire connection object, i need read uncommited isolation level on query level

so will it be possible to add such support in dielect itself ??
Thanks in advance

R KrishnaOctober 19, 2010 at 1:04 AM

<prop key="hibernate.connection.isolation">1</prop> wouldn't this add with ur? OR is "for read only" different from "with ur"?

Details

Assignee

Reporter

Original estimate

Time tracking

No time logged16h remaining

Components

Priority

Created December 27, 2007 at 4:54 PM
Updated December 31, 2024 at 4:04 PM