Hi,
I built a hibernate query to search records from Oracle RDB and we observed that while executing this query, the status in Oracle RDB is W(Write) mode whereas it should have been in R(Read) mode. I managed to set Query.setReadOnly(true) but the situation does not change. This problem can affect a lot the performance.
If I try with SQL without using Hibernate, I got Read status.
Could anyone had this experience and please give us some indication?
Thank you in advance for any help.
Tvan

q.setReadOnly is not related to the db but to hibernate's session assumption about the object, e.g. the resulting objects will not be dirty chekked etc.

Hi Max,
I am agreed with you about the dirty checking. If not dirty checking, it means that it does not synchronize with the db. Then no Write action to execute from Hibernate?
Thank you
Tvan

yes, no write actionn should occur on these objects if they are loaded from that query.

Hi Max,
Yes. That is the heart of the subject. The problem is that on Oracle RDB, we observed that the server marks status action Write. And with same query in SQL executed not via Hibernate, the status is Read.
I uses Hibernate/Spring and here you can see the code from the method:


Thank you
Tvan

i do not know what you are talking about when you say "status write".

Does data actually change ?

Hi Max,
Data does not change with Query.setReadOnly(true) but Oracle RDB server registers the query status as Write for each found record. This status will be used to isolate other users at the same milisecond to update the record. I have found the solution by
session.connection().setReadOnly(true);
objList = query.list();
session.connection().setReadOnly(false);

and I see the status is Read now.

But I will be forced to put the code in try catch in order to catch SQLException. I don't see that is a nice way if I already used Hibernate/Spring. Your opinion will be very appreciated
Thank you.
Tvan

that method has the following comment:

"This method cannot be called during a transaction."

Meaning you are probably running that query in autocommit mode when running in your external test case.

When doing search, is it neccasary to do a Transaction? because there is no consequence on persistent data.
I also set defaultAutoCommit to false as Hibernate adviced, but I can not then update data. I don't know why and I had to set to true.
Thank you for your opinions.
Tvan

a connection always run within a transaction. with or without commit mode.

Some db's might be able to optimize osmethings so if you want that then handle a read-only connection to the session directly or write a custom ConnectionProvider to configure the connection accordingly.

/max

Thank you for your idea.
I use Hibernate/Spring, my xxxdaoImpl class extends HibernateDaoSupport of Spring so I don't call session.getTransaction() to do begin and commit because I supposed that the TransactionManager of Spring does the role? So session.connection().setReadOnly(true); is inside a transaction?

By the way, I just wonder myself that when Query.setReadOnly is true, why the Connection.setReadOnly is still in false?
Thank you
Tvan

because query.setReadOnly only has to do with the objects being returned from that db -it has nothing to do with the rest of the objects you can/would be loading.

OK. I am agreed with you but when I use java.sql.Connection without Hibernate and I need not to do setReadOnly(true), the problem does not occurs. But with Hibernate the this Connection is modified diffrently from the origine and make search query less performant . I don't see the reason to this modification?
Thanks
Tvan

i already told you....you are probably running in autocommit mode whjen not using hibernate

You are right. The Db server is by default set to autocommit.
But I also mentionned that I had tried true and false with autocommit in Hibernate and in the both configuration, it is always Write mode. There is a problem in someway.
Thanks
Tvan