Scenario: You are unable to fetch connections from the Database and the server log shows the error “Unable to get managed connection for <DataSource>” .
Facts: You have the following error in the server.log file:
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasource/PostgreDS
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
... 51 more
Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms]
On the other hand, if you are seeing the following error message in your log, the scenario is different:
java.sql.SQLException: Connection is not associated with a managed connection.org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@731b8c5
In this case, you are not exhausting the Connection Pool. You are probably caching Connection, Statement, PreparedStatement or ResultSet references. You shouldn’t cache any of the above objects. The application server is already caching those objects for you to optimize reuse across connections.
Action Plan
You are unable to fetch any more connection from the WildFly DataSource. To solve this issue, you have to check the following steps:
- Check for connection leaks in your code
- Check your Datasource pool size
- Inspect the SQL Statements performance
Check for connection leaks in your code
A Connection leak can be in your application code if you are not closing a Connection/Statement/ResultSet. You can check if you have a Connection leak in your code by setting the attribute debug=true in the <cached-connection-manager> element. For example:
<subsystem xmlns="urn:jboss:domain:jca:5.0">
<cached-connection-manager debug="true" error="false"/>
</subsystem>
When you set the attribute debug=”true” you will see the following message in your code, which warns you about the connection leak:
... INFO [org.jboss.jca.core.api.connectionmanager.ccm.CachedConnectionManager] (http-/127.0.0.1:8080-1) IJ000100: Closing a connection for you. Please close them yourself: org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6@6f1170a9: java.lang.Throwable: STACKTRACE
More details about the Cached Connection Manager in this tutorial: WildFly Connection Pool configuration
Finally, to write safe Connection statements, we recommend using the try-with-resources to close database connections. We recommend checking this article: Using try-with-resources to close database connections
Check the Datasource pool size
If you don’t have Connection leaks in your code, the next suspect is an inadequate value for the max-pool-size and blocking-timeout-millis. The following metrics can help to understand if are approaching the max-pool-size:
The number of connections currently in use:
/subsystem=datasources/data-source=YOURDATASOURCE/statistics=pool:read-attribute(name=InUseCount)
The maximum number of connections requested to the pool:
/subsystem=datasources/data-source=YOURDATASOURCE/statistics=pool:read-attribute(name=MaxUsedCount)
Again, check the article WildFly Connection Pool configuration to find how to monitor the Datasource to find out the optimal values for max-pool-size and blocking-timeout-millis.
Verify the SQL statements which take most of the time
Finally, if you don’t have Connection leaks in your code and you cannot further stretch the Connection pool size, you have to make more efficient the SQL Statements.
To do that, you should check which are the most costly SQL Statements that your application is using.
For example, MySQL provides a number of built-in tools to check the long running transaction. First of all, SHOW PROCESSLIST or SHOW FULL PROCESSLIST commands can expose the running queries in real-time.
On the other hand, with PostgreSQL you can check the running SQL Statements as follows:
-- show running queries (pre 9.2) SELECT procpid, age(clock_timestamp(), query_start), usename, current_query FROM pg_stat_activity WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; -- show running queries (9.2) SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;
Conclusion
This article was a walk through the resolution of two common datasource errors. “Unable to get managed connection” which happens when JBoss / WildFly runs out of database connections. On the other hand, “Connection is not associated with a managed connection” relates to caching JDBC Resources in your applications.
Found the article helpful? if so please follow us on Socials
