db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oyvind.Bakk...@Sun.COM
Subject Re: DERBY-31: setQueryTimeout semantics
Date Fri, 22 Apr 2005 07:32:04 GMT
Daniel John Debrunner skrev:
> Oyvind.Bakksjo@Sun.COM wrote:
>>(*) It is possible to write long-running statements which a certain
>>database implementation, in theory, can defer execution of, until the
>>application calls ResultSet.next(). In such a case, the execute phase
>>will be quick, but fetching a single record may take longer time than
>>the query timeout that was set for the statement. In practice, this will
>>rarely be the case. If it should prove to be a problem later, one could
>>consider applying the query timeout to the execution of
>>ResultSet.next(), but for now I don't think we should worry about it.
> I actually think that next() (or any ResultSet method that moves to a
> row) taking a long time is not rare, it will be just (or almost) as
> common as executeQuery taking a long time. A typical case would be the
> locks for the rows required by the next are held by other transactions
> and therefore the next() has to wait. Or with a complex join the amount
> of work to determine the first row may be almost identical to that to
> determine the Nth row.

I deliberately disregarded waiting for locks when I wrote the paragraph 
above, since Derby already has a mechanism for timing out while waiting 
for locks, using the derby.locks.waittimeout property. Having thought a 
little more about this, however, I've changed my mind.

* The application can set a different query timeout for each statement 
it executes. This can not be achieved with the derby.locks.waittimeout 
* The application can set a querytimeout less than derby.locks.waittimeout
* If a query waits N seconds M times (i.e. for M locks), N*M may be far 
larger than the waittimeout property, but it still won't time out if N < 

In principle, there is no requirement that execute produces any result 
at all, so all work could be done while invoking ResultSet.next(). In 
this case, it makes sense to have the querytimeout affect not only 
Statement.execute(), but the fetching phase as well. It might also be a 
more orthogonal approach implementation-wise.

We're then left with three choices:
1) setQueryTimeout() only affects Statement.execute()
2) setQueryTimeout() affects Statement.execute() and ResultSet.next(), 
starting from zero for each invocation
3) setQueryTimeout() affects Statement.execute() and ResultSet.next(), 
accumulating time spent in each invocation

I think option 3 is the most sensible one, but I'm open to other 
opinions. Also, it would be interesting to consider what other vendors 
do. Does anybody know?

Oyvind Bakksjo
Office: +47 73842119
Mobile: +47 92283522

View raw message