Working with Timeouts

NuoDB and JDBC supports several timeout options. However they are specified in a number of different ways:

Timeout Type Property How To Use Implemented By

Idle Connection

IDLE_CONNECTION_TIMEOUT

Global setting in the TE.

TE

idle-timeout

Client connection property.

JDBC Driver

Connection Aging

maxAge

NuoDB DataSource property.

JDBC Driver

Connection Wait Time

maxWait

NuoDB DataSource property.

JDBC Driver

Query Timeout

setQueryTimeout()

Method on JDBC Statement class.

TE

QUERY TIMEOUT

Connection property specified using SET.

TE

Lock Wait Timeout

DEFAULT_LOCK_WAIT_TIMEOUT

System property specified using SET SYTEM PROPERTY.

TE

lock-wait-timeout

Connection property specified when establishing a connection.

TE

LOCK_WAIT_TIMEOUT

Runtime parameter specified using SET LOCK_WAIT_TIMEOUT

TE

This section also discusses a NuoDB extension to set Query Timeout in microseconds, useful for short timeouts.

To review comparable timeout options available from third-party datasource providers, see here.

Idle Timeout

How long does a connection remain open if it is not executing any SQL?

Idle timeout is a value in seconds and can be set in two different ways in your client application.

Option Description Default Value

IDLE_CONNECTION_TIMEOUT

System property that applies globally to all connections from any application to the database. Only a database user with System.ADMINISTRATOR role can SET this property.

0 (disabled by default)

idle-timeout

Connection property, allowing idle timeout to be set on a per connection basis. Set to 0, to default to the global setting.

-1 (no value, connections never timeout)

A connection is considered idle if it is not executing any SQL, even if a transaction is still open. When a connection exceeds its idle time it is closed and a message logged in the net category. Any ongoing transaction is also terminated and any work done up to that point is rolled back and lost.

  • If using a pooled DataSource (such as NuoDB’s own DataSource or a Hikari DataSource), the idle timeout will apply to all connections in the pool.

  • If using NuoDB’s DataSource, when an idle connection times out, if the number of connections drops below the specified minimum (minIdle), then a new connection is created to replace it.

Connection Aging Timeout

How long should a connection live before being destroyed?

Option Description Default Value

maxAge

Connection aging is a NuoDB DataSource property and is a value in milliseconds.

0 (never timeout)

When using a connection pool, connections should always be made to timeout eventually.

With NuoDB’s DataSource class, this is enabled by setting maxAge to a non-zero positive value.

Every time a connection is returned to the pool, its age is checked against maxAge and if it has expired (timed out) it is closed and not returned to the pool. The pool also periodically checks the age of idle connections in the pool, and any that are older than maxAge are closed and removed from the pool. A connection will never timeout due to aging so long as it is executing SQL.

The age check is only performed on idle connections (connections that are not currently in use). If need be, the pool will request a new connection to replace the old one.

As NuoDB is an Elastic Database with scalable number of TEs, connection aging is very important as it allows connections to be continuously rebalanced over the available TEs.

Other third-party DataSources also support a connection aging/time-to-live property.

Importance of Connection Aging

In a distributed system, TEs may come and go for a number of reasons, both planned and unplanned.

  • TEs may be scaled out or in depending on load.

  • TEs may have to be stopped due to a software or hardware upgrade (this is why we recommend running more than one TE to ensure redundancy).

  • A TE may also terminate unexpectedly, for example, as a result of a platform restart or failure, or a process error

  • A network connection may fail so the TE appears to have gone, even when it is still running.

Using a connection pool is generally recommended because creating and closing connections is expensive. Instead a set of connections is created up front, placed in the pool and then given out to the application as it needs them. Once used, the connections return to the pool until next time.

Without connection aging, new connections are only created if the pool needs to grow or if connections fail because their TE is no longer available. A busy pool may grow to maximum size and never fetch new connections. Scaling out to add new TEs is of limited value in this scenario because it will take a long time for the pool to need new connections.

By making the connections timeout, the pool will have to get new connections periodically, including connections to any new TEs.

Determining Value for Connection Aging

How long a connection lives for is application specific. Killing and creating a new connection has an overhead, it’s why you are using a connection pool, so you don’t want t make it too short. Connections can live for hours or even days, but that does not allow for dynamic scaling in or out of TEs.

The "typical" value is 5-10 minutes but it will depend on your requirements. If scaling of TEs occurs rarely, then a longer timeout is acceptable. If you regularly scale, or if your network is unreliable, you might choose a smaller value.

Connection Wait Timeout

How long to wait for a connection from the pool before giving up?

Option Description Default Value

maxWait

Connection wait time is a NuoDB DataSource property (maxWait) and is a value in milliseconds.

0 (never timeout)

If all the connections in the pool are in use, a thread will have to wait for one to become free. Typically this means the code will block at dataSource.getConnection() waiting until a connection is available for use.

To control how long to wait for a connections set maxWait to a non-zero positive value. If the wait time is reached an exception is raised. You may decide to retry (as you would with Deadlock) or give up.

Other third-party DataSources also support a connection wait time property.

Query Timeout

How long to wait for a query to run before giving up?

Query timeout is a value in seconds and can be set in two different ways in your client application. The timeout itself is implemented in the TE.

Option Description Default Value

setQueryTimeout()

This method sets the property for a JDBC Statement (or any subclass such as PreparedStatement).

0 (never timeout)

QUERY TIMEOUT

Set a query timeout for the current connection. It is one of several properties that can be set for a connection.

0 (disabled)
From the Javadoc for java.sql.Statement

Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds. By default there is no limit on the amount of time allowed for a running statement to complete. If the limit is exceeded, an SQLTimeoutException is thrown. A JDBC driver must apply this limit to the execute, executeQuery and executeUpdate methods.

It is up to the client to decide what to do if it receives a SQLTimeoutException. Any existing transaction is still ongoing, with any locks it is holding, and the connection is still useable.

The client must perform one of the following options:

  • Retry with a longer timeout using the same connection.

  • Commit the work performed so far and close the connection.

  • Give up, rollback the transaction and close the connection.

Longer term, consider optimizing the query to make it faster and/or use batch processing (next section) to reduce the size of the result set.

Long Queries and/or Large Result Sets

The query time includes both the time to run the query and the time to process the result set. It is therefore possible for the query to run within the allotted time, but for a timeout exception to be raised whilst processing the data returned.

If this is happening, try processing the data in batches that are small enough to be handled without exceeding the query timeout value. This is good practice anyway with very large result sets as they can consume a lot of memory. Only if breaking the query into multiple queries is not possible should you consider increasing the query timeout.

Here is a batching example:

/* For clarity, this code ignores exception handling. */
boolean finished = false;
int offset = 0;
int batchSize = 10000;

try (PreparedStatement statement = dbConnection.prepareStatement( //
                "SELECT * FROM T_CUSTOMERS OFFSET ? FETCH NEXT ?")) {

    while (!finished) {
        /* Execute query */
        statement.setInt(1, offset);
        statement.setInt(2, batchSize);
        ResultSet rs = statement.executeQuery() ;

        /* Process results */
        int nRows = 0;

        while(rs.next()) {
            /* Process the row */
            ... // Use the result-set

            nRows++;
        }

        offset += batchSize;

        /* If fewer than batchSize rows are returned, then we have run out */
        /* of rows to process.                                             */
        finished = nRows < batchSize;
    }
}

Setting Query Timeout in Microseconds

In addition to the standard JDBC Statement#setQueryTimeout() method which allows you to specify a query timeout in whole seconds, NuoDB also provides a setQueryTimeoutMicros() method in its implementation class, RemStatement.

The setQueryTimeoutMicros() method allows you to specify the number of microseconds for the timeout. The parameter is of type long.

There is also an RemStatement#getQueryTimeoutMicros() which returns the timeout value as a long value which is the number of microseconds.

To use RemStatement#setQueryTimeoutMicros(), you must cast a Statement object to an RemStatement object as in the code snippet below.

The internal class RemStatement is potentially subject to change, although it is unlikely that NuoDB would remove this method. Care should be taken when upgrading the JDBC driver to check that any code using RemStatement still compiles.

Here we are setting the query timeout to 500,000 microseconds (half a second).

Statement statement = connection.createStatement();
long timeoutInMicroseconds = 500000L;
RemStatement remStmt = null;

if (statement instanceof RemStatement)
    /* Simple cast to subtype */
    remStmt = ((RemStatement)pStatement);
else {
    try {
        /* statement may be a proxy wrapping an actual Statement instance */
        remStmt= statement.unwrap(RemStatement.class);
    }
    catch (ClassCastException e)
        logger.warn("Unable to setQueryTimeoutMicros(), ignored. Use setQueryTimeout() instead.");
}


if (remStmt != null)
    remStmt.setQueryTimeoutMicros(timeoutInMicroseconds)
Statement.isWrapperFor() returns false in all cases and should not be used.

Lock Wait Timeout

Lock wait timeout is a locking mechanism. Locking mechanisms ensure that multiple transactions cannot alter the data at the same time.

A transaction acquires a lock on the data before updating it. When multiple transactions are trying to update the same row, one transaction gets a lock and holds it while another transaction requests a lock on the same data. This means that the second transaction is blocked by the first transaction. It has to wait for the first transaction to commit or rollback. Once the first transaction releases the lock, the second transaction can acquire a lock and make changes to the data.

When a lock wait time is configured, if the first transaction fails to release the lock, the second transaction will time out and the database will respond to the second transaction with an error message, "Error:58000: Timed out after <time> milliseconds." This behavior keeps the second transaction from waiting forever.

Option

Description

Default value

DEFAULT_LOCK_WAIT_TIMEOUT

System property specified using SET SYSTEM PROPERTY.

0

lock-wait-timeout

Connection property specified when establishing a connection.

Value of DEFAULT_LOCK_WAIT_TIMEOUT

LOCK_WAIT_TIMEOUT

Runtime parameter specified using SET LOCK_WAIT_TIMEOUT.

For more information, see SET, SQL System Properties, and Connection Properties.