commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian Cook <bc...@printtime.com>
Subject Re: [DBCP] Anther lame "pool exhausted exception" question
Date Fri, 15 Jul 2005 13:32:28 GMT

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: commons-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: commons-user-help@jakarta.apache.org
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
> 
> 


-- 
Brian Cook
Digital Services Analyst
Print Time Inc.
bcook@printtime.com
913.345.8900


Mime
View raw message