Timeouts

NuoDB and JDBC support 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

For more information on the timeout options available from third-party DataSource providers, see Third-Party Data Sources.

Idle Timeout

It is the maximum time a connection remains open if it is not executing any SQL. Idle timeout is a value in seconds and can be set in two different ways in the 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 is logged in the net category. Any active transaction on that connection is terminated and any work performed 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

It is the maximum amount of time a database connection is kept alive in a connection pool before it is closed and replaced with a new one.

When using a connection pool, connections should always timeout eventually. With NuoDB’s DataSource class, this is enabled by setting maxAge to a non-zero positive value.

Option Description Default Value

maxAge

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

0 (never timeout)

Every time a connection is returned to the pool, its age is checked against maxAge and if it has expired (timed out) the connection 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 as long as it is executing SQL.

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

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

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

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 pool of connections is created in advance and reused, with connections being provided to the application as needed. After use, connections are returned to the pool and remain available for future requests.

Without connection aging, new connections are only created if the pool needs to expand or if connections fail because their TE is no longer available. A busy pool may grow to maximum size and never fetch new connections. In such situations, adding new TEs provide limited benefit, since the pool will take long time to require additional connections.

By enabling connection timeouts, the pool will is forced to get new connections periodically, including connections to any newly added TEs.

Determining Value for Connection Aging

The appropriate connection lifetime depends on the application. Killing and creating connections creates overhead, which is the reason for using a connection pool. Connections can remain open for hours or even days, but longer lifetimes limit the pool’s ability to dynamically scale in or out of TEs.

The typical connection lifetime is 5-10 minutes but it will depend on your requirements. If scaling of TEs occurs rarely, then a longer timeout is acceptable. For environments with frequent scaling or unreliable networks, shorter timeouts may be more appropriate.

Connection Wait Timeout

It is the maximum wait time to obtain a database connection from the pool before failing.

If all the connections in the pool are in use, a thread will have to wait for a connection 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 connection, 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 a deadlock) or give up.

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

Option Description Default Value

maxWait

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

0 (never times out)

Query Timeout

It is the maximum time a query is allowed to run before the system stops waiting and throws an error.

Query timeout is a value in seconds and can be configured in two different ways in the 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)

  • When a query times out, the system takes additional time on top of QUERY TIMEOUT to complete the rollback of the timed-out statements before throwing an exception.

  • The SQL statements ROLLBACK and ROLLBACK TO SAVEPOINT ignore QUERY TIMEOUT. For more information, see ROLLBACK.

When SQLTimeoutException occurs, it is the client’s responsibility to determine how to proceed. Any existing transaction remains active holding any acquired locks, and the connection remains usable.

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.

  • Roll back the transaction and close the connection.

For long-term improvement, consider optimizing the query to reduce execution time or use batch processing to reduce the size of the result set.

Batch Processing Long Queries

The query time includes the time to run the query and the time to process the result set. Therefore, it is possible for the query to complete within the allotted time, but a timeout exception could occur while processing the data returned.

If this happens, process the data in smaller batches that can be handled within the timeout limit. This is also recommended when working with large result sets as they can consume a lot of memory. Increasing the query timeout must be considered only if breaking the query into multiple queries is not possible.

For example:

/* Exception handling is not included in this code. */
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;
    }
}

Configuring Query Timeout in Microseconds

In addition to the standard JDBC Statement#setQueryTimeout() method to specify a query timeout in seconds, NuoDB also provides the setQueryTimeoutMicros() method in its implementation class, RemStatement. The setQueryTimeoutMicros() method can be used to specify a query timeout in microseconds. The parameter is of type long.

RemStatement#getQueryTimeoutMicros() can be used to obtain the query timeout value as a long, representing the duration in microseconds. To use RemStatement#setQueryTimeoutMicros(), cast a Statement object to a RemStatement object.

In the following example, the query timeout is set to 500,000 microseconds.

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)
When upgrading the JDBC driver, ensure that any code using RemStatement still compiles.
Statement.isWrapperFor() returns false in all cases and should not be used.

Lock Wait Timeout

It is the maximum time a transaction will wait to acquire a database lock held by another transaction before giving up. Lock wait timeout ensures 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 roll back. Once the first transaction releases the lock, the second transaction can acquire a lock and make changes to the data.

When a lock wait timeout 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.

When a query exceeds its timeout limit, the system requires additional time after LOCK_WAIT_TIMEOUT to complete the rollback of the timed-out statements before a client exception is thrown. For more information, see ROLLBACK.