Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

cannot get connection, pool exhausted

534835Feb 23 2008 — edited Jan 14 2015
org.apache.tomcat.dbcp.dbcp.SQLNestedException: cannot get connection, pool exhausted.

i have configured connection pooling in tomcat in the context.xml file.
i have created a class with static method which will return the connection object from the datasource.
Iam using this connection object every time for database transactions.

At some time iam geeting this error..
org.apache.tomcat.dbcp.dbcp.SQLNestedException: cannot get connection, pool exhausted.

here are the configuration settings.....
<Resource
name="jdbc/pool"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
username="scott"
password="tiger"
driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:pool"
maxWait="1000"
removeAbandoned="true"
maxActive="130"
maxIdle="10"
removeAbandonedTimeout="300"
logAbandoned="true"
/>

Database used : Oracle 9i
driver: jdbc odbc driver (type 1)
Server: tomcat 5.5
can any one tell me how to solve this problem...if i increase the maxinActive="" attribute value to 140 iam getting the following error..

java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00020: maximum number of processes (150) exceeded...

how to over come this problem....?

here is the stack trace for the first problem pool exhausted...
-----------------------------------------------------------------------
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool ex
hausted
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDa
taSource.java:103)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSo
urce.java:540)
at rollno.Connect.getConnection(Connect.java:30)
at org.apache.jsp.admnin_jspService(admnin_jsp.java:7
8)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:328)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
15)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:188)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDisp
atcher.java:691)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(Applica
tionDispatcher.java:469)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationD
ispatcher.java:403)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDis
patcher.java:301)
at org.apache.jasper.runtime.PageContextImpl.doForward(PageContextImpl.j
ava:691)
at org.apache.jasper.runtime.PageContextImpl.forward(PageContextImpl.jav
a:661)
at org.apache.jsp.login_jspService(login_jsp.java:193)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:328)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
15)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:210)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:870)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.p
rocessConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpo
int.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFol
lowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:685)
at java.lang.Thread.run(Unknown Source)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(Gener
icObjectPool.java:756)
at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(Abandone
dObjectPool.java:74)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDa
taSource.java:95)
------------------------------------------------------------

thanks in advance

Comments

JustinCave Feb 23 2008
Are you certain that you are always closing the connections that you've opened? My first guess is that you have some sort of connection leak that is causing this problem.

Do you expect to need more than 130 simultaneous database connections?

Justin
534835 Feb 25 2008
thanks for the reply..
actually the application is undergoing performance testing..

yes you are write, there is some leakage problem and i have corrected already and i have changed the pool configuration ..

maxWait="60000"
maxActive="130"
maxIdle="50"

this time iam getting the following error
java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00020: maximum number of processes (150) exceeded.

I have set the maxActive to 130 then how will the number of processes exceeds 150?

tell me when will the maximum number of processes (150) exceeded in the oracle?
--------------
Actually i have created a class with static method which returns the connection object?

method
----------
public static synchronized Connection getConnection()
{
Connection con=null;
try
{
//con=DriverManager.getConnection("jdbc:odbc:pool","","");

// Obtain our environment naming context
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");


// Look up our data source
DataSource ds = (DataSource) envContext.lookup("jdbc/pool");

// Allocate and use a connection from the pool
con = ds.getConnection();
//System.out.println("connection established and object returned from the pool...");
//... use this connection to access the database ...
return con;
}
catch(Exception e)
{
e.printStackTrace();
}
return con;
}
--------------------------------------------
when ever i need the connection , iam invoking this method and once its usage is over iam closing the connection'

con.close();

this is what i was doing......
Can you tell me how to solve this problem?
iam badly in need of help...thnaks again
JustinCave Feb 25 2008
ORA-00020 indicates that Oracle has reached its configured limit for the number of processes it can spawn. This includes the processes for your application, for other users & applications, and for Oracle background processes. Your DBA can raise this limit, though the DBA may have concerns about doing so depending on the sort of hardware the database is running on.

Are you certain that you have resolved all the connection leaks? Assuming a standard OLTP type system, 100 database connections should be able to service many thousands of users. Are you simulating more than that for your performance testing?

Justin
414247 Feb 25 2008
Your static method is OK. The question is how you
manage the connections. I'm betting your application
code may not be thread-safe. Are you assigning your
connections to a variable that is visible to multiple
threads?

Thread 1 gets connection A and assigns the variable.
Thread 2 gets connection B and assigns the variable.
Thread A and B both use and close connection B.
(connection A is leaked)...

or, is it possible that you can exit your code due to
any exception, before closing the connection?

Here is the ideal standard for safe AppServer JDBC. If you can
adopt this, many such problems will go away.

/* This is how you should make any of your top-level methods
* that will do JDBC work for any of your user invokes.
*/
public void myTopLevelJDBCMethod()
{
Connection c = null; // All JDBC objects should be method
// level objects to ensure thread-safety
// and prevent connection leaking.
// Define the connection object before
// the JDBC 'try' block.


try {
// This is the JDBC try block for this method. Do
// all the JDBC for this method in this block.

// Get the connection directly from a DataSource
// in the try block. Do not get it from any method
// that has kept a connection and is sharing it for
// repeated use.

c = myDataSource.getConnection();

... do all the JDBC for this method in the scope of this try block...
... you can pass the connection or sub-objects to sub-methods
... but none of these methods must expect to keep or use the
... objects they receive after their method call completes...

(eg)
DoSomethingFancyWith(c);

// Use Prepared/Callable Statements. They are faster usually,
// Especially because we cache them transparently with the pool.
PreparedStatement p = c.prepareStatement(...);
ResultSet rs = p.executeQuery();
ProcessResult(myrs);

// Close JDBC objects in the proper order: resultset, then statement, then connection

rs.close(); // always close result sets ASAP at the level they were created
p.close(); // always close statements ASAP at the level they were created

...

// When the JDBC is finished in the try-block, close the con:
c.close(); // always close connection ASAP in the same method
// and block that created/obtained it.
c = null; // set the con to null so the finally block below
// knows it's been taken care of.
}
catch (Exception e ) {
... do whatever, according to your needs... you do not have to
... have a catch block if you don't want it...
}
finally {
// Always have this finally block. A finally block is crucial
// to ensure the connection is closed and returned to the pool,
// (not leaked).
// failsafe: Do every individual thing you want to do in the
// finally block in it's own try block-catch-ignore so everything
// is attempted.

try {if (c != null) c.close();} catch (Exception ignore){}
}
}
534835 Feb 26 2008
thanks for the reply Mr.Justin Cave

I have checked the application 5 to 10 times and there is no connection leakage problem.

In the application, when the user logged in, 5 connection objects are created and once the database processing is over connection is closed.
So when 100 users logged in at a time 500 connection objects are created , performed database transactions and the connection is closed.

during performance testing, 150 users logged in at a time and looged out, for the first time it is working perfectly, If performance testing is repeated 2 to 3 times, the following error is comming..

java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00020: maximum number of processes (150) exceeded.

I have checked the "vsysstat" table at that time , the "logons current" value has reached more than 140.

this is what happening in the application.

please give me some suggestions for solving this problem..
534835 Feb 26 2008
thanks Mr. Joe for the reply.

As i said earlier, i have declared a Connection variable in the scriplet and iam invoking the getConnection() method, performing some database operations and once it is done iam closing the connection.

like

<%
Connection con=null;
try{
con=pool.Connect.getConnection();
Statement setst=con.createStatement();
ResultSet setrs=setst.executeQuery("select * from pool");
if(setrs.next())
{
/*retrieving the values*/
}

setrs.close();
setst.close();
}
catch(Exception e){e.printStackTrace();}
finally
{
if (con!=null&&!con.isClosed())
pool.Connect.closeConnection(con);
}

/*close connection method*/
public static synchronized void closeConnection(Connection con1)
{
try
{
con1.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}

-------------------------------
The variables which are declared in scriplet becomes the local variables of the generated servlets _service() method and hence they are thread safe.

This is what i was doing in the application. Now can you tell me where went wrong?
Is my code thread safe, if not where i have done the mistake?
help me..
JustinCave Feb 26 2008
In the application, when the user logged in, 5
connection objects are created and once the database
processing is over connection is closed.
So when 100 users logged in at a time 500 connection
objects are created , performed database transactions
and the connection is closed.
I assume you mean that 500 connection objects are created and destroyed over the course of the test. Not that you're opening 5 connections every time a user logs in.
during performance testing, 150 users logged in at a
time and looged out, for the first time it is working
perfectly, If performance testing is repeated 2 to 3
times, the following error is comming..

java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00020:
maximum number of processes (150) exceeded.
That would either imply that you have a connection leak or that the second or third performance test is doing something differently than the first. I'd tend toward the former explanation.
I have checked the "vsysstat" table at that time ,
the "logons current" value has reached more than
140.
Are you using shared server or dedicated server connections? Have you monitored the sessions in v$session? I'm guessing that after you run your load test that you'll see a lot more open connections to the database than you'd expect for your application, which would be indicative of a connection leak.

Justin
414247 Feb 26 2008
Hi. I am ignorant of the subject, but I know that servlets become
a single object that many threads can execute simultaneously
so if this is different, ok, but just for a test, I'd try this (to completely
ensure the connection is limited in scope:

<%

Exception ex = null;
try {
Connection con=null;
try{
con=pool.Connect.getConnection();
Statement setst=con.createStatement();
ResultSet setrs=setst.executeQuery("select * from pool");
if(setrs.next())
{
/*retrieving the values*/
}

setrs.close();
setst.close();

pool.Connect.closeConnection(con);
con = null;
}
catch(Exception e){ ex = e; }
finally
{
if (con!=null) // We know something went wrong...
{
pool.Connect.closeConnection(con);
}
}
}
finally {
if (ex != null)
{
throw ex;
}
}
534835 Feb 27 2008
thanks Justin Cave for the reply,

I assume you mean that 500 connection objects are created and destroyed over the course of the test. Not that you're opening 5 connections every time a user logs in.

That would either imply that you have a connection leak or that the second or third performance test is doing something differently than the first. I'd tend toward the former explanation.

I have created a table for storing the connection details like.when the connection is created an entry is made in the table stating tht connection established.
Also when the connection is closed one more entry is made in the table stating tht connection is closed.
Once the performance testing is done, i have checked the table to find out how many connections are created and how many connections are closed. I have got the expected result and also find out that number of connections created equals number of connections closed.
Also the connection created and closed simultaneously..

The performance testing done for the second and third time are same as the first one, no change..

Are you using shared server or dedicated server connections? Have you monitored the sessions in v$session? I'm guessing that after you run your load test that you'll see a lot more open connections to the database than you'd expect for your application, which would be indicative of a connection leak.

i dont know whether iam using shared or dedicated server connections, can you tell me how to find out?
I dont know how to monitor the sessions in v$session..

May be you are correct, after load test lot more open connections to the database than you'd expect for your application, which would be indicative of a connection leak.

But the opened connections are closed then and there, then why the problem is comming. Can you please suggest me the ways to overcome this problem.
534835 Feb 27 2008
thanks Mr. Joe for the reply..
internally jsp is nothing but a servlet...
i have tried and tested the jsp and iam sure that the connection created are closed perfectly..
thanks for your code..i have gone through it, its simple and easy to understand.

Can you tell me still there is some problem with the way iam creating the connection and it s usage in the code.

What could be the reason for the "number of processes exceded in the oracle"?
thanks again
414247 Feb 27 2008
That's a DBMS-side issue entirely.
1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 26 2008
Added on Feb 23 2008
11 comments
8,679 views