No managed connections available within configured blocking timeout (JBoss 7 and Postgres)

Question

Periodically i get error :

ERROR JDBCExceptionReporter --> javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/myDB 08:12:05,928 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/mySoftware].[jsp]] (ajp--xx.255.0.yyy-8109-21) Servlet.service() for servlet jsp threw exception: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms]) at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377) and etc.

.

So, i have next datasource config. on JBoss AS :

<datasource jta="true" jndi-name="java:jboss/datasources/myDB" pool-name="ssbs-pssbs" enabled="true" use-ccm="true">
                    <connection-url>jdbc:postgresql://xx.255.0.yyy/myDatabase</connection-url>
                    <driver-class>org.postgresql.Driver</driver-class>
                    <driver>postgresql-jdbc4</driver>
                    <pool>
                        <min-pool-size>30</min-pool-size>
                        <max-pool-size>150</max-pool-size>
                        <prefill>true</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                    <security>
                        <user-name>tick</user-name>
                        <password>tack</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>30000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>

and in my Postgres server i allow max_connection on 500. Why i get this exception ?

Solution

Your primary issue is probably a connection leak, but maybe not. In other words database transactions are leaving the pool and not being returned. In this case there are a few specific things to start with because the problem is probably a code problem and not a database problem.

The first thing to check is the current status of everything in pg_stat_activity. This includes the most recent query and the transaction state. In a connection leak you will probably find a large number of IDLE connections with similar queries. The queries can help you track down the connection leak. Also in a connection leak, when the problem starts, it will continue until you restart things.

In the event where the issue is in fact too few connections available, then you will see lots of ACTIVE connections. In that case increase the number of connections in your pool. Also in this case, the problem will occur intermittently and then appear to go on its own all by itself.

Comments

[Andrzej Łach]

Looks like all of managed connections from the pool are in use? 30000 [ms] is quite huge delay. I would check if you release connection correctly after it is used, how long does it take average transaction on database and of course how many threads are trying to get connection concurently.

[John Ament]

and you're running in to this because you're hitting your app server configured limit of 150 connections. How are you interacting with these connections? plain JDBC? JPA?

[Benjamin]

I'm use Hibernate 3.6. 2 application use one database on postgres server. Every 2 day my application write "Unable to get managed connection for java:jboss/datasources/myDB 08:12:05,928 ERROR", generally 2 application "max" can use 250 connection to DB. I have 500 connection but i get error. As well as i create "statistics server" report via pgadmin and see what a lot of connection have status "Commit" and they stay status forever.

[Usha]

Is this issue resolved? If Yes, how?

[Wasif Tanveer]

i am facing same problem does anyone have any suggestion about this? thanks