Connections used in Oracle connection pool

[devang]

Hello,

I am using jboss-3.2.5 on solaris, Sun JDK 1.4.2. My oracle-ds.xml is configured to use a local-tx-datasource with the thin JDBC driver from oracle.

This setup was working fine on the development linux machine with a small load. On the production, though, it doesn't seem to recycle a conncection after it is used once. I have tracking enabled; we are closing all resultsets, statements and connections in that order. I have quadrupled the blocking timeout to 120 seconds and have raised the max-connections to 200 as suggested in the FAQ. It just helps to delay the inevitable. I also reduced the idle-timeout to 1 minute without any improvement.

We are not using any transactions in our code or any other special features. Just vanilla JDBC.

Here is the stack-trace; I hope someone can help me....Thanks.


22:42:32,264 ERROR [Engine] StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception
org.jboss.util.NestedSQLException: No ManagedConnections available within configured blocking timeout ( 120000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 120000 [ms] ))
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:106)
at com.mspacific.utils.DBReport.initJDBC(DBReport.java:65)
at com.mspacific.utils.DBReport.execute(DBReport.java:552)
at org.apache.jsp.EBC.briefingView_jsp._jspService(briefingView_jsp.java:847)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:703)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:463)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:398)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:312)
at org.apache.jasper.runtime.PageContextImpl.doForward(PageContextImpl.java:670)
at org.apache.jasper.runtime.PageContextImpl.forward(PageContextImpl.java:637)
at org.apache.jsp.briefingView_jsp._jspService(briefingView_jsp.java:282)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:72)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:275)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
Caused by: javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 120000 [ms] )
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:252)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:534)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:444)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:312)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:887)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:102)
... 54 more

[devang]

I just verified and this problem happens on Linux(JDK 1.4.2) too. I did a trace on the connection pool and I have pasted it below. The number of inUse connections never seems to drop (except sometimes by 1). When the number of connections reaches the maximum (40 in this case); I get the error again.

I might be using the wrong driver clsss; that is just one guess.

[6~2004-07-20 19:22:54,586 TRACE [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] supplying new ManagedConnection: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@11ce4fe[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1fe0d66 handles=0 lastUse=1090376574586 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@39bf12 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@12f4538]
2004-07-20 19:22:54,586 TRACE [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Getting connection from pool org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@11ce4fe[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1fe0d66 handles=0 lastUse=1090376574586 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@39bf12 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@12f4538] [InUse/Available/Max]: [33/7/40]
2004-07-20 19:22:55,434 TRACE [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] putting ManagedConnection back into pool kill=false cl=org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@11ce4fe[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1fe0d66 handles=0 lastUse=1090376574586 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@39bf12 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@12f4538]
2004-07-20 19:22:55,529 TRACE [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Returning connection to pool org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@11ce4fe[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1fe0d66 handles=0 lastUse=1090376575529 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@39bf12 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@12f4538] [InUse/Available/Max]: [32/8/40]
2004-07-20 19:23:06,928 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] run: IdleRemover notifying pools, interval: 30000

[troypoppe]

I've seen similar problems with an application we host. Turns out the developer was not ensuring that his connection was closed in a finally block.

[adrian.brock]

See the comment in deploy/jbossweb-tomcat50.sar/server.xml

[penguine]

Hi Adrian,

We have the same problem.
It seems that all the connections are closed by the applications. In jboss3.0.* it has worked fine. In 3.2.* we have the problem described above.

[ecka]

This 'error' suddenly started occuring for us in one of our JBoss projects. Without any obvious reason.
We use CMP to 99%. The only difference btw this and other projects is that it has a little more CMPs and MBeans. We use JBoss 3.2.2.

Does anyone have any good hints were to start looking? I've traced the connection and I can only see that some connection don't get returned to the pool.

Thanks

[ecka]

Since we found our bug I thought I'd answer my own question.

Concentrate on the 1% that manually handle connections

I'm welcome

[penguine]

look at:
Problems under heavy production load
http://www.jboss.org/index.html?module=bb&op=viewtopic&t=54943
we a similar problem, too.
When we use jms and hypersonic for persistence manager than the
jbossmanagedconnectionpool became problems under heavy load.
there is "no managed connections available".

[penguine]

Hi Adrian,

is it a synchronize problem in "InternalManagedConnectionPool.java"?