This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes garbage collection and statement closing question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "garbage collection and statement closing question" Watch "garbage collection and statement closing question" New topic
Author

garbage collection and statement closing question

Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
First question:

When the garbage collector cleans up a JDBC Connection, does it release the resources? I know that you're always supposed to explicitly close Connections, but in some code that I'm maintaining I found what appears to be a Connection leak, and I just wanted to know how serious it is. If the GC releases the resources, than it's not that bad, though of course it will need to be fixed at some point.

Basically, to open a new Connection, an OpenConnectionThread instance is created. The Connection is opened inside the run method. The current thread starts the OpenConnectionThread instance and joins on it for a configured timeout period (20s). After the join exits, the OpenConnectionThread is checked to see if it has successfully created the Connection. If it hasn't, an Exception is thrown.

The problem is that even after the exception is thrown, OpenConnectionThread instance will keep on running, and may eventually acquire the connection, which no one save the GC will ever see.

An obvious fix would be to set a boolean on the thread, which will tell it if the connection needs to be closed immidiately after opening. Maybe even interrupt the thread to conserve resources. The question is, how critical is it? Will the GC eventually close the Connection, making leaked resources a temporary problem, or will the Connection remain open until the JVM shuts down?

Whew.

My second question is much simpler:

Is there any reason to close Statements and PreparedStatements if you're gonna be closing the Connection immidiately afterwards? Cases like:

try {
pstmt1.close()
} catch (Exception e) {}
try {
pstmt2.close()
} catch (Exception e) {}
try {
pstmt3.close()
} catch (Exception e) {}
try {
pstmt4.close()
} catch (Exception e) {}
try {
conn.close()
} catch (Exception e) {}


Since Connection.close() is supposed to automatically release all of that Connection's resources, these seem to me like completely unnecessary round trips to the database. Am I correct in this?


Thanks in advance!
Yuriy Zilbergleyt
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Yuriy,
#1 - It depends. If the implementer has a finalizer, it may be implemented. However, you could easily have a deadlock before this happens which will bring your application to a grinding halt. For example if you have a connection pool of 5 connections and all are waiting on garbage collection, your system is not going to do anything.


Visit this before you visit any performance related issues though.

Why not open/use/close the connection in the same thread?

#2 - "Since Connection.close() is supposed to automatically release" - "supposed to" is the operative phrase here. Not all drivers actually do this, at least not consistently. Which is why people get in the habit of doing this themselves.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
Thanks for the advice, Jeanne!

The driver being used is oracle's classes12.jar. There's no finalize in oracle's Connection class itself, though it might still get handled somehow....

At least according to the log files, this type of failure to open a connection happened for the first time in years yesterday (this is what made me look into the issue in the first place). In any case, I'll open a trouble ticket for this.

On the other hand, the connection's close() closes all the statements, so that's good.


Why not open/use/close the connection in the same thread?

Performance. Why keep making additional round trips, when the same set of connections can last for days? Plus all the prepared statements will already be there, cached. Though we really need to think of moving to stored procedures for this app...

Thanks!
Yuriy
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Originally posted by Yuriy Zilbergleyt:
Performance. Why keep making additional round trips, when the same set of connections can last for days?

For the future, connection pooling is often used for this scenario. The connections stay active in a pool without you having to deal with it yourself. conn.close() just returns the connection to the pool then.
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
For the future, connection pooling is often used for this scenario. The connections stay active in a pool without you having to deal with it yourself. conn.close() just returns the connection to the pool then.

This is actually the implementation of our connection pool that I'm talking about. Our application is a POJO backend app. It's not deployed inside of an application server. We use a(n in-house created) library for an abstract resource pool, and then extend it for JDBC connections.
[ February 07, 2007: Message edited by: Yuriy Zilbergleyt ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: garbage collection and statement closing question
 
Similar Threads
ORA-01000: maximum open cursors exceeded
Releasing resources in a stateful session bean
Exception Handling-Purpose of finally in the try-catch construct
garbage collection
Life of the JDBC-ODBC Connection object in a Thread??