Connection Validation in GlassFish JDBC Connection Pool
By jagadish on Apr 16, 2007
Connection Pool manages a pool of connections and serves the user
requests quickly as creating a new
connection for each user request will take longer time. Since the connections are pooled, it is possible
that a connection may become invalid or stale, i.e., database server may set the connection state to
closed because of network failure or database might have been restarted. In such scenario, if the application
retrieves a connection from connection pool, failure will occur indicating that the connection is no more valid.
To circumvent stale connections issue, connection pool property "is-connection-validation-required" can be set
to true. This flag indicates the connection pool to validate the connection for every request by the application.
Validating a connection ensures that the connection is in healthy state. If the connection validation fails,
connection will be removed from the pool and the application will get valid connection. [Pool will either return the
next valid connection or create a new connection]. Thus validation ensures that every getConnection() request
by the application will be served by a valid connection.
To set validation :
Following command will list resources by name "DerbyPool"
To get the list of connection pool attributes and properties,
domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method = auto-commit
domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required = false
To enable connection validation :
domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required = true
By default, the validation mode is "auto-commit". Auto-commit property of the connection to be validated
will be set & reset by the validation logic. Most of the jdbc-drivers cache the "auto-commit" value of the
connection and hence actual validation (connecting to database) does not happen.
Instead, setting table-based validation will ensure that validation happens properly. A valid table name need to
be provided, validation logic will issue a query on this table. If the connection is stale, this query will fail and
hence will be removed from the pool.
To set Table-based validation :
domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method = table
bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name=sys.systables
domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name = sys.systables
Sometimes, database restart may result in all the connections in the pool to be invalid.
In such a case, first get connection after restart will result in validating a connection, removing it from the pool,
validating the next connection and so on. Eventually all connections will be validated, removed from the pool
and pool will be reinitialized.
Following property "fail-all-connections" will ensure that when one of the connections become valid, pool will be
reinitialized, ie., all connections will be destroyed and initialized again.
domain.resources.jdbc-connection-pool.DerbyPool.fail-all-connections = true
Above property need to be set only in cases where there are more possibilities of database restarts/failures, as
all connections are dropped and recreated for a single connection validation failure.
ReferencesApplication Server - Reference Manual > create-jdbc-connection-pool