7 messages in com.mysql.lists.javaRe: Closing Statements and ResultSets| From | Sent On | Attachments |
|---|---|---|
| Ken Bloom | 12 Feb 2006 08:16 | |
| David Rabinowitz | 12 Feb 2006 09:13 | |
| Robert DiFalco | 12 Feb 2006 09:16 | |
| Christopher G. Stach II | 12 Feb 2006 15:08 | |
| Mark Matthews | 12 Feb 2006 19:10 | |
| Ken Bloom | 12 Feb 2006 20:33 | |
| Clayburn Juniel | 13 Feb 2006 06:29 |
| Subject: | Re: Closing Statements and ResultSets |
|---|---|
| From: | Mark Matthews (ma...@mysql.com) |
| Date: | 02/12/2006 07:10:34 PM |
| List: | com.mysql.lists.java |
Ken Bloom wrote:
I have noticed that Connector/J 3.1.x is very strict about making sure to close all of your Statements and ResultSets. If you don't close them, the consequence is a leak of memory and of database resources.
Unfortunately, in my developemnt group, we have some very lazy programmers who don't always close these things. Additionally, there are some situations where it's convenient to stick most of the DB connection code off in a function and only return a ResultSet. In this situation, the Statement can be lost, and we will be unable to close it until we close the database connection. We could potentially execute hundreds of thousands more Statements before we close the connection.
I have noticed that Connector/J keeps these statements from being garbage collected and finalized by holding onto them, so that it can do a mass cleanup when Connection.close() is called.
Could a conforming implementation of a JDBC driver use the java.lang.ref classes to hold on to Statements and ResultSets for the mass cleanup, so that they can be garbage collected and finalized when they are no longer used by client code, even if that's earlier than Connection.close()? Would it break Connector/J if I were to modify it to use java.lang.ref? Are there any pitfalls to watch out for?
Ken,
Yes, I've benchmarked this approach, and it's 15-25% slower _overall_ in throughput than the same Connector/J code that doesn't use this approach due to how reference queues and finalizers work currently.
If you're using prepared statements that exist on the server, there's a very good chance you're going to run into deadlock, if not a very hairy locking scheme because these statements need to communicate with the server when they're closed.
As MySQL starts to get more features that require communication with the server to release resources (such as cursors), this kind of implementation starts to fall apart.
The other issue is that this will _not_ be future proof. JDBC-4.0 removes any ideas that a developer can rely on statements being closed automatically by the driver when GC'd, since generally it's just not workable (in fact it's explicitly not allowed). This was at the request of pretty much every vendor on the JDBC-4.0 E.G. which has found that automatic resource "recovery" for JDBC is basically unworkable.
-Mark