aspose file tools*
The moose likes Tomcat and the fly likes Application hosted on Tomcat has datasource DBCP issues Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Tomcat
Bookmark "Application hosted on Tomcat has datasource DBCP issues" Watch "Application hosted on Tomcat has datasource DBCP issues" New topic
Author

Application hosted on Tomcat has datasource DBCP issues

Ajay Kamble
Ranch Hand

Joined: Jan 23, 2009
Posts: 51
Hi All,

I am working on an web application hosted on Tomcat 5.5.7 and MySQL 4.1.7 (Linux OS).

We are using DBCP Datasource provide by Tomcat.

The application has some utility code and it provides two interfaces - web application and web service. Both interfaces provide same functionality. Web service interface is used by a standalone simple utility program that users can use from command line.

Recently we are facing severe issues with application, reason being heavy usage of utility program. From the logs we found that single user is using it heavily.

We see that application (datasource connections) is serving mostly to this user and that is why other users are facing issues as they keep getting errors.

Our major problem is with datasource connections as all get consumed by this particular user. We get following errors,

1. org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
2. java.sql.SQLException: Connection is closed

I believe both these errors occur because datasource connection numbers are insufficient for this high usage. I have checked code to see if connections are not released, but that does not seem to be a problem.

Here is our datasource configuration,



Can we improve this configuration? I know maxActive no is very less (we actually increased it from 15 to 30, 15 used to serve well for us before), but we are not sure if increasing this no will help us. If we make it 50 then 50 will be consumed, if we make it 100 then 100 will be consumed.

Do we need to remove maxWait? Or do we need to increase removeAbandonedTimeout? Please provide your suggestions.

Also, I was wondering why web service calls put such a load on system. Why system is serving only one user who is consuming web service through utility code? Do we need to use some thread pool where we make call to utility code like,



Any help is highly appreciated.

## Some more error logs
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:756)

##DBCP object created 2009-04-06 09:31:09 by the following code was never closed:
java.lang.Exception

I checked this code and it releases connection properly, not sure why above error is thrown, Here is code,



We get exception while closing though,
05-04-2009 00:06:40|WARNING|SomeClass.someMethod()|Error closing connection. java.sql.SQLException: Already closed

Regards,
Ajay
Kees Jan Koster
JavaMonitor Support
Rancher

Joined: Mar 31, 2009
Posts: 251
    
    5
Dear Ajay,

Your code is leaking connections. You have to check the stack trace of "##DBCP object created 2009-04-06 09:31:09 by the following code was never closed:" to find the unclosed exceptions.

The code you show looks correct, but check the code precisely, especially for nested calls to database operations.


Java-monitor, JVM monitoring made easy <- right here on Java Ranch
Ajay Kamble
Ranch Hand

Joined: Jan 23, 2009
Posts: 51
Hi Kees,

Thanks for your reply. I checked stack trace for DBCP errors and the code that I pasted in my first post is the same code.

It is pretty simple. It is doing database operations in try statement and connection is closed in finally statement.

It is difficult to see why connections are not released.

By looking at all different kinds of database related error I feel maybe there is some other issue. I am getting connection not released, connection pool exhausted, connection already closed.

Is it problem with database connection pool or something else?

Regards,
Ajay


Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8966
    
    9

Ajay Kamble wrote:
Is it problem with database connection pool or something else?


You could try searching the DBCP Issue Tracker. I would think that your problem is unlikely to be in DBCP, as it is widely used and well tested.


[How To Ask Questions On JavaRanch]
Ajay Kamble
Ranch Hand

Joined: Jan 23, 2009
Posts: 51
Joe,

I tried to search DBCP issue tracker. Unfortunately I didn't found anything there.

Can i try something else to resolve this issue?

Regards,
Ajay
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8966
    
    9

The only thing I can think of, as Kees suggests, is that there's something you aren't showing us in your code that's using database connections and not returning them to the pool.
Javid Jamae
Author
Ranch Hand

Joined: May 14, 2008
Posts: 198
If you're passing the connection around, perhaps your closing it in another method. The best thing to do is to track that down. You might also consider checking if the connection is closed before closing it in your finally block to avoid getting the exception (though I don't know if this is a best practice). :-)



Author: JBoss in Action, javidjamae.com, @javidjamae on Twitter
Rob Janney
Greenhorn

Joined: Oct 21, 2009
Posts: 1
If you are using dbcp 1.2.1 or earlier, the "connection already closed" issue could be caused by this bug: http://issues.apache.org/jira/browse/DBCP-28
 
wood burning stoves
 
subject: Application hosted on Tomcat has datasource DBCP issues