We have a need to be able to limit the time taken by various queries, inserts, updates, etc. If the time alotted is exceeded, then the statement will be retried against another datasource.

I've spotted the Query.setTimeout method as a potential means of doing this, but I don't believe it will work since it is Statementd bound. Am I correct in thinking that this value applies only to the primary Statement executed, and not to any other Statements necessary to grab the rest of the object graph as encoded in the mapping files (collections, associations)?

For example:



Now, if parent also had a collection of children (say 1000 for the sake of argument), would the query to load that data by Hibernate.initialize(getChildren()) also inherit the timeout value?

Further, is there any way of putting (via Hibernate) a time restriction on the entire list()/scroll() operation, no matter how many underlying queries need to be generated? If not, I suppose we can always session.cancelQuery() from another thread.

Thanks!
Ken

You are correct, there is no way to limit association fetches...

Thanks Gavin. Due to the way our app works we need to write the monitor thread anyway, but is there any value to having Hibernate internalize this sort of behavior?

Ah, I think its probably a bit difficult to implement, and a very unusual requirement.

You see really, this is the job of JTA. The transaction manager is supposed to notice stalled transactions and end them cleanly.

Good point...it just so happens that for our requirement it isn't necessarily a stalled transaction, just one that is too slow. Our requirements say that once we've exceeded the limits set on a particular query (i.e. due to load or number of records, or even due to database explosion), we're supposed to fail over to a backup datasource containing slightly lagging (30 mins on average) data. In other words, we have a contract that states the maximum amount of time each particular call will take.

Since you've so kindly provided Session.cancelQuery, we'll just have to write the monitor thread ourselves and kill anything that runs too long.

[quote="KenR"]Good point...it just so happens that for our requirement it isn't necessarily a stalled transaction, just one that is too slow. Our requirements say that once we've exceeded the limits set on a particular query (i.e. due to load or number of records, or even due to database explosion), we're supposed to fail over to a backup datasource containing slightly lagging (30 mins on average) data. In other words, we have a contract that states the maximum amount of time each particular call will take.

Since you've so kindly provided Session.cancelQuery, we'll just have to write the monitor thread ourselves and kill anything that runs too long.[/quote]

The problem though is that the query was fired by a http request and if the user wants to cancel it by press of a button, how would I get an handle to that query which is already running in the DB?
I have created a forum question for the same. please refer

http://forum.hibernate.org/viewtopic.ph ... ancelquery

my code segment is

Session session = getSession();
org.hibernate.Transaction trans = session.getTransaction();
trans.begin();
session.cancelQuery();
trans.rollback();

Even after this, I am able to see the query that is running in the DB and gets timed out after some time.

Is there any possibility that we can get handle to the query which is active in the DB?

Would be happy to get your assistance in this ASAP.