Hi Florin:
So glad to hear derby user is using 10.6.2.1. I suspect there might be
something more complicated than Statement.QueryTimeout was not working. Is it
possible for you to reproduce a smaller test case to allow people to further
investigate the case?
Like Kristian pointed out, Statement.QueryTimeout should take effect. I
attach a repro that is using Statement.QueryTimeout. I tested against 10.6.2.1
release and it is working for me.
Best Regards,
Lily
________________________________
From: "[email protected]" <[email protected]>
To: [email protected]
Sent: Wed, October 20, 2010 8:38:11 AM
Subject: AW: does Derby honor the statement.setQueryTimeout(int) ?
Hi Kristian,
Changing of the global timeout is not an option, since I need to have most of
the statements behave normally (to wait as long as neccessary to succeed) and
just a few statements with a "fail fast" behavior.
The concrete problem: I have a kind of a timer that fires events at regular
intervals (5 sec) and I have to perform some actions. It might be possible
under
certain circumstances that the performing of the actions take (much) longer
than
the fixed rate of the timer, so that the next events are fired before the long
running one is finished. However, if the actions are involving the same sets of
rows, I want that the overlapping ones to fail fast and roll back the
transaction.
Now because Derby is waiting indefinitely, this scenario (events are comming
via
jms messages) will lead to the consumming of all available database
connections,
since for each jms message a new thread/transaction is created and implicitely
a
new connection.
Regards,
Florin
-----Ursprüngliche Nachricht-----
Von: Kristian Waagan [mailto:[email protected]]
Gesendet: Mittwoch, 20. Oktober 2010 17:24
An: [email protected]
Betreff: Re: does Derby honor the statement.setQueryTimeout(int) ?
On 20.10.10 17:16, [email protected] wrote:
>
> Hi everybody,
>
> I'm using Derby latest 10.6.2.1 and I'm having a problem with
> statement.setQueryTimeout(int).
>
> Namely I want to set a small value for the timeout (5 seconds) so that
> the statement (an update statement) will fail (relatively) fast if
> some other transaction is holding a write lock on the same row.
>
> The problem is that setQueryTimeout seems to be ignored, and the
> update is waiting until the global timeout - 60+ seconds.
>
> Is that a known problem with Derby ? Or am I doing something wrong ?
>
Hi Florian,
I'm not sure, but if Derby is waiting for a lock, it may not honor the query
timeout.
If the statement is doing "normal processing", the query timeout should take
effect. If your application code is set up correctly for retrying, is it an
option to lower the lock timeout as well?
Regards,
--
Kristian
> Best Regards,
>
> Florin Herinean
>
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/*
* This is a repro for showing that setting a statement timeout for
* the client driver also affects the maximum time that a query
* is allowed to execute.
*
* To run this repro:
*
* 1. Start a Derby server
* 2. Change the jdbc url to your server and port number
* 3. Run the program.
*
* It should fail with an exception.
*
* To make the query succeed, remove the following code line:
*
* Statement.setQueryTimeout(5);
*
* Note that without this timeout, the program might take 2 to
* 10 minuttes to complete.
*/
public class StatementTimeout
{
/* Client-Server Derby */
static String driver = "org.apache.derby.jdbc.ClientDriver";
static String jdbcUrl = "jdbc:derby://localhost:1527/derbyDB;create=true";
public static void main(String[] args)
{
try {
// Load the driver
Class.forName(driver).newInstance();
// Get a connection and a statement
Connection conn = DriverManager.getConnection(jdbcUrl);
Statement s = conn.createStatement();
// Set a Statement timeout
s.setQueryTimeout(5);
System.out.println("Set statement timeout to 5 seconds");
long before = System.currentTimeMillis();
// Execute a "long" query that takes about 2-10 minutes
ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM SYS.SYSCOLUMNS
t0, SYS.SYSCOLUMNS t1, SYS.SYSCOLUMNS t2, SYS.SYSCOLUMNS t3");
long after = System.currentTimeMillis();
while (rs.next()) {
System.out.println("Number of records: " + rs.getInt(1));
}
System.out.println("Query time: " + (after-before) + " ms");
rs.close();
conn.close();
} catch (Exception e) {
System.out.println("Exception thrown: " + e);
e.printStackTrace(System.out);
}
}
}
