Connection Pooling - exhausted pool problem

[George Stoianov]

Hello everyone,

I am having a continuous problem with a connection pool namely at one point or another it runs out of connections.

Initially I had the pool initiated from within my own code but after reading one of the articles in java ranch journal I set it up at the container and also changed my code to close the connections as soon as they are not needed.

I am still having problems even thought I have adjusted upward all parameters, maxActive is 800 and maxIdle is 35 to try to keep as many connections as need available but I am still having issues. I am using Tomcat 4.0 the pool is configured as a JNDI resource at the container level and is for MySQL.

Here is the message:
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)

I was wondering if you may have some ideas, namely at this point I am looking for a way to monitor the number of connections over time so that I can put this behaveour in context with the traffic on the site, that is if you do not have a better idea.

Thank you.
george

[Roger Chung-Wee]

Please post your code which gets and closes the connections.

[stu derby]

This suggests that your code is "leaking" connections, checking them out and sometimes not returning them to the pool.

One of the classic ways of doing that is to not have connection close in a finally block; whenever an exception occurs a connection gets leaked.

Another less common way of miscoding is to sometimes allocate a new connection using the same variable as an already open connection.

There's plenty of other ways too.

[George Stoianov]

This is my code to get the datasource for the pool:

 
    public void init(FilterConfig config) throws ServletException {
this.config = config;
 
        try {
 
            //Create a datasource for pooled connections.
            Context initCtx = new InitialContext();
            Context envCtx = (Context) initCtx.lookup("java:comp/env");
        
            datasource = (DataSource) envCtx.lookup("kms-db-pool");
 
        } catch (Exception e) {
 
            e.printStackTrace();
            throw new ServletException(e.getMessage());
        }

}

and here is my method to get a connection

    private Connection getConnection() throws SQLException {
    
        return datasource.getConnection();
    }

and this is how I retrieve it:

 
try {
         con = getConnection();
   FileDAO fileDAO = new FileDAO(con);
 

and this is how I close it:

   
 
    con.close();
 
} catch (SQLException sqle) { sqle.printStackTrace(); }
 
 

Reading one of the previous posts I think my problem may have to do with leaking as I am not using a finally block to close.

I am not getting sql problem in the logs prior to the close line, but I will make the change and see how it goes.

Thank you very much.
George

[Roger Chung-Wee]

Also, make sure that your con variable is local and that you always close each Connection. Do not leave Connection objects hanging around like this.

con = getConnection();
// do some stuff
con = getConnection();
// do more stuff stuff
con.close(); // only 1 Connection closed!

[R. M. Menon]

Also, make sure that your con variable is local and that you always close each Connection. Do not leave Connection objects hanging around like this.

And close the connection in a "finally" clause once you are done.

[George Stoianov]

My problem also stems from the fact that I did not start working with a pool by reading the best practices document and implementing them in my code...yeah (now) I know ... so now I am stuck with a bunch of code I am reworking to make it more modular and make sure all my db connection code is localized into one central place allowing me to track everything more closely and avoid problems in the future.

I am also wondering if there is a possibility, already in the Jakarta DBCP project, which I can use to enable logging or something on my test machine to get some stats generated so that I can see what is causing this behaivour. One error I encountered is a "connection is closed" sqlexception and then the page is displayed even though if a check fails it should not...

I think if I told you what I am trying to do I may get booed for my choice but then again I will probably get valuable advise as I did so far .

I have a Filter for my web application which has the responsibility of inspecting all requests and identifying whether certain content is protected and requires authentication, so if a check fails it should just present a log in screen, very common functionality on many web sites. I am not so sure if my implementation is a good choice though and whether this db heavy thing should be designed in a different manner...

Thank you.
george

[George Stoianov]

Taking a look again after reading your comments again. I had to first fix the finally problem, I was not using it and also my connection was class local not method local which may explain some of the connection closed errors.... here is a place where I should read more about servlet filters and guaranteed and not-guaranteed behaviour and multi-threading...

thank you very much for your input.
george