ConnectionPooling, connections runs out?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Friday, September 30, 2005 05:30 AM

I have the following JNDI connection to a Oracle database from a Tomcat 5.5 server. Context accessToUnderlyingConnectionAllowed="true" auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="100" maxIdle="15" maxWait="10000" name="jdbc/OracleDB" type="javax.sql.DataSource" url="jdbc:oracle:thin:@127.0.0.1:1521:MINE" username="username" password="password" validationQuery="select sysdate from dual "/> W   More>>

I have the following JNDI connection to a Oracle database from a Tomcat 5.5 server.



Context



accessToUnderlyingConnectionAllowed="true"

auth="Container"

driverClassName="oracle.jdbc.driver.OracleDriver"

maxActive="100"

maxIdle="15"

maxWait="10000"

name="jdbc/OracleDB"

type="javax.sql.DataSource"

url="jdbc:oracle:thin:@127.0.0.1:1521:MINE"

username="username"

password="password"

validationQuery="select sysdate from dual

"/>



Web.xml



Oracle database

connection

jdbc/OracleDB

javax.sql.DataSource

Container







The problem is that sometimes the connectionpool "runs out" and it's not near the 100 connection.



The way I do it is that each function gets a connection from the pool



public boolean func1()

{

try

{

Connection c = getConnection();

//sql call(s)

}

catch(SQLException e)

{

}

finally

{

//close rs, preparedstatement and connection

}



and thease function may be nested. So funct 1 calls funcc 2 that calls funct 3 etc. So I guess a single user may get multiple connection to the database for a short time.



But the website can have around 5 visitors that makes calls, and the getConnection function can take long time to return a connection. I guess that this means that all connection in the pool is gone (all 100?) and it waits up to 10 sec for a new connection.



Should not 100 connection be enough? Does any have any tip so I can work this problem out?







Another problem I had is that when the user first comes to the site it takes a long time to load it. This is also becouse of the connection pool. My guess was that there is no empty connection in the pool and a new (many new?) was created. Is there a way to specify a minimum number of connection to hold in the pool?





When I look in oracle and sessions, there is only 1 or 2 connection/session but with many cursors. I assume this is the way it should be. When is a new connection/session set up? Is the any maximum cursors on a single connection/session?



Greatful for any suggestions...

   <<Less

Re: ConnectionPooling, connections runs out?

Posted By:   Uzi_Refaeli  
Posted On:   Monday, October 31, 2005 06:59 AM

What about open only one connection and pass it to the other functions?


Consider this:


public boolean func1(){
try{
Connection c = getConnection();
//sql call(s)

func2(c);

}catch(SQLException e){
}finally{
//close rs, preparedstatement and connection
}
}

public boolean func2(Connection con){
try{
//use con here as you like
}catch(SQLException e){
}finally{
//what ever you want BUT DON'T CLOSE the connection here
}
}
About | Sitemap | Contact