No ManagedConnections available Error

[majohnst]

I use EJB3 to connect to Postgres 8.1. At random times, I get a database connection error:

No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ))

From what I have read, this means that the database connection pool is messing up some how. In my datasource definition, I have included:

 <local-tx-datasource>
 <jndi-name>MyDS</jndi-name>
 <connection-url>jdbc:postgresql://cherry/pgacms</connection-url>
 <driver-class>org.postgresql.Driver</driver-class>
 <user-name>...</user-name>
 <password>...</password>
 <min-pool-size>5</min-pool-size>
 <max-pool-size>30</max-pool-size>
 <new-connection-sql>SELECT version()</new-connection-sql>
 <check-valid-connection-sql>SELECT version()</check-valid-connection-sql>

 </local-tx-datasource>

I have checked, and the max number of connections defined for all my datasources is less than the maximum connections that Postgres allows. From what I have read about this error, most people say that the database connection is not being closed correctly in the java code.

So my question, since I am using EJB3 for my database connection, do I have to do anything to explicitly close the connection?

[weston.price]

From what I have read, this means that the database connection pool is messing up some how.

Actually, it simply means that a connection could not be allocated within the specific blocking timeout (30 seconds). There are usually a few possiblities:

1) You are not closing connections
2) You have long running transactions to the DB. As a result, the pool is exhausted and a connection cannot be handed out.
3) A mix of the above two conditions.

If your using EJB3/JPA you do not have to explicitly close the connection as JPA does this for you. The next place to look would be at your DB transactions and determine what is taking so long to complete that a connection is tied up for that long. You can always increase the blocking timeout in the *-ds.xml file by adding the following element

<blocking-timeout-millis>value</blocking-timeout-millis>

As mentioned above 30 seconds is the default and this is *usually* a good starting point, most the time it can actually be decreased. One suggestion is to look at your code to see if you are doing long running DB operations that could be broken up into smaller transactions.

[weston.price]

The other *obvious* solution would be to increase the max number of connections in the pool. Of course, you would want to verify this against suitable load testing to determine the 'blreaking point'.

[majohnst]

I don't think it is my db queries. All of them are really short and quick.

In my servlet, when a request comes in, I start a JTA transaction, do all the servlet work, then either commit or rollback the JTA transaction. When I do the commit or rollback, that should close the DB connection and return it to the pool? Right?

[weston.price]

Yes, if using JPA the connection will be closed for you. To clarify, you are using EJB3 to do the persistence correct?

[majohnst]

Doing some simple stress testing, I really think my database connection isn't being closed. I am using EJB3. I'm not sure what version, an early RC version probably and jboss 4.0.3.

My servlet code is:

 UserTransaction ut = null;
 try {
 // begin transaction
 InitialContext ctx = new InitialContext();

 ut = (UserTransaction)ctx.lookup("java:comp/UserTransaction");
 ut.begin();

 ... do webpage code

 ut.commit();

 catch (Exception e) {
 log.error("error in servlet", e);
 try {
 ut.rollback();
 }
 catch (Exception erb) {
 log.error("could not rollback transaction", erb);
 }

 }
 finally {

 ut = null;
 }

Postrgres is setup to accept 20 connections. My datasource is setup for 10 connections max. I am using JMeter to do the stress testing. I setup JMeter to run 10 threads concurrently and load a series of webpages. Soon after I start the test, I get the error:

10:59:45,341 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool:232] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.postgresql.util.PSQLException: FATAL: sorry, too many clients already)
 at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:164)
 at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:519)
 at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:208)
 at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:566)
 at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:410)
 at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:342)
 at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:462)