aspose file tools*
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
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31062
    
232

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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31062
    
232

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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: garbage collection and statement closing question