Announcement Announcement Module
Collapse
No announcement yet.
Connection pool getting exhausted. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Connection pool getting exhausted.

    Hi ,

    I am using Hibernate 3.0 with Spring 1.2.1 . I am getting connection pool exhausted exception . I am using Spring's HibernateDaoSupport class. To get data from database i am using HibernateTemplate.find() method. As per my knowledge this method takes care for session life cycle. I need not to worry about that .Now i am facing one major problem.
    When i try to fetch huge data , say 2000 records result [To get these records i have to take join on several table. I am using HQL for the query] . So at that time i am getting following exception . Here is the stack trace


    15:07:39,890 INFO ConnectionProviderFactory:53 - Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider
    15:07:39,953 DEBUG JDBCExceptionReporter:49 - SQL Exception
    org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00020: maximum number of processes (200) exceeded
    )
    at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:855)
    at org.apache.commons.dbcp.BasicDataSource.getConnect ion(BasicDataSource.java:540)
    at org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider.getConnection(LocalDataSourceCo nnectionProvider.java:80)
    at org.hibernate.cfg.SettingsFactory.buildSettings(Se ttingsFactory.java:118)
    at org.hibernate.cfg.Configuration.buildSettings(Conf iguration.java:1497)
    at org.hibernate.cfg.Configuration.buildSessionFactor y(Configuration.java:1041)
    at org.springframework.orm.hibernate3.LocalSessionFac toryBean.newSessionFactory(LocalSessionFactoryBean .java:746)
    at org.springframework.orm.hibernate3.LocalSessionFac toryBean.afterPropertiesSet(LocalSessionFactoryBea n.java:673)
    at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:962)
    at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:354)
    at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:223)
    at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:147)
    at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:271)
    at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:312)
    at org.springframework.context.support.ClassPathXmlAp plicationContext.<init>(ClassPathXmlApplicationCon text.java:80)
    at org.springframework.context.support.ClassPathXmlAp plicationContext.<init>(ClassPathXmlApplicationCon text.java:65)
    at qc.commons.service.impl.SpringServiceLocator.<init >(SpringServiceLocator.java:47)
    at qc.qbid.invoice.model.service.impl.InvoiceManagerI mpl.parserRead(InvoiceManagerImpl.java:1731)
    at qc.qbid.invoice.model.service.impl.InvoiceManagerI mpl.parserCSV(InvoiceManagerImpl.java:1646)
    at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.onCl ickImport(InvoiceDetailsMBean.java:1017)
    at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.uplo ad(InvoiceDetailsMBean.java:982)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:324)
    at org.apache.myfaces.el.MethodBindingImpl.invoke(Met hodBindingImpl.java:138)
    at org.apache.myfaces.application.ActionListenerImpl. processAction(ActionListenerImpl.java:62)
    at javax.faces.component.UICommand.broadcast(UIComman d.java:106)
    at javax.faces.component.UIViewRoot._broadcastForPhas e(UIViewRoot.java:110)
    at javax.faces.component.UIViewRoot.processApplicatio n(UIViewRoot.java:184)
    at org.apache.myfaces.lifecycle.LifecycleImpl.invokeA pplication(LifecycleImpl.java:259)
    at org.apache.myfaces.lifecycle.LifecycleImpl.execute (LifecycleImpl.java:90)
    at javax.faces.webapp.FacesServlet.service(FacesServl et.java:109)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:237)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:157)
    at qc.commons.auth.ChannelsAuthenticationFilter.doFil ter(ChannelsAuthenticationFilter.java:186)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:186)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:157)
    at org.apache.myfaces.component.html.util.ExtensionsF ilter.doFilter(ExtensionsFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:186)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:157)
    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:214)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.StandardContextValve.invo keInternal(StandardContextValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:152)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:137)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:118)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:102)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:929)
    at org.apache.coyote.tomcat5.CoyoteAdapter.service(Co yoteAdapter.java:160)
    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:799)
    at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.processConnection(Http11Protocol.jav a:705)
    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(P oolTcpEndpoint.java:577)
    at org.apache.tomcat.util.threads.ThreadPool$ControlR unnable.run(ThreadPool.java:683)
    at java.lang.Thread.run(Thread.java:536)
    Caused by: java.sql.SQLException: ORA-00020: maximum number of processes (200) exceeded

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
    at oracle.jdbc.ttc7.O3log.receive1st(O3log.java:407)
    at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.j ava:259)
    at oracle.jdbc.driver.OracleConnection.<init>(OracleC onnection.java:346)
    at oracle.jdbc.driver.OracleDriver.getConnectionInsta nce(OracleDriver.java:468)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriv er.java:314)
    at org.apache.commons.dbcp.DriverConnectionFactory.cr eateConnection(DriverConnectionFactory.java:37)
    at org.apache.commons.dbcp.PoolableConnectionFactory. makeObject(PoolableConnectionFactory.java:290)
    at org.apache.commons.dbcp.BasicDataSource.validateCo nnectionFactory(BasicDataSource.java:877)
    at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:851)
    ... 62 more
    15:07:40,359 WARN JDBCExceptionReporter:57 - SQL Error: 0, SQLState: null
    15:07:40,359 ERROR JDBCExceptionReporter:58 - Cannot create PoolableConnectionFactory (ORA-00020: maximum number of processes (200) exceeded
    )
    15:07:40,359 WARN SettingsFactory:142 - Could not obtain connection metadata
    org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00020: maximum number of processes (200) exceeded
    )
    at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:855)
    at org.apache.commons.dbcp.BasicDataSource.getConnect ion(BasicDataSource.java:540)
    at org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider.getConnection(LocalDataSourceCo nnectionProvider.java:80)
    at org.hibernate.cfg.SettingsFactory.buildSettings(Se ttingsFactory.java:118)
    at org.hibernate.cfg.Configuration.buildSettings(Conf iguration.java:1497)
    at org.hibernate.cfg.Configuration.buildSessionFactor y(Configuration.java:1041)
    at org.springframework.orm.hibernate3.LocalSessionFac toryBean.newSessionFactory(LocalSessionFactoryBean .java:746)
    at org.springframework.orm.hibernate3.LocalSessionFac toryBean.afterPropertiesSet(LocalSessionFactoryBea n.java:673)
    at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:962)
    at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:354)
    at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:223)
    at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:147)
    at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:271)
    at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:312)
    at org.springframework.context.support.ClassPathXmlAp plicationContext.<init>(ClassPathXmlApplicationCon text.java:80)
    at org.springframework.context.support.ClassPathXmlAp plicationContext.<init>(ClassPathXmlApplicationCon text.java:65)
    at qc.commons.service.impl.SpringServiceLocator.<init >(SpringServiceLocator.java:47)
    at qc.qbid.invoice.model.service.impl.InvoiceManagerI mpl.parserRead(InvoiceManagerImpl.java:1731)
    at qc.qbid.invoice.model.service.impl.InvoiceManagerI mpl.parserCSV(InvoiceManagerImpl.java:1646)
    at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.onCl ickImport(InvoiceDetailsMBean.java:1017)
    at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.uplo ad(InvoiceDetailsMBean.java:982)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:324)
    at org.apache.myfaces.el.MethodBindingImpl.invoke(Met hodBindingImpl.java:138)
    at org.apache.myfaces.application.ActionListenerImpl. processAction(ActionListenerImpl.java:62)
    at javax.faces.component.UICommand.broadcast(UIComman d.java:106)
    at javax.faces.component.UIViewRoot._broadcastForPhas e(UIViewRoot.java:110)
    at javax.faces.component.UIViewRoot.processApplicatio n(UIViewRoot.java:184)
    at org.apache.myfaces.lifecycle.LifecycleImpl.invokeA pplication(LifecycleImpl.java:259)
    at org.apache.myfaces.lifecycle.LifecycleImpl.execute (LifecycleImpl.java:90)
    at javax.faces.webapp.FacesServlet.service(FacesServl et.java:109)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:237)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:157)
    at qc.commons.auth.ChannelsAuthenticationFilter.doFil ter(ChannelsAuthenticationFilter.java:186)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:186)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:157)
    at org.apache.myfaces.component.html.util.ExtensionsF ilter.doFilter(ExtensionsFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:186)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:157)
    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:214)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.StandardContextValve.invo keInternal(StandardContextValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:152)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:137)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:118)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:102)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
    at org.apache.catalina.core.StandardValveContext.invo keNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:520)
    at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:929)
    at org.apache.coyote.tomcat5.CoyoteAdapter.service(Co yoteAdapter.java:160)
    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:799)
    at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.processConnection(Http11Protocol.jav a:705)
    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(P oolTcpEndpoint.java:577)
    at org.apache.tomcat.util.threads.ThreadPool$ControlR unnable.run(ThreadPool.java:683)
    at java.lang.Thread.run(Thread.java:536)
    Caused by: java.sql.SQLException: ORA-00020: maximum number of processes (200) exceeded

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
    at oracle.jdbc.ttc7.O3log.receive1st(O3log.java:407)
    at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.j ava:259)
    at oracle.jdbc.driver.OracleConnection.<init>(OracleC onnection.java:346)
    at oracle.jdbc.driver.OracleDriver.getConnectionInsta nce(OracleDriver.java:468)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriv er.java:314)
    at org.apache.commons.dbcp.DriverConnectionFactory.cr eateConnection(DriverConnectionFactory.java:37)
    at org.apache.commons.dbcp.PoolableConnectionFactory. makeObject(PoolableConnectionFactory.java:290)
    at org.apache.commons.dbcp.BasicDataSource.validateCo nnectionFactory(BasicDataSource.java:877)
    at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:851)
    ... 62 more



    Does any buddy have idea , why is this happening.

    Thanks in advance

    Vab

  • #2
    Spring indeed takes care of the problem (delegates to the connection pooling). Can you post your configuration, especially the DBCP settings and the code with the hibernate template?
    I haven't used DBCP but you it might badly configured so it doesn't do proper pooling.
    Try also to use a different connection pooling like c3p0 (comes w/ Spring distribution).

    Comment


    • #3
      Originally posted by costin
      Spring indeed takes care of the problem (delegates to the connection pooling). Can you post your configuration, especially the DBCP settings and the code with the hibernate template?
      I haven't used DBCP but you it might badly configured so it doesn't do proper pooling.
      Try also to use a different connection pooling like c3p0 (comes w/ Spring distribution).

      Hi Costin,

      I have not added explicitly any configuration for DBCP connection pooling. My config file is like this


      <beans>
      <bean id="dataSource"
      class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
      <property name="driverClassName">
      <value>oracle.jdbc.driver.OracleDriver</value>
      </property>
      <property name="url">
      <value>jdbc:oracle:thin:@160.126.10.77:1521BQBID</value>
      </property>
      <property name="username">
      <value>user</value>
      </property>
      <property name="password">
      <value>password</value>
      </property>
      </bean>

      <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
      <property name="dataSource">
      <ref bean="dataSource"/>
      </property>
      <property name="hibernateProperties">
      <props>
      <prop key="hibernate.dialect">org.hibernate.dialect.Orac leDialect</prop>
      <prop key="hibernate.show_sql">true</prop>
      <prop key="hibernate.max_fetch_depth">3</prop>
      </props>
      </property>
      </bean>
      <!-- Spring Data Access Exception Translator Defintion -->
      <bean id="jdbcExceptionTranslator" class="org.springframework.jdbc.support.SQLErrorCo deSQLExceptionTranslator">
      <property name="dataSource"><ref bean="dataSource"/></property>
      </bean>


      <!-- Hibernate Template Defintion -->
      <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.Hibernat eTemplate">
      <property name="sessionFactory"><ref bean="sessionFactory"/></property>
      <property name="jdbcExceptionTranslator"><ref bean="jdbcExceptionTranslator"/></property>
      </bean>
      <bean id="INVOICE_DAO" class="qc.qbid.invoice.model.dao.impl.InvoiceDAOHi bImpl" singleton="false">
      <property name="hibernateTemplate"><ref bean="hibernateTemplate"/></property>
      </bean>
      </beans>


      while code snippet is like this

      final List invoicePaymentChargeToList = this.getHibernateTemplate()
      .find("select invoicePaymentChargeTo " +
      "from InvoicePaymentChargeTo invoicePaymentChargeTo, InvoicePayment invoicePayment , " +
      "InvoiceBatch invoiceBatch " +
      "where invoicePayment.invoiceBatch.invoiceBatchId = " +
      " invoiceBatch.invoiceBatchId " +
      "and invoicePayment.invoicePaymentId = " +
      "invoicePaymentChargeTo.invoicePayment.invoicePaym entId " +
      "and invoiceBatch.invoiceBatchId = " + selectedBatch);



      am i doing any wrong thing ?

      Thanks
      Vab

      Comment


      • #4
        Is it possible that the database server you are connecting to is already close to being maxed out in terms of connections? If there already are 198 connections and you try to add 5 new ones for the pool then you would see this error. See if you can monitor the number of connections before and during startup of your app.

        Comment

        Working...
        X