[DBCP] Anther lame "pool exhausted exception" question

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[DBCP] Anther lame "pool exhausted exception" question

Brian Cook

I have several apps written that use org.apache.commons.dbcp.  And I am
getting the  org.apache.commons.dbcp.SQLNestedException: Cannot get a
connection, pool exhausted exception.

When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
connections open at any given time.  Looking deeper it looks like new
connections are created most but not each time the Java Bean the uses
the data base is called.  So it seems pretty clear that I am failing to
close all of the connections in my app.  My problem is that I can not
find where that is.

My understanding is that as long as I close every ResultSet, and each
Connection object I should have my bases covered.  I have gone back
though the app repeatedly and verified that all the ResultSets,
Connections, and BasicDataSource objects are all being closed.

Due to an internal political problem I am not using JNDI for the
connections.  Instead I have a class file that is called when a
connection is need. After the object is used a close() method is called
and closes the Statement, Connection, ResultSet, BasicDataSource objects.

So my questions are ..

    1.  Is there something else I should be doing to return the
connections to the pool?
    2.  Does not using JNDI with org.apache.commons.dbcp cause problems
with closing the connection pool some how?


I have been though about 6 months of the archives for this list and did
not find any hints,  I went to look at the "examples" lnik at on the
Commons.DBCP page but the link to
http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
to be broken.  Googling has only just returned the usall advice of make
sure you are closing every connection.

I am open to any ideas or thoughts to what I am failing to due here.  
Details and the code are included below.


OS:                 Linux Red Hat 9.0
Container:       Tomcat 4.127
DBCP:            commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
JDK:               1.5
Database:        MySQL 4.1
JDBC Driver:  mysql-connector-java-3.1.7-bin.jar



public class DBConector {


<Omited Code/>


    public DBConector(String dataBaseName) {

      try{
        String DataBaseURL = "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
        "?user="+DataBaseUser+"&password="+DataBasePassword;

        mysqlCon = getPooledConection(DataBaseURL);
        sqlStatement = mysqlCon.createStatement();

      }
      catch(Exception s) {
        System.err.println(new java.util.Date()+" Error throw by" +
            " DBConector()\n"+s);
      }
    }


<Omited Code/>


    public ResultSet read(String SQL) {

      try{
         rs = sqlStatement.executeQuery( SQL );
       }                                   // Close try
       catch(Exception s) {
            System.err.println(new java.util.Date()+" Error throw by an
SQL " +
            "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
            "\nSQL Queary : "+SQL+"\n");
       }                                  // Close catch

        return(DataFromDB);
    }                                       // Close the read method



<Omited Code/>


   protected static Connection getPooledConection(String connectURI) {
        ds = new BasicDataSource();
        ds.setDriverClassName(DBDriver);
        ds.setUsername(DataBaseUser);
        ds.setPassword(DataBasePassword);
        ds.setUrl(connectURI);
        ds.setInitialSize(3);
        ds.setMaxActive(32);
        ds.setMaxIdle(8);
        ds.setMinIdle(3);
        Connection con = null;

      try {
        con = ds.getConnection();
      }
      catch(Exception a){System.out.println(new java.util.Date() +
        "PrintTimeDataBase6.getPooledConection() has thrown an
exception.\n"+a+
        "\n"+ a.getMessage()   );
      }
        return con;
    }                               // Close method


<Omited Code/>


   public void close() {
       try {  sqlStatement.close(); } catch(Exception a) {  }
       try {  mysqlCon.close();      } catch(Exception a) {  }
       try {  rs.close();   } catch(Exception a) {  }
       try {  ds.close();   } catch(Exception a) {  }
   }                                // Close method
}                                   // Close the PrintTimeWebServices Class

--
Brian Cook
Digital Services Analyst
Print Time Inc.
[hidden email]





---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Anther lame "pool exhausted exception" question

Craig McClanahan-2
The key to returning connections to the connection pool is to call
close() on the *connection* itself.  The most common way people get
themselves in trouble is to skip this somehow (perhaps because an
exception is thrown).

To avoid that sort of problem, I tend to use an idiom like this for
JDBC access code:

    DataSource ds = ...; // Acquire a reference from JNDI or wherever
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = ds.getConnection(); // Or whatever
        stmt = conn.prepareStatement("...");
        rs = stmt.executeQuery();
        ... process the results ...
    } catch (SQLException e) {
        ... process the exception ...
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            rs = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            conn = null;
        }
    }

Courtesy of the "finally" logic, I'm guaranteed to always execute my
cleanup code, no matter where an exception might get thrown.

You'll note that it can be pretty tedious to do all this stuff with
raw JDBC.  You might also want to look at using one of the SQL
frameworks that encapsulate this sort of stuff, but still give you the
guarantee that the connection will ultimately be returned to the pool.

Craig



On 7/14/05, Brian Cook <[hidden email]> wrote:

>
> I have several apps written that use org.apache.commons.dbcp.  And I am
> getting the  org.apache.commons.dbcp.SQLNestedException: Cannot get a
> connection, pool exhausted exception.
>
> When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
> connections open at any given time.  Looking deeper it looks like new
> connections are created most but not each time the Java Bean the uses
> the data base is called.  So it seems pretty clear that I am failing to
> close all of the connections in my app.  My problem is that I can not
> find where that is.
>
> My understanding is that as long as I close every ResultSet, and each
> Connection object I should have my bases covered.  I have gone back
> though the app repeatedly and verified that all the ResultSets,
> Connections, and BasicDataSource objects are all being closed.
>
> Due to an internal political problem I am not using JNDI for the
> connections.  Instead I have a class file that is called when a
> connection is need. After the object is used a close() method is called
> and closes the Statement, Connection, ResultSet, BasicDataSource objects.
>
> So my questions are ..
>
>     1.  Is there something else I should be doing to return the
> connections to the pool?
>     2.  Does not using JNDI with org.apache.commons.dbcp cause problems
> with closing the connection pool some how?
>
>
> I have been though about 6 months of the archives for this list and did
> not find any hints,  I went to look at the "examples" lnik at on the
> Commons.DBCP page but the link to
> http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
> to be broken.  Googling has only just returned the usall advice of make
> sure you are closing every connection.
>
> I am open to any ideas or thoughts to what I am failing to due here.
> Details and the code are included below.
>
>
> OS:                 Linux Red Hat 9.0
> Container:       Tomcat 4.127
> DBCP:            commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
> JDK:               1.5
> Database:        MySQL 4.1
> JDBC Driver:  mysql-connector-java-3.1.7-bin.jar
>
>
>
> public class DBConector {
>
>
> <Omited Code/>
>
>
>     public DBConector(String dataBaseName) {
>
>       try{
>         String DataBaseURL = "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
>         "?user="+DataBaseUser+"&password="+DataBasePassword;
>
>         mysqlCon = getPooledConection(DataBaseURL);
>         sqlStatement = mysqlCon.createStatement();
>
>       }
>       catch(Exception s) {
>         System.err.println(new java.util.Date()+" Error throw by" +
>             " DBConector()\n"+s);
>       }
>     }
>
>
> <Omited Code/>
>
>
>     public ResultSet read(String SQL) {
>
>       try{
>          rs = sqlStatement.executeQuery( SQL );
>        }                                   // Close try
>        catch(Exception s) {
>             System.err.println(new java.util.Date()+" Error throw by an
> SQL " +
>             "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
>             "\nSQL Queary : "+SQL+"\n");
>        }                                  // Close catch
>
>         return(DataFromDB);
>     }                                       // Close the read method
>
>
>
> <Omited Code/>
>
>
>    protected static Connection getPooledConection(String connectURI) {
>         ds = new BasicDataSource();
>         ds.setDriverClassName(DBDriver);
>         ds.setUsername(DataBaseUser);
>         ds.setPassword(DataBasePassword);
>         ds.setUrl(connectURI);
>         ds.setInitialSize(3);
>         ds.setMaxActive(32);
>         ds.setMaxIdle(8);
>         ds.setMinIdle(3);
>         Connection con = null;
>
>       try {
>         con = ds.getConnection();
>       }
>       catch(Exception a){System.out.println(new java.util.Date() +
>         "PrintTimeDataBase6.getPooledConection() has thrown an
> exception.\n"+a+
>         "\n"+ a.getMessage()   );
>       }
>         return con;
>     }                               // Close method
>
>
> <Omited Code/>
>
>
>    public void close() {
>        try {  sqlStatement.close(); } catch(Exception a) {  }
>        try {  mysqlCon.close();      } catch(Exception a) {  }
>        try {  rs.close();   } catch(Exception a) {  }
>        try {  ds.close();   } catch(Exception a) {  }
>    }                                // Close method
> }                                   // Close the PrintTimeWebServices Class
>
> --
> Brian Cook
> Digital Services Analyst
> Print Time Inc.
> [hidden email]
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: [DBCP] Anther lame "pool exhausted exception" question

Alfredo Ledezma Melendez


Hello.

Maybe you can use DbUtils class from dbutils project to code less in the finally
clause; methods closeQuietly and close makes this tedious stuff easier.

    DataSource ds = ...; // Acquire a reference from JNDI or wherever
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = ds.getConnection(); // Or whatever
        stmt = conn.prepareStatement("...");
        rs = stmt.executeQuery();
        ... process the results ...
    } catch (SQLException e) {
        ... process the exception ...
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

Even further, you can avoid ResultSet and Statement closing, using methods from
QueryRunner to execute or update a database. Look into documentation, it's
pretty useful.

I hope this help. Regards,
____________________________________________
Alfredo Ledezma Mel?ndez.
Gerencia de Sistemas CRM
Consultor Externo de Sistemas de Atenci?n a Clientes
RadioMovil DIPSA, S. A. de C. V.
Ej?rcito Nacional No. 488, Col. Anahuac, C.P. 11570
M?xico D.F.

-----Original Message-----
From: Craig McClanahan [mailto:[hidden email]]
Sent: Thursday, July 14, 2005 5:10 PM
To: Jakarta Commons Users List; [hidden email]
Subject: Re: [DBCP] Anther lame "pool exhausted exception" question

The key to returning connections to the connection pool is to call
close() on the *connection* itself.  The most common way people get
themselves in trouble is to skip this somehow (perhaps because an
exception is thrown).

To avoid that sort of problem, I tend to use an idiom like this for
JDBC access code:

    DataSource ds = ...; // Acquire a reference from JNDI or wherever
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = ds.getConnection(); // Or whatever
        stmt = conn.prepareStatement("...");
        rs = stmt.executeQuery();
        ... process the results ...
    } catch (SQLException e) {
        ... process the exception ...
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            rs = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            conn = null;
        }
    }

Courtesy of the "finally" logic, I'm guaranteed to always execute my
cleanup code, no matter where an exception might get thrown.

You'll note that it can be pretty tedious to do all this stuff with
raw JDBC.  You might also want to look at using one of the SQL
frameworks that encapsulate this sort of stuff, but still give you the
guarantee that the connection will ultimately be returned to the pool.

Craig



On 7/14/05, Brian Cook <[hidden email]> wrote:

>
> I have several apps written that use org.apache.commons.dbcp.  And I am
> getting the  org.apache.commons.dbcp.SQLNestedException: Cannot get a
> connection, pool exhausted exception.
>
> When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
> connections open at any given time.  Looking deeper it looks like new
> connections are created most but not each time the Java Bean the uses
> the data base is called.  So it seems pretty clear that I am failing to
> close all of the connections in my app.  My problem is that I can not
> find where that is.
>
> My understanding is that as long as I close every ResultSet, and each
> Connection object I should have my bases covered.  I have gone back
> though the app repeatedly and verified that all the ResultSets,
> Connections, and BasicDataSource objects are all being closed.
>
> Due to an internal political problem I am not using JNDI for the
> connections.  Instead I have a class file that is called when a
> connection is need. After the object is used a close() method is called
> and closes the Statement, Connection, ResultSet, BasicDataSource objects.
>
> So my questions are ..
>
>     1.  Is there something else I should be doing to return the
> connections to the pool?
>     2.  Does not using JNDI with org.apache.commons.dbcp cause problems
> with closing the connection pool some how?
>
>
> I have been though about 6 months of the archives for this list and did
> not find any hints,  I went to look at the "examples" lnik at on the
> Commons.DBCP page but the link to
> http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
> to be broken.  Googling has only just returned the usall advice of make
> sure you are closing every connection.
>
> I am open to any ideas or thoughts to what I am failing to due here.
> Details and the code are included below.
>
>
> OS:                 Linux Red Hat 9.0
> Container:       Tomcat 4.127
> DBCP:            commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
> JDK:               1.5
> Database:        MySQL 4.1
> JDBC Driver:  mysql-connector-java-3.1.7-bin.jar
>
>
>
> public class DBConector {
>
>
> <Omited Code/>
>
>
>     public DBConector(String dataBaseName) {
>
>       try{
>         String DataBaseURL = "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
>         "?user="+DataBaseUser+"&password="+DataBasePassword;
>
>         mysqlCon = getPooledConection(DataBaseURL);
>         sqlStatement = mysqlCon.createStatement();
>
>       }
>       catch(Exception s) {
>         System.err.println(new java.util.Date()+" Error throw by" +
>             " DBConector()\n"+s);
>       }
>     }
>
>
> <Omited Code/>
>
>
>     public ResultSet read(String SQL) {
>
>       try{
>          rs = sqlStatement.executeQuery( SQL );
>        }                                   // Close try
>        catch(Exception s) {
>             System.err.println(new java.util.Date()+" Error throw by an
> SQL " +
>             "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
>             "\nSQL Queary : "+SQL+"\n");
>        }                                  // Close catch
>
>         return(DataFromDB);
>     }                                       // Close the read method
>
>
>
> <Omited Code/>
>
>
>    protected static Connection getPooledConection(String connectURI) {
>         ds = new BasicDataSource();
>         ds.setDriverClassName(DBDriver);
>         ds.setUsername(DataBaseUser);
>         ds.setPassword(DataBasePassword);
>         ds.setUrl(connectURI);
>         ds.setInitialSize(3);
>         ds.setMaxActive(32);
>         ds.setMaxIdle(8);
>         ds.setMinIdle(3);
>         Connection con = null;
>
>       try {
>         con = ds.getConnection();
>       }
>       catch(Exception a){System.out.println(new java.util.Date() +
>         "PrintTimeDataBase6.getPooledConection() has thrown an
> exception.\n"+a+
>         "\n"+ a.getMessage()   );
>       }
>         return con;
>     }                               // Close method
>
>
> <Omited Code/>
>
>
>    public void close() {
>        try {  sqlStatement.close(); } catch(Exception a) {  }
>        try {  mysqlCon.close();      } catch(Exception a) {  }
>        try {  rs.close();   } catch(Exception a) {  }
>        try {  ds.close();   } catch(Exception a) {  }
>    }                                // Close method
> }                                   // Close the PrintTimeWebServices Class
>
> --
> Brian Cook
> Digital Services Analyst
> Print Time Inc.
> [hidden email]
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos.

This message is for the sole use of the person or entity to whom it is being sent.  Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law.  If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited.  If you received this document by mistake please notify  immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Anther lame "pool exhausted exception" question

Dirk Verbeeck
In reply to this post by Brian Cook
Hi Brian,

Examples can be found here:
http://svn.apache.org/repos/asf/jakarta/commons/proper/dbcp/trunk/doc/

If you are creating a BasicDataSource yourself then you need to keep it
in a static variable somewhere. You create it once and only close the
datasource when your application is shutting down. The BasicDataSource
contains the pool and you only want one of these around for each
database(scheme) you want to connect to.

Your program then uses ds.getConnection() / conn.close() to get/return
connections from/to the pool.
Use the pattern Craig gave you to make sure you always close the
rs/stmt/conn.

So key point here is to only create one datasource (and store it in a
static somewhere) and not to close the datasource after each call.

Cheers
Dirk


Brian Cook wrote:

>
> I have several apps written that use org.apache.commons.dbcp.  And I am
> getting the  org.apache.commons.dbcp.SQLNestedException: Cannot get a
> connection, pool exhausted exception.
>
> When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
> connections open at any given time.  Looking deeper it looks like new
> connections are created most but not each time the Java Bean the uses
> the data base is called.  So it seems pretty clear that I am failing to
> close all of the connections in my app.  My problem is that I can not
> find where that is.
>
> My understanding is that as long as I close every ResultSet, and each
> Connection object I should have my bases covered.  I have gone back
> though the app repeatedly and verified that all the ResultSets,
> Connections, and BasicDataSource objects are all being closed.
>
> Due to an internal political problem I am not using JNDI for the
> connections.  Instead I have a class file that is called when a
> connection is need. After the object is used a close() method is called
> and closes the Statement, Connection, ResultSet, BasicDataSource objects.
>
> So my questions are ..
>
>    1.  Is there something else I should be doing to return the
> connections to the pool?
>    2.  Does not using JNDI with org.apache.commons.dbcp cause problems
> with closing the connection pool some how?
>
>
> I have been though about 6 months of the archives for this list and did
> not find any hints,  I went to look at the "examples" lnik at on the
> Commons.DBCP page but the link to
> http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
> to be broken.  Googling has only just returned the usall advice of make
> sure you are closing every connection.
>
> I am open to any ideas or thoughts to what I am failing to due here.  
> Details and the code are included below.
>
>
> OS:                 Linux Red Hat 9.0
> Container:       Tomcat 4.127
> DBCP:            commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
> JDK:               1.5
> Database:        MySQL 4.1
> JDBC Driver:  mysql-connector-java-3.1.7-bin.jar
>
>
>
> public class DBConector {
>
>
> <Omited Code/>
>
>
>    public DBConector(String dataBaseName) {
>
>      try{
>        String DataBaseURL = "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
>        "?user="+DataBaseUser+"&password="+DataBasePassword;
>
>        mysqlCon = getPooledConection(DataBaseURL);
>        sqlStatement = mysqlCon.createStatement();
>
>      }
>      catch(Exception s) {
>        System.err.println(new java.util.Date()+" Error throw by" +
>            " DBConector()\n"+s);
>      }
>    }
>
>
> <Omited Code/>
>
>
>    public ResultSet read(String SQL) {
>
>      try{
>         rs = sqlStatement.executeQuery( SQL );
>       }                                   // Close try
>       catch(Exception s) {
>            System.err.println(new java.util.Date()+" Error throw by an
> SQL " +
>            "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
>            "\nSQL Queary : "+SQL+"\n");
>       }                                  // Close catch
>
>        return(DataFromDB);
>    }                                       // Close the read method
>
>
>
> <Omited Code/>
>
>
>   protected static Connection getPooledConection(String connectURI) {
>        ds = new BasicDataSource();
>        ds.setDriverClassName(DBDriver);
>        ds.setUsername(DataBaseUser);
>        ds.setPassword(DataBasePassword);
>        ds.setUrl(connectURI);
>        ds.setInitialSize(3);
>        ds.setMaxActive(32);
>        ds.setMaxIdle(8);
>        ds.setMinIdle(3);
>        Connection con = null;
>
>      try {
>        con = ds.getConnection();
>      }
>      catch(Exception a){System.out.println(new java.util.Date() +
>        "PrintTimeDataBase6.getPooledConection() has thrown an
> exception.\n"+a+
>        "\n"+ a.getMessage()   );
>      }
>        return con;
>    }                               // Close method
>
>
> <Omited Code/>
>
>
>   public void close() {
>       try {  sqlStatement.close(); } catch(Exception a) {  }
>       try {  mysqlCon.close();      } catch(Exception a) {  }
>       try {  rs.close();   } catch(Exception a) {  }
>       try {  ds.close();   } catch(Exception a) {  }
>   }                                // Close method
> }                                   // Close the PrintTimeWebServices Class
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Anther lame "pool exhausted exception" question

Brian Cook

Ding Ding Ding.  We have a winner.  That is exactly what I am doing
wrong.  A new BasicDataSource is created every time a Java Bean is
called.  I guess that also explains why I am seeing 60 - 100 active
connections in netstat when the maxActive value was set to 32.

I will look into using JNDI and or have a servlet that runs continually
in the background.

Thanks to everyone who responded the ideas provided are great.  You guys
  rock!



Dirk Verbeeck wrote:

> Hi Brian,
>
> Examples can be found here:
> http://svn.apache.org/repos/asf/jakarta/commons/proper/dbcp/trunk/doc/
>
> If you are creating a BasicDataSource yourself then you need to keep it
> in a static variable somewhere. You create it once and only close the
> datasource when your application is shutting down. The BasicDataSource
> contains the pool and you only want one of these around for each
> database(scheme) you want to connect to.
>
> Your program then uses ds.getConnection() / conn.close() to get/return
> connections from/to the pool.
> Use the pattern Craig gave you to make sure you always close the
> rs/stmt/conn.
>
> So key point here is to only create one datasource (and store it in a
> static somewhere) and not to close the datasource after each call.
>
> Cheers
> Dirk
>
>
> Brian Cook wrote:
>
>>
>> I have several apps written that use org.apache.commons.dbcp.  And I
>> am getting the  org.apache.commons.dbcp.SQLNestedException: Cannot get
>> a connection, pool exhausted exception.
>>
>> When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
>> connections open at any given time.  Looking deeper it looks like new
>> connections are created most but not each time the Java Bean the uses
>> the data base is called.  So it seems pretty clear that I am failing
>> to close all of the connections in my app.  My problem is that I can
>> not find where that is.
>>
>> My understanding is that as long as I close every ResultSet, and each
>> Connection object I should have my bases covered.  I have gone back
>> though the app repeatedly and verified that all the ResultSets,
>> Connections, and BasicDataSource objects are all being closed.
>>
>> Due to an internal political problem I am not using JNDI for the
>> connections.  Instead I have a class file that is called when a
>> connection is need. After the object is used a close() method is
>> called and closes the Statement, Connection, ResultSet,
>> BasicDataSource objects.
>>
>> So my questions are ..
>>
>>    1.  Is there something else I should be doing to return the
>> connections to the pool?
>>    2.  Does not using JNDI with org.apache.commons.dbcp cause problems
>> with closing the connection pool some how?
>>
>>
>> I have been though about 6 months of the archives for this list and
>> did not find any hints,  I went to look at the "examples" lnik at on
>> the Commons.DBCP page but the link to
>> http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
>> to be broken.  Googling has only just returned the usall advice of
>> make sure you are closing every connection.
>>
>> I am open to any ideas or thoughts to what I am failing to due here.  
>> Details and the code are included below.
>>
>>
>> OS:                 Linux Red Hat 9.0
>> Container:       Tomcat 4.127
>> DBCP:            commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
>> JDK:               1.5
>> Database:        MySQL 4.1
>> JDBC Driver:  mysql-connector-java-3.1.7-bin.jar
>>
>>
>>
>> public class DBConector {
>>
>>
>> <Omited Code/>
>>
>>
>>    public DBConector(String dataBaseName) {
>>
>>      try{
>>        String DataBaseURL =
>> "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
>>        "?user="+DataBaseUser+"&password="+DataBasePassword;
>>
>>        mysqlCon = getPooledConection(DataBaseURL);
>>        sqlStatement = mysqlCon.createStatement();
>>
>>      }
>>      catch(Exception s) {
>>        System.err.println(new java.util.Date()+" Error throw by" +
>>            " DBConector()\n"+s);
>>      }
>>    }
>>
>>
>> <Omited Code/>
>>
>>
>>    public ResultSet read(String SQL) {
>>
>>      try{
>>         rs = sqlStatement.executeQuery( SQL );
>>       }                                   // Close try
>>       catch(Exception s) {
>>            System.err.println(new java.util.Date()+" Error throw by an
>> SQL " +
>>            "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
>>            "\nSQL Queary : "+SQL+"\n");
>>       }                                  // Close catch
>>
>>        return(DataFromDB);
>>    }                                       // Close the read method
>>
>>
>>
>> <Omited Code/>
>>
>>
>>   protected static Connection getPooledConection(String connectURI) {
>>        ds = new BasicDataSource();
>>        ds.setDriverClassName(DBDriver);
>>        ds.setUsername(DataBaseUser);
>>        ds.setPassword(DataBasePassword);
>>        ds.setUrl(connectURI);
>>        ds.setInitialSize(3);
>>        ds.setMaxActive(32);
>>        ds.setMaxIdle(8);
>>        ds.setMinIdle(3);
>>        Connection con = null;
>>
>>      try {
>>        con = ds.getConnection();
>>      }
>>      catch(Exception a){System.out.println(new java.util.Date() +
>>        "PrintTimeDataBase6.getPooledConection() has thrown an
>> exception.\n"+a+
>>        "\n"+ a.getMessage()   );
>>      }
>>        return con;
>>    }                               // Close method
>>
>>
>> <Omited Code/>
>>
>>
>>   public void close() {
>>       try {  sqlStatement.close(); } catch(Exception a) {  }
>>       try {  mysqlCon.close();      } catch(Exception a) {  }
>>       try {  rs.close();   } catch(Exception a) {  }
>>       try {  ds.close();   } catch(Exception a) {  }
>>   }                                // Close method
>> }                                   // Close the PrintTimeWebServices
>> Class
>>
>>
>> ------------------------------------------------------------------------
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Brian Cook
Digital Services Analyst
Print Time Inc.
[hidden email]
913.345.8900


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]