You can configure a timeout on the data source of an application
so that the transaction or statement timeout occurs if a query fails
to return, because of a deadlock or blocked transactions.
Before you begin
Traditional Java Database
Connectivity (JDBC) provides a standard interface, called java.sql.Statement.setQueryTimeout,
to limit the number of seconds that a JDBC driver waits for a statement
to execute. This can used by an application to control the maximum
amount of time the application waits for an SQL statement to complete
before the request is interrupted. With earlier versions of WebSphere® Application Server, the only way
of setting a query timeout is by programmatically establishing an
SQL query timeout in the application by invoking the java.sql.Statement.setQueryTimeout interface
on every statement.
About this task
In Version 8, you can configure this query timeout using
either of the following two custom properties at the data source level:
- webSphereDefaultQueryTimeout establishes a default
query timeout, which is the number of seconds that an SQL statement
may execute before timing out. This default value is overridden during
a Java Transaction API (JTA)
transaction if the syncQueryTimeoutWithTransactionTimeout custom
property is enabled.
- syncQueryTimeoutWithTransactionTimeout uses the
time remaining (if any) in a JTA transaction as the default query
timeout for SQL statements.
By default, query time is disabled. Based on the presence and
value of the two new data source custom properties, a timeout value
is calculated of either:
- the time remaining in the current JTA transaction based on the
TM timeout setting - syncQueryTimeoutWithTransactionTimeout
- the absolute number of seconds specified by configuration - webSphereDefaultQueryTimeout
The calculated timeout is then used in conjunction with the JDBC
API to set a query timeout value on each statement.
Procedure
- Open the administrative console.
- Go to the panel for
the data source.
- Click
- Click .
- Click Custom properties under Additional
Properties.
- Click New.
- Enter webSphereDefaultQueryTimeout in
the Name field.
- Enter the number of seconds to use for the default query
timeout in the Value field. The
timeout value is in seconds. A value of 0 (zero) indicates no timeout.
- Click OK.
- Click New.
- Enter syncQueryTimeoutWithTransactionTimeout in
the Name field.
- Enter true or false in
the Value field. A value of true indicates
to use the time remaining in a JTA transaction as the default query
timeout.
- Click OK.
- Save your changes. The updates go into effect
after the server is restarted.
Results
You have configured the query timeout on the data source of
your application.
Example
The following example illustrates using
webSphereDefaultQueryTimeout
= 20 and
syncQueryTimeoutWithTransactionTimeout =
true:
statement = connection.createStatement();
statement.executeUpdate(sqlcommand1); // query timeout of 20 seconds is used
statement.executeUpdate(sqlcommand2); // query timeout of 20 seconds is used
transaction.setTransactionTimeout(30);
transaction.begin();
try
{
statement.executeUpdate(sqlcommand3); // query timeout of 30 seconds is used
// assume the above operation took 5 seconds, remaining time = 30 - 5 seconds
statement.executeUpdate(sqlcommand4); // query timeout of 25 seconds is used
// assume the above operation took 10 seconds, , remaining time = 25 - 10 seconds
statement.executeUpdate(sqlcommand5); // query timeout of 15 seconds is used
}
finally
{
transaction.commit();
}
statement.executeUpdate(sqlcommand6); // query timeout of 20 seconds is used
The
following example illustrates using
webSphereDefaultQueryTimeout
= 20 and
syncQueryTimeoutWithTransactionTimeout =
false:
statement = connection.createStatement();
statement.executeUpdate(sqlcommand1); // query timeout of 20 seconds is used
statement.executeUpdate(sqlcommand2); // query timeout of 20 seconds is used
transaction.setTransactionTimeout(30);
transaction.begin();
try
{
statement.executeUpdate(sqlcommand3); // query timeout of 20 seconds is used
// assume the above operation took 5 seconds
statement.executeUpdate(sqlcommand4); // query timeout of 20 seconds is used
// assume the above operation took 10 seconds
statement.executeUpdate(sqlcommand5); // query timeout of 20 seconds is used
}
finally
{
transaction.commit();
}
statement.executeUpdate(sqlcommand6); // query timeout of 20 seconds is used
You
can override the query timeout for a statement at any time by invoking
the
java.sql.Statement.setQueryTimeout interface
from your application code.