Cannot get a connection, pool exhausted with Tomcat

hanming · **Joined:** Sun May 08, 2005 4:17 am **Posts:** 3

Hi,

I'm having the above mentioned error when using Hibernate with tomcat.
I understand that there is suggestion that I must close my session.
However, I'm still having the problem with the session.close in my finally block and I have tried to configure c3p0.

The weird thing for me is that the criteria.list() is still successful

I'd appreciate any advice on the problem and apologize if I missed out anything simple.

Thanks.

Han Ming

Hibernate version: 3.0.5

Code between sessionFactory.openSession() and session.close():

Code:

Object result = null;
SessionFactory sessionFactory = null;
Session session = null;
Transaction txn = null;

  try {
    sessionFactory = new Configuration().configure().buildSessionFactory();
    session = sessionFactory.openSession();

    txn = session.beginTransaction();

    Criteria criteria = session.createCriteria((Class) dbClass);   
  
    result = criteria.list();
    txn.commit();
    return result;
  } catch (Exception e) {
    if (txn != null) {
      txn.rollback();
    }
    e.printStackTrace();
  } finally {
    if (session != null && session.isOpen()) {
      session.close();
  }

  if (sessionFactory != null && !sessionFactory.isClosed()) {
    sessionFactory.close();
  }
}

Mapping :
<session-factory>
<property name="myeclipse.connection.profile">MySQL</property>
<property name="connection.url">jdbc:mysql://localhost/test</property>
<property name="connection.username">root</property>
<property name="connection.password"></property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property>
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">100</property>
<property name="show_sql">true</property>
</session-factory>

Full stack trace of any exception that occurs:
697500 DEBUG [http-8080-Processor24] org.hibernate.transaction.JDBCTransaction - committed JDBC Connection
697500 DEBUG [http-8080-Processor24] org.hibernate.jdbc.JDBCContext - after transaction completion
697500 DEBUG [http-8080-Processor24] org.hibernate.impl.SessionImpl - after transaction completion
697500 DEBUG [http-8080-Processor24] org.hibernate.impl.SessionImpl - closing session
697500 DEBUG [http-8080-Processor24] org.hibernate.jdbc.ConnectionManager - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
697500 DEBUG [http-8080-Processor24] org.hibernate.jdbc.JDBCContext - after transaction completion
697500 DEBUG [http-8080-Processor24] org.hibernate.impl.SessionImpl - after transaction completion
697500 INFO [http-8080-Processor24] org.hibernate.impl.SessionFactoryImpl - closing

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
at com.basicelement.struts.action.DatabaseAction.list(DatabaseAction.java:86)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at com.basicelement.web.filter.LoginFilter.doFilter(LoginFilter.java:65)
at com.basicelement.web.filter.LoginFilter.doFilter(LoginFilter.java:45)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:756)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
... 32 more

Name and version of the database you are using:MySQL 4

kdekooter · **Joined:** Mon Apr 12, 2004 6:33 pm **Posts:** 35

The stactrace suggests the default tomcat connection pool (DBCP) is used:

Quote:

org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)

So I suggest you configure this pool apporpirately. You do this in the context.xml of your webapp. Here is my setup:

Code:

   <Resource 
      auth="Container" 
      description="Xxxxxx" 
      name="jdbc/xxxxx" 
      type="javax.sql.DataSource"
        username="xxxx"
        password=""
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://xxxxx/xxxx"
        maxActive="20"
        maxIdle="1"
        minIdle="0"
        maxWait="1000"
        validationQuery="select * from validationquery where id = 1"
        testOnBorrow="true"
        testWhileIdle="true"
        timeBetweenEvictionRunsMillis="1000"
        poolPreparedStatements="true"
        maxOpenPreparedStatements="1000"
        removeAbandoned="true"
        removeAbandonedTimeout="300"
        logAbandoned="false"      
      />

hth[/code]