java.sql.SQLException: Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.

Question

Encountering this error with our J2EE application. How to know how much connection the application is currently using and what should be the optimal connection pool settings for a heavily traffic applicaiton

Answer 1

How to know how much connection the application is currently using

You don't give enough information to answer that. Most appservers will have some sort of JMX reporting on things like this. Alternatively, depending on the database, you could find the number of currently open connections.

what should be the optimal connection pool settings for a heavily traffic applicaiton

Higher than what you've got?

The above of course assumes that you're not mishandling connections. By that I mean if you're using them directly in code you should always use this idiom:

Connection conn = null;
try {
  conn = ... ; // get connection
  // do stuff
} finally {
  if (conn != null) try { conn.close(); } catch (Exception e) { }
}

If you're not released connections back to the pool and are waiting for the garbage collector to clean them up and release them you're going to use way more connections that you actually need.

Answer 2

Are you sure you are closing everything you need to? Take a loog here: http://www.java2s.com/Code/JavaAPI/javax.sql/ConnectionPoolDataSourcegetPooledConnection.htm

Make sure that the close goes in the finally block. You will see this code in the link:

finally 
{
   closeAll(resultSet, statement, connection);
}

I helped someone else find a simlar issue (theirs was a memory issue instead... but if the process had gomne on longer it would have had this result) where they had not closed the result set or the connection.

Answer 3

I think you may need to inspect ask yourself some questions, here are some to think about:

A. Are you using youre connections correctly, are you closing them and returning them to the pool after usage?

B. Are you using long running transactions, e.g. "conversations" with users? Can they be left hanging if the user terminates his usage of the application?

C. Have you designed your data access to fit your application? E.g. are you using caching techniques in areas where you expect repeated reads frequently?

D. Are your connection pool big enough? 5 years ago I had a application with 250 simultaneous connections towards a Oracle database, a lot more than what you typically find out of the box. running the application on say 50 didn't work.

To give more detailed answer, you need to provide more info on the app.

Good luck!

Answer 4

First thing to check is whether you have resource leaks. Try surveilling with jconsole to see how your application behaves and if there is anything that springs in the eye.

Then the actual jdbc pool connection is inherently JEE container specific so you need to give more information.