Connection validation
Connection validation ensures that connections aren’t assigned to your application after the connection has already gone stale.
Connection validation is always a trade-off between how sure you want to be that a connection is valid and the performance impact that the validation mechanism can exert into application that use the pool. There will be a negative performance impact by having to return an invalid connection by your application and borrow a new one, so finding the right balance is key.
Before using a connection from the pool a simple query is sent to test the connection. If there is an issue with the connection, it is removed from the pool and another one is used. If you have an issue such as the database being down and you have a large number of connections then each of those connections will be tested and removed.
In order to avoid this, you can set a connection validation so that if a connection fails, all remaining open connections are closed.
Set up the validation using the admin console
-
Click on the name of the connection pool
-
Select the Advanced tab
-
Scroll down to the Connection Validation section and select the following settings:
-
Connection Validation required
-
Validation method - custom-validation
-
Validation class name – a proper class name for the chosen JDBC driver, such as
org.glassfish.api.jdbc.validation.DerbyConnectionValidation
for a Derby DB
-
From the same screen, you can also set whether to close all connections on failure.
Set up the validation using administration commands
To turn on connection validation using asadmin
commands:
asadmin> set resources.jdbc-connection-pool.test-pool.connection-validation-method=custom-validation
asadmin> set resources.jdbc-connection-pool.test-pool.validation-classname= org.glassfish.api.jdbc.validation.DerbyConnectionValidation
asadmin> set resources.jdbc-connection-pool.test-pool.is-connection-validation-required=true
You can also set whether to close all connections on failure with the following command:
asadmin> set resources.jdbc-connection-pool.test-pool.fail-all-connections=true