2 Replies Latest reply on Apr 6, 2009 10:24 AM by Guillaume Jeudy

    jboss connectionpooling by subject, connection closed/no rec

    Guillaume Jeudy Master

      Hi,

      I'm using jboss 4.2.2.GA and I have a particular connection pooling setup:

      <local-tx-datasource>
       <!-- This datasource should only be used by the UI component
       due to its peculiar connection pooling settings preventing use of more than 1 connection per subject.
       -->
       <jndi-name>ReferenceDataManagerDS</jndi-name>
       <connection-url>${rdmds.connection.url}</connection-url>
       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
       <security-domain>OracleDbRealm</security-domain>
       <max-pool-size>1</max-pool-size>
       <idle-timeout-minutes>999999999999</idle-timeout-minutes>
       <new-connection-sql>alter session set current_schema = ${rdmds.owner.username}</new-connection-sql>
       <exception-sorter-class-name>
       org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
       </exception-sorter-class-name>
       <metadata>
       <type-mapping>Oracle9i</type-mapping>
       </metadata>
       </local-tx-datasource>


      I use the connection pooling per subject config. The maximum pool size is set to 1 for reasons I won't explain here. The web application works fine as long as I don't hold up more than 1 connection per subject in a request cycle (but thats fine for the needs of my project).

      However, if the connection is reset by peer, the connection is not automatically renewed and my application start throwing SQLException, Connection closed. Before I start investigating further, is there anything obvious that I missed that would help me avoid that ?
      I'm considering adding a check-valid-connection-sql config something like:
      SELECT * FROM DUAL

      Even if that worked wouldn't that add unnecessary overhead? I mean shouldn't jboss find such unrecoverable exceptions by itself and let go the physical connection handle and recreate a new one ?

      Here's the stack i'm getting:

      javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
       at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
       at a.b.reference.action.dao.impl.ReleasePackageServiceImpl.findAllReleasePackages(ReleasePackageServiceImpl.java:71)
       at sun.reflect.GeneratedMethodAccessor1441.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
       at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
       at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:46)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.persistence.ManagedEntityIdentityInterceptor.aroundInvoke(ManagedEntityIdentityInterceptor.java:48)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:31)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:38)
       at org.jboss.seam.util.Work.workInTransaction(Work.java:40)
       at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:32)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:42)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
       at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
       at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
       at a.b.reference.action.dao.impl.ReleasePackageServiceImpl_$$_javassist_33.findAllReleasePackages(ReleasePackageServiceImpl_$$_javassist_33.java)
       at a.b.reference.action.impl.RefReleasePackageFactory.initAllReleasePackages(RefReleasePackageFactory.java:40)
       at sun.reflect.GeneratedMethodAccessor1440.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
       at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
       at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:46)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.persistence.ManagedEntityIdentityInterceptor.aroundInvoke(ManagedEntityIdentityInterceptor.java:48)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:31)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:42)
       at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
       at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
       at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
       at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
       at a.b.reference.action.impl.RefReleasePackageFactory_$$_javassist_34.initAllReleasePackages(RefReleasePackageFactory_$$_javassist_34.java)
       at sun.reflect.GeneratedMethodAccessor1439.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
       at org.jboss.seam.util.Reflections.invokeAndWrap(Reflections.java:125)
       at org.jboss.seam.Component.callComponentMethod(Component.java:2082)
       at org.jboss.seam.Component.getInstanceFromFactory(Component.java:1926)
       at org.jboss.seam.Component.getInstance(Component.java:1863)
       at org.jboss.seam.Component.getInstance(Component.java:1840)
       at org.jboss.seam.Namespace.getComponentInstance(Namespace.java:55)
       at org.jboss.seam.Namespace.getComponentInstance(Namespace.java:50)
       at org.jboss.seam.el.SeamELResolver.resolveBase(SeamELResolver.java:166)
       at org.jboss.seam.el.SeamELResolver.getValue(SeamELResolver.java:53)
       at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
       at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
       at org.jboss.el.parser.AstIdentifier.getValue(AstIdentifier.java:44)
       at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
       at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
       at javax.faces.component.UISelectItems.getValue(UISelectItems.java:130)
       at org.jboss.seam.ui.component.UISelectItems.getValue(UISelectItems.java:156)
       at com.sun.faces.renderkit.RenderKitUtils.getSelectItems(RenderKitUtils.java:323)
       at com.sun.faces.renderkit.html_basic.MenuRenderer.getOptionNumber(MenuRenderer.java:675)
       at com.sun.faces.renderkit.html_basic.MenuRenderer.renderSelect(MenuRenderer.java:793)
       at com.sun.faces.renderkit.html_basic.MenuRenderer.encodeEnd(MenuRenderer.java:335)
       at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:836)
       at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:279)
       at com.sun.faces.renderkit.html_basic.GridRenderer.encodeChildren(GridRenderer.java:242)
       at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:812)
       at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:271)
       at com.sun.faces.renderkit.html_basic.GroupRenderer.encodeChildren(GroupRenderer.java:117)
       at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:812)
       at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:271)
       at com.sun.faces.renderkit.html_basic.GridRenderer.encodeChildren(GridRenderer.java:242)
       at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:812)
       at org.ajax4jsf.renderkit.RendererBase.renderChild(RendererBase.java:282)
       at org.ajax4jsf.renderkit.RendererBase.renderChildren(RendererBase.java:262)
       at org.richfaces.renderkit.html.PanelRenderer.doEncodeChildren(PanelRenderer.java:220)
       at org.richfaces.renderkit.html.PanelRenderer.doEncodeChildren(PanelRenderer.java:215)
       at org.ajax4jsf.renderkit.RendererBase.encodeChildren(RendererBase.java:121)
       at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:812)
       at javax.faces.component.UIComponent.encodeAll(UIComponent.java:886)
       at javax.faces.render.Renderer.encodeChildren(Renderer.java:137)
       at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:812)
       at javax.faces.component.UIComponent.encodeAll(UIComponent.java:886)
       at javax.faces.component.UIComponent.encodeAll(UIComponent.java:892)
       at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:592)
       at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:108)
       at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:189)
       at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:106)
       at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
       at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144)
       at javax.faces.webapp.FacesServlet.service(FacesServlet.java:245)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
       at org.jboss.seam.debug.hot.HotDeployFilter.doFilter(HotDeployFilter.java:68)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
       at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:85)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
       at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
       at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
       at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:154)
       at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:260)
       at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:366)
       at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:493)
       at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:60)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
       at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:58)
       at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
       at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at a.b.reference.web.filter.ProfilingFilter.doFilter(ProfilingFilter.java:43)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at jcifs.http.NtlmHttpFilter.doFilter(NtlmHttpFilter.java:118)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
       at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
       at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
       at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
       at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
       at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
       at java.lang.Thread.run(Thread.java:595)
      Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
       at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
       at org.hibernate.loader.Loader.doList(Loader.java:2223)
       at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
       at org.hibernate.loader.Loader.list(Loader.java:2099)
       at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
       at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
       at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
       at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
       at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
       ... 138 more
      Caused by: java.sql.SQLException: Closed Connection
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
       at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:840)
       at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:349)
       at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:344)
       at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:187)
       at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
       at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
       at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
       at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
       at org.hibernate.loader.Loader.doQuery(Loader.java:673)
       at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
       at org.hibernate.loader.Loader.doList(Loader.java:2220)
       ... 146 more
      


        • 1. Re: jboss connectionpooling by subject, connection closed/no
          Adrian Brock Master

           

          "gjeudy" wrote:

          I'm considering adding a check-valid-connection-sql config something like:
          SELECT * FROM DUAL


          That will only help when you first get the connection from the pool.
          i.e. at the start of a new tranaction.


          Even if that worked wouldn't that add unnecessary overhead? I mean shouldn't jboss find such unrecoverable exceptions by itself and let go the physical connection handle


          It obviously has done, that is why you have the connection closed message
          most likely spotted by the OracleExceptionSorter you've configured.


          and recreate a new one ?


          It does, but you have to let the transaction rollback and start it over again
          which will retrieve a "new" connection from the pool.
          It can't recover mid-transaction.

          The connection remains "sticky" to the transaction even when it has errored and closed.

          The transaction is dead, the loss of the physical connection means you can
          no longer access the local transaction state within the database.

          Opening a new connection to take over from the failed one
          won't give you access to that state so you could never commit it
          or if you tried you would miss committing part of the data.

          So what you need is to have some code at whatever starts and ends the transaction
          that spots the SQLException (or any other retryable exception) and retry it.

          If you were using EJB/CMT then JBoss has a way to configure your own
          retry handlers that are transparent to the application:
          http://www.jboss.org/community/docs/DOC-11841
          Other frameworks may or may not have their own approach?

          Either way, the retry policy for a failed transaction is not something that
          can or should be configured at the DataSource level it's an application policy.

          • 2. Re: jboss connectionpooling by subject, connection closed/no
            Guillaume Jeudy Master

            Adrian, Thanks for your input.

            In my case the error could consistently be reproduced across several request cycles(transactions). I know the application is not at fault because it normally works without any code/config changes. I experienced this issue after I momentarily lost connection to the database server. I had to reconnect my SQL client but my local Jboss server could not recover and was consistently throwing this exception until I flushed the connection pool through jmx-console.

            What is the best way to monitor physical connection open/close? I'm a little bit wary of cranking up the logging as it will likely create alot of noise but still willing to do it if there are no alternatives.

            Thanks!
            Guillaume