Hi all,
I had my application deployed in Tomcat 5.5 later in Tomcat 6.0 which used connection pooling.I didn't had any problem with Tomcat and using it for more that 1 year. Application is basically developed using JSF with Postgres 8.1 as a backend database. I am not using EJB or any JTA.
I am trying to deploy my application on Glassfish Server v2 ur2 but facing connection pooling problem. The connection pool gets exhausted after some time.
SQL Exception throws a message as follows:
In-use connections equal max-pool-size.
I have figured out that connection pooling fails when there are nested SQL connections and my application tries to get more than one connection. I have been struggling for 3 days and still could not solve this problem.
I am assuming that :
1. I get a physical connection from the pool by:
DataSource ds_1 = (DataSource) cxt.lookup( "java:comp/env/jdbc/postgres" );
Connection con = ds.getConnection();
2. con.close() will return a connection to the pool.
3. correct way of closing a connection is :
try {
............
stat = rs.getStatement();
con = stat.getConnection();
rs.close(); rs=null;
stat.close() ; stat = null;
con.close() ;
System.out.println(" Connection closed");
con = null ;
}
catch (Exception e) {
System.out.println("ClOSE CON: "+e.getMessage());
e.printStackTrace();
}finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try { rs.close(); } catch (SQLException e) { ; }
rs = null;
}
if (stat != null) {
try { stat.close(); } catch (SQLException e) { ; }
stat = null;
}
if (con != null) {
System.out.println(" con not null Closing Connection");
try {
con.close();
} catch
(SQLException e) {e.getMessage() ; e.printStackTrace(); }
con = null;
}
}
}
4. order of closing a connection in nested connection is not important (ie. closing the last opened connection first and then second last connection doesn't matter)
please correct me if my assumptions are wrong.
As of now I am doing some testing with only one user. Parameters for connection pool are:
Initial poolsize = 30
Max pool size = 100
pool resize quantity = 2
Idle Time out = 300 seconds
Max Wait time = 60000 milli seconds
Isolation level guaranteed
leak time out = 15 seconds
creation retry attempts = 3
retry interval = 10 seconds
Still after 8 to 10 successful attempts the connection pooling fails.
I have gone through
http://forums.java.net/jive/message.jspa?messageID=224047
https://glassfish.dev.java.net/issues/show_bug.cgi?id=3279
http://forums.java.net/jive/thread.jspa?messageID=264139񀟋
but could not find a solution.
please help me out of this.
Also, as I access the application I get this messages :
A potential connection leak detected for connection pool jdbc/postgres. The stack trace of the thread is provided below :
com.sun.enterprise.resource.AbstractResourcePool.setResourceStateToBusy(AbstractResourcePool.java:301)
com.sun.enterprise.resource.AbstractResourcePool.getResourceFromPool(AbstractResourcePool.java:778)
com.sun.enterprise.resource.AbstractResourcePool.getUnenlistedResource(AbstractResourcePool.java:652)
com.sun.enterprise.resource.AbstractResourcePool.internalGetResource(AbstractResourcePool.java:594)
com.sun.enterprise.resource.AbstractResourcePool.getResource(AbstractResourcePool.java:443)
com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:248)
com.sun.enterprise.resource.PoolManagerImpl.getResource(PoolManagerImpl.java:176)
com.sun.enterprise.connectors.ConnectionManagerImpl.internalGetConnection(ConnectionManagerImpl.java:327)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:235)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:165)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:158)
com.sun.gjc.spi.base.DataSource.getConnection(DataSource.java:108)
hraps.Connect_1.getConnection(Connect_1.java:64)
hraps.Connect_1.execSelect(Connect_1.java:132)
hraps.SessionBean1.<init>(SessionBean1.java:80)
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
java.lang.reflect.Constructor.newInstance(Constructor.java:513)
java.lang.Class.newInstance0(Class.java:355)
java.lang.Class.newInstance(Class.java:308)
com.sun.faces.config.ManagedBeanFactoryImpl.newInstance(ManagedBeanFactoryImpl.java:277)
com.sun.faces.application.ApplicationAssociate.createAndMaybeStoreManagedBeans(ApplicationAssociate.java:527)
com.sun.faces.el.ManagedBeanELResolver.getValue(ManagedBeanELResolver.java:82)
javax.el.CompositeELResolver.getValue(CompositeELResolver.java:143)
com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
com.sun.el.parser.AstIdentifier.getValue(AstIdentifier.java:86)
com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:206)
com.sun.faces.application.ValueBindingValueExpressionAdapter.getValue(ValueBindingValueExpressionAdapter.java:102)
hraps.CheckUserLockListener.beforePhase(CheckUserLockListener.java:46)
com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:222)
com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144)
com.sun.faces.extensions.avatar.lifecycle.PartialTraversalLifecycle.render(PartialTraversalLifecycle.java:106)
javax.faces.webapp.FacesServlet.service(FacesServlet.java:245)
org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:317)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:198)
com.sun.webui.jsf.util.UploadFilter.doFilter(UploadFilter.java:267)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:198)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:198)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:288)
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:271)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:202)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:206)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571)
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:150)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571)
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080)
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:272)
com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter(DefaultProcessorTask.java:637)
com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess(DefaultProcessorTask.java:568)
com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process(DefaultProcessorTask.java:813)
com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask(DefaultReadTask.java:341)
com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:263)
com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:214)
com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:265)
com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run(SSLWorkerThread.java:106)
Monitoring Statistics :
PoolCounters:
maxNumConnUsed = 10
minNumConnUsed = 0
currNumConnUsed = -355
maxNumConnFree = = 366
minNumConnFree = 0
currNumConnFree = 365
numConnCreated = 495
numConnDestroyed = 450
Inside before Phase
Got New Connection
Got New Connection
Closing Connection
Connection closed
Please help me in sorting out this problem. I need to use Glassfish so as to use EJB and Transaction Managment later on in my application.
[Message sent by forum member 'pratham_vishnu' (pratham_vishnu)]
http://forums.java.net/jive/thread.jspa?messageID=284623