Skip to:

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

Add support for "WITH UR" isolation clause on DB2

Description

The question is basically the same as Steve's, i.e. to force DB2 to use as few locks as possible on heavy load scenarios.

Although some might consider the use of the WITH UR clause a bad approach (since it allows dirty reads), the fact is that there are some cases where this is in fact acceptable, such as computing the total amount of rows of a query (for information purposes) or producing a high-level listing of items with very little detailed info. The bottom line is: if I need to trade performance for extremely accurate data, I surely want to be able to decide when this should happen.

I've already performed the changes that makes it possible to use both the FOR READ ONLY and the WITH UR clauses and I will be submitting a patch shortly so you can have a look at it. The strategy I've used was basically the following:

1. Added two new methods on the Dialect class:
String getDatabaseReadOnlyString(String sql) - for adding the READ ONLY clause
String getDirtyReadsString(String sql) - for adding the WITH UR clause

2. Changed the Query interface in order to allow the user to say whether he/she wants the query to allow dirty reads:
Query setAllowDirtyReads(boolean allowDirtyReads);

3. Added a default implementation on the AbstractQueryImpl which initializes the flag a false

4. Added a similar attribute on the QueryParameters class

5. Changed the prepareQueryStatement() method of the Loader class (just after the useLimit part in order:
a) Ask the dialect for the getDatabaseReadOnlyString() is there are no LockModes set (as did Steve)
b) Ask the dialect for the getDirtyReadsString() is the queyParameters allows dirty reads.

All the tests were well succeeded.

Hope you find these changes, at least, worth looking at.
Best Regards,
Ricardo

Activity

Show:

Christian BeikovJanuary 2, 2025 at 5:38 PM

Contrary to https://hibernate.atlassian.net/browse/HHH-3031 this would have to be a query wide setting which might not be supported on any other database than DB2.

We could add a special LockMode to allow reading uncommitted rows, but I don’t really fancy this idea at all.

Something that you can do now since ORM 6 is to implement this yourself through the SqlAstTranslator, but even before, you could have already implemented this through a custom hint that you handle in the DB2Dialect. Maybe it makes sense to implement this as a predefined database hint so that people can use e.g. addQueryHint(StandardDatabaseHints.ISOLATION_UNCOMMITTED_ROWS)?

eciApril 24, 2014 at 7:02 AM

Hello Julien,

@Julien, I read your suggestion.

In hibernate 4.3.1, I did not try to extend the class: org.hibernate.dialect.DB2Dialect yet .
After reading the modification made in hibernate 3.5 for the 'read only' support, I thought that the modification was complex.
In fact, I didn't see that I could override this method, but I will try your solution this week.

Thank you very much
Regards
Eddy

Julien KroneggApril 21, 2014 at 7:26 PM

@eci: Why don't you use your own MyDialog extends org.hibernate.dialect.DB2Dialog with an overrided method `getQueryHintString` ?

This method serves to

Apply a hint to the query. The entire query is provided, allowing the Dialect full control over the placement and syntax of the hint. By default, ignore the hint and simply return the query.

eciApril 10, 2014 at 11:39 AM
Edited

Hello,

Thank you for this answer.

I am in the same situation.
The DB administrator thinks I should use READ ONLY of every select request that we identified.

I am using DB2 9.7 and hibernate 4.3.1. I tried to resue the code given above.
The DB2 dialect has no getDatabaseReadOnlyString method. Is this supported in hibernate 4.3.1 .

How can I use a custom interceptor with an entity manager (because I use the entity manager way of doing things) ? Most exemples use Interceptor with a Session, but I have no explicit Session, because I use the EntityManager.

The 2 following solutions do not work for me: in the final generated SQL query I have no READ ONLY keyword:
query.setHint("org.hibernate.readOnly", true);
query.setLockMode(LockModeType.READ);

What I try to do is:
Instead of a NameQuery who generates:
Select SITE.NAME from SITE
I want to have
Select SITE.NAME from SITE FOR READ ONLY

Thank you very much

yFebruary 1, 2013 at 6:22 AM

@Ricardo Please provide which version on hibernate has solution for Uncommitted Read. Hibernate 4.1.9 ?? String getDirtyReadsString(String sql) - method not there disappointed face

Details

Assignee

Reporter

Original estimate

Time tracking

No time logged16h remaining

Components

Priority

Created December 11, 2008 at 9:49 PM
Updated January 2, 2025 at 5:38 PM

Flag notifications