major problem with connections not closing

We had a major problem in which we tried to roll the web app over to running against an Oracle database, and the number of open connections in our pooling code (that worked fine with the old database) blew up when lots of users started getting onto the site. The connections just were not being released, and any JDBC calls starting spitting out exceptions, and the Oracle 10g thin client driver was saying that the maximum number of sessions were open (we were in the hundreds--we have a per-CPU license, so that shouldn't be an issue).

We're not sure what to test right now. Whether we use Statements or PreparedStatements, we try to always close the Statement (which should close the ResultSet, if there is one) and then in the finally part of our try-catch-finally, we try to close the connection. Is that all we need to do? Can you think of anything we should be looking for? Thanks! Our Oracle expert doesn't know any JDBC stuff...

We've actually delt with this as well. We attempted to implement best practices:

close the statement and resultset seperately. Always put the closes for the statement and the resultset in the finally block enclosed in an if statement...

Ex
if(rs != null)
{
rs.close();
rs = null
}

if(statement != null)
{
statement.close();
statement = null;
}

if(connection != null)
{
connection.close();
connection = null
}

Also, use prepared statements whenever possible (more efficient). We had problems with our connection pooling dumping our users until we finally moved from tomcat to websphere.

-Rene

I doubt we'll move from Tomcat to Websphere anytime soon...we'd be talking major bucks to load Websphere on two 2-processor webservers, right?

We already do what you suggested. Our connection pooling code was written by someone who is no longer here, but it looks very close to what I've seen in tutorials online, so I think it's basically the same thing as what the Apache group wrote. However, there's a bit of code that confuses me...a ConnectionReaper runs in its own Thread to clean up every once in a while, and it does this:

The removeConnection method calls close() on the PooledConnection, and it also removes it from the pool's List. That PooledConnection.close() method is below:

If you look back at the first snippet, you'll see that it's checking to see if the connection is not in use--I changed it to that today. Before, it was checking to see if it was in use. Now, what would be the right thing to do here? It almost seems like you wouldn't want to just check one or the other--instead, it seems to me like you'd want to check pretty regularly (every few minutes?) to see if connections are not in use and haven't been used in a while and clean them out of there, and you might also want to check connections that are in use and have been in use for a long time (runaway connections going berserk?). Does that sound right? I looked at an early version of this code, and I see that it's always been written to check and see if the connection was in use, so maybe it was meant to only check for hung connections, or maybe it was a typo.

I'm caring about this so much because we're not using Tomcat 5 (which I understand has a built-in connection pooling facility) and we're not about to switch to Tomcat 5 because we're under pressure to get this fixed quickly.

there are lots of db pool implementations. commercial and free one and i suggest you use one of these (why reinvent the wheel...)

e.g. have a a look at:
http://jakarta.apache.org/commons/dbcp/

pascal

I'm in total agreement...I think our pooling code is very much like an earlier version of the commons one...I mean, I think he might have taken that and tweaked one or two things, but I'm just guessing. However, we found something, and we're looking for more things like that...

Because of the way the tables and indexing was set up in our old database, we used a LIKE statement on a table with about 1.5 million rows, and it was killing our web app when we ran it on Oracle because that gets called every time someone logs in and goes to one of the "home" pages of the site (so it gets hit numerous times after they log in as well). When we hit the site with lots of users via JMeter, a lot of them got in and started browsing, but a lot of them got hung because a lot of those queries were hanging for a minute or more!!! Hence our system basically crashed on us...

So now we're looking for more stuff like that, and then we'll see how it performs, and we'll run some simple checks on our db pooling code just to make sure (number of openings of connections versus closings, etc). I would like to like to eventually switch to Tomcat 5 and not use custom pooling code (no matter how non-custom it is), but because of time constraints we don't have the luxury of convincing people to let us try to switch to something else right now.