Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

garbage collection and statement closing question

 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 429
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic