How does idle-timeout-minutes work in a datasource ?

Solution Verified - Updated

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP)
    • 5
    • 6
    • 7
  • EAP 5
    - idle-timeout-minutes as defined in a *-ds.xml
  • EAP 6 and later
    • idle-timeout-minutes as defined in
      • jboss-as-resource-adapters_1_*.xsd
      • jboss-as-datasources_1_*.xsd

Issue

  • What is idle-timeout-minutes in datasource configuration ?
  • Why is the connection not removed after idle-timeout-minutes ?
  • Why is DestroyedCount always 0 for a data source which has not set max-pool-size and min-pool-size ?
  • What is use of idle-timeout-minutes in datasource?
  • We are relying on idle-timeout-minutes to flush idle connections and get below errors:
IO Error: End of TNS data channel; nested exception is java.sql.SQLRecoverableException: IO Error: End of TNS data channel
...
Closed Connection; nested exception is java.sql.SQLRecoverableException: Closed Connection
  • How to set idle timeout in a data source?
  • What is the meaning of the following TRACE event?
<TIMESTAMP> TRACE [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (IdleRemover) Destroying timedout connection org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@3b729fa1[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@624e2184 connection handles=0 lastUse=1388075895091 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@42b996d7 pool internal context=SemaphoreArrayListManagedConnectionPool@3a6f01bf[pool=<POOL NAME>]
  • want to know when the connections would drop to 0, is it when the idle timeout kicks in ?

Resolution

idle-timeout-minutes is the maximum time, in minutes, before an idle (unreserved/unused) connection is closed. The actual maximum time depends upon the idleRemover scan time, which is half of the smallest idle-timeout-minutes of any connection pool.

The common sequence of operations for a connection might be:

  • One or more connections are created when the JBoss managed datasource is created
  • The application requests a pooled connection by calling DataSource.getConnection() on the JBoss managed datasource
  • JBoss returns a wrapper around the underlying JDBC connection provided by the JDBC driver. The wrapper implements the Content from docs.oracle.com is not included.java.sql.Connection interface and delegates to the underlying driver connection for most operations.
  • The application uses the returned connection wrapper to perform one or more SQL commands (SQL insert/update/delete/select or DDL operations).
  • The application calls Connection.close() to return the connection to the pool. Please note that it is the developers responsibility to close the wrapped connection, e.g. in a finally block. Until the connection is returned to the pool, it is considered to be in-use even if it is not actively being used to execute SQL commands. Until it is returned to the pool, it is not eligible for timeout.
  • After the connection is returned to the pool, if it remains unused for more than idle-timeout-minutes, the idleRemover destroys the idle connection. This can result in the number of physical connections being reduced to 0 or the min-pool-size.

The maximum time an idle connection might stay in a pool is :

{<idle-timeout-minutes>(of the pool with the idle connection)  + 1/2 (<idle-timeout-minutes>(of the smallest idle-timeout-minutes of all pools))}

The idle-timeout-minutes property should be configured to a value greater than 0 but less than the timeout period specified on the database server, network firewalls, etc. to permit graceful termination by JBoss before idle connections are externally severed.

NOTE: Make sure that the database timeout is larger when compared to datasource timeouts to avoid java.sql.SQLException: Closed Connection errors.

NOTE: One should not rely only on idle-timeout-minutes but also use connection validation. See EAP 6/7 JDBC datasource not fault tolerant in JBoss and needs connection validation enabled for more details.

NOTE: See how to Disable the idleRemover for a JCA connection pool in JBoss

See also Connections are not closed after 'idle-timeout-minutes' in EAP for common cases where connections do not timeout.

Diagnostic Steps

Enable TRACE for the org.jboss.jca.core.connectionmanager package to get more details :

In the EAP 6 standalone.xml or domain.xml (depending on your chosen profile):

            <logger category="org.jboss.jca.core.connectionmanager">
                <level name="TRACE"/>
            </logger>

In the below log excerpt where idle-timeout-minutes equals 5 minutes, the IdleRemover runs the scan 3 times, after every 1/2 * (idle-timeout-minutes) and destroys the idle connection after 7.5 minutes.

17:53:12,243 TRACE [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (http-/127.0.0.1:8080-1) Returning connection to pool org.jboss.jca.core.connectionmanager.listener.NoTxConnectionListener@17c95553[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@2300c858 connection handles=0 lastUse=1366633392243 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@8611b5c pool internal context=SemaphoreArrayListManagedConnectionPool@178a4622[pool=MysqlDS]]
17:55:42,073 TRACE [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Result of await: false
17:55:42,073 DEBUG [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Notifying pools, interval: 150000
17:58:12,074 TRACE [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Result of await: false
17:58:12,074 DEBUG [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Notifying pools, interval: 150000
18:00:42,074 TRACE [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Result of await: false
18:00:42,075 DEBUG [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Notifying pools, interval: 150000
18:00:42,075 TRACE [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (IdleRemover) Destroying timedout connection
SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.