Will calling "close()" on a "java.sql.Connection" really closeassociated resources?

[david.karr]

In billions of lines of code using JDBC connections, I constantly see
finally clauses that first close a ResultSet, then a Statement, and
then a Connection (if any of them are non-null). The thing is, the
javadoc for the "close()" method says this:

"Releases this Connection object's database and JDBC resources
immediately instead of waiting for them to be automatically released.
"

That implies to me that manually closing the ResultSet and Statement
just before closing the Connection is a waste of perfectly good
bytecode.

If I'm understanding this correctly, is there any situation where it's
still a good idea to manually close the resources when you're about to
close the connection?

 

[David Karr]

In billions of lines of code using JDBC connections, I constantly see
finally clauses that first close a ResultSet, then a Statement, and
then a Connection (if any of them are non-null). The thing is, the
javadoc for the "close()" method says this:

    "Releases this Connection object's database and JDBC resources
immediately instead of waiting for them to be automatically released.
"

That implies to me that manually closing the ResultSet and Statement
just before closing the Connection is a waste of perfectly good
bytecode.

If I'm understanding this correctly, is there any situation where it's
still a good idea to manually close the resources when you're about to
close the connection?

It occurred to me later, if the Connection is from a connection pool,
and isn't really closed at that point of calling "close()" in user
code, will those resources still be automatically closed, or would
they be closed only if the actual Connection is closed?

 

[Arne Vajhøj]

In billions of lines of code using JDBC connections, I constantly see
finally clauses that first close a ResultSet, then a Statement, and
then a Connection (if any of them are non-null). The thing is, the
javadoc for the "close()" method says this:

"Releases this Connection object's database and JDBC resources
immediately instead of waiting for them to be automatically released.
"

That implies to me that manually closing the ResultSet and Statement
just before closing the Connection is a waste of perfectly good
bytecode.

If I'm understanding this correctly, is there any situation where it's
still a good idea to manually close the resources when you're about to
close the connection?

JDBC spec says:

<quote>
9.4.4 Closing Connection Objects
An application calls the method Connection.close to indicate that it has
finished
using a connection. All Statement objects created from a given
Connection object
will be closed when the close method for the object is called.
Once a Connection has been closed, any attempt to access any of its
methods with
the exception of the close, isClosed or isValid methods will result in a
SQLException being thrown.
</quote>

<quote>
13.1.4 Closing Statement Objects
An application calls the method Statement.close to indicate that it has
finished
processing a statement. All Statement objects will be closed when the
connection
that created them is closed. However, it is good coding practice for
applications to
close statements as soon as they have finished processing them. This
allows any
external resources that the statement is using to be released immediately.
Closing a Statement object will close and invalidate any instances of
ResultSet
produced by that Statement object. The resources held by the ResultSet
object
may not be released until garbage collection runs again, so it is a good
practice to
explicitly close ResultSet objects when they are no longer needed.
Once a Statement has been closed, any attempt to access any of its
methods with
the exception of the isClosed or close methods will result in a SQLException
being thrown.
These comments about closing Statement objects apply to PreparedStatement
and CallableStatement objects as well.
</quote>

So they will be closed.

But actually I think it is good to do it explicit. Because
it makes it obvious that they are being closed also to those
that have not read the JDBC spec.

Readable code is better than short code.

Arne

 

[Arne Vajhøj]

It occurred to me later, if the Connection is from a connection pool,
and isn't really closed at that point of calling "close()" in user
code, will those resources still be automatically closed, or would
they be closed only if the actual Connection is closed?

More quotes:

<quote>
In a DataSource implementation that includes connection pooling, a great
deal
happens behind the scenes. In such an implementation, the following are
true:
â–  The DataSource implementation includes an implementation-specific
connection pooling module that manages a cache of PooledConnection objects.
The DataSource object is typically implemented by the application server
as a
layer on top of the driver’s implementations of the
ConnectionPoolDataSource and PooledConnection interfaces.
â–  The DataSource.getConnection method calls
PooledConnection.getConnection to get a logical handle to an underlying
physical connection. The overhead of setting up a new physical connection is
incurred only if there are no existing connections available in the
connection pool.
When a new physical connection is needed, the connection pool manager
will call
the ConnectionPoolDataSource method getPooledConnection to create
one. The work to manage the physical connection is delegated to the
PooledConnection object.
â–  The Connection.close method closes the logical handle, but the physical
connection is maintained. The connection pool manager is notified that the
underlying PooledConnection object is now available for reuse. If the
application attempts to reuse the logical handle, the Connection
implementation
throws an SQLException.
â–  A single physical PooledConnection object may generate many logical
Connection objects during its lifetime. For a given PooledConnection object,
only the most recently produced logical Connection object will be valid. Any
previously existing Connection object is automatically closed when the
associated
PooledConnection.getConnection method is called. Listeners (connection
pool managers) are not notified in this case.
This gives the application server a way to take a connection away from a
client.
This is an unlikely scenario but may be useful if the application server
is trying
to force an orderly shutdown.
â–  A connection pool manager shuts down a physical connection by calling the
method PooledConnection.close. This method is typically called only in
certain circumstances: when the application server is undergoing an orderly
shutdown, when the connection cache is being re initialized, or when the
application server receives an event indicating that an unrecoverable
error has
occurred on the connection.
</quote>

<quote>
11.6.2 Closing a Pooled Statement
An application closes a pooled statement exactly the same way it closes
a non pooled
statement. Whether it is pooled or not, a statement that has been closed
is no longer
available for use by the application, and an attempt to reuse it will
cause an
exception to be thrown.
The following methods can close a pooled statement:
■ Statement.close — called by an application; if the statement is being
pooled,
closes the logical statement used by the application but does not close the
physical statement being pooled
■ Connection.close — called by an application
■ Non pooled connection — closes the physical connection and all statements
created by that connection. This is necessary because the garbage collection
mechanism is unable to detect when externally managed resources can be
released.
■ Pooled connection — closes the logical connection and the logical
statements it
returned but leaves open the underlying PooledConnection object and any
associated pooled statements
■ PooledConnection.close — called by the connection pool manager to close
the physical connection and the associated physical statements being
pooled by
the PooledConnection object
An application cannot directly close a physical statement that is being
pooled;
instead, this is done by the connection pool manager. The method
PooledConnection.close closes the connection and all of the statements
open on
a given connection, which releases the resources associated with those
statements.
</quote>

I think it can be summarized to:

close of logical connection => close of logical statement
close of physical connection => close of physical statement

Arne

 

[Lothar Kimmeringer]

The thing is, the
javadoc for the "close()" method says this:

"Releases this Connection object's database and JDBC resources
immediately instead of waiting for them to be automatically released.
"

The problem is that documentation and reality are not always the
same and if you have to solve the problem, that resources are
actually not closed you don't care what might be "correct" and
you close the reosurces by yourself to make sure.

JDBC-drivers are implemented by third-parties, so with every
new database you can run into different problems leading to
constructs like these.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Lew]

Clearly you exaggerate.

JDBC spec says:
[elided]

So they will be closed.

But actually I think it is good to do it explicit. Because
it makes it obvious that they are being closed also to those
that have not read the JDBC spec.

Readable code is better than short code.

It is also common to wish to close a statement but not the connection.
Likewise result sets but not statements. By maintaining the discipline of
closing statements and result sets explicitly, one avoids some risks:

- Modifying code that depended on closing the connection so that it is no
longer "about to close", and thus leaving the statements / result sets open
longer than intended.

- Deciding that one should just leave statements / result sets open as long as
the connection is, then attempting to scale for more clients or more
throughput but encountering artificial scarcity that impedes scalability.

- Having to spend thought and time to decide if one should tidy up the
statements and result sets, and then all over again when one refactors.

- The risks mentioned by Arne and Lothar.

- As above, but when someone else maintains the code.

 

[Arne Vajhøj]

The problem is that documentation and reality are not always the
same and if you have to solve the problem, that resources are
actually not closed you don't care what might be "correct" and
you close the reosurces by yourself to make sure.

JDBC-drivers are implemented by third-parties, so with every
new database you can run into different problems leading to
constructs like these.

It could happen.

But do you have any example of drivers not implementing it
correctly?

Arne

 

[Lothar Kimmeringer]

It could happen.

But do you have any example of drivers not implementing it
correctly?

I once had a customer running a DB/400 on an AS/400. Suddenly
our system repeatedly ran out of memory so I was sent there
to find out why that happened. It turned out that the AS/400
JDBC-driver was not freeing up internal cache-data and this
behavior started at the time when there was a release-update
on that AS/400-system. Updating the driver to the version that
was shipped together with the release-update-CDs fixed that
problem. I don't know if that effect wouldn't have occured
when all resources would have been closed by myself, but I
think it's an example for your question.

There are other examples, e.g. the MySQL-driver throwing an
exception when selecting a null-value from a timestamp/date-
column or the Adabas-driver (many years ago), transfering
zero to a real-type-column if the value is < 1 (leading to
funny currency-conversion results ;-)


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Kevin McMurtrie]

It could happen.

But do you have any example of drivers not implementing it
correctly?

Arne

Connection pools may not be able to close DB resources automatically.
Doing so requires proxying a lot of complicated JDBC calls and trying to
match the internal state of the driver. That's not easy since the
definition and lifespan of a resource varies by driver and whether or
not the connection is full-duplex.

It's best to have a try/finally to manually close resources. Even if
there's no pooling now, it may be a required feature eventually.
Pooling can improve small query performance by 3x in simple systems. In
some cases it's required to block lethal load surges.

 

[Arne Vajhøj]

Connection pools may not be able to close DB resources automatically.
Doing so requires proxying a lot of complicated JDBC calls and trying to
match the internal state of the driver. That's not easy since the
definition and lifespan of a resource varies by driver and whether or
not the connection is full-duplex.

????

The JDBC spec very clearly states what a pooled connection driver
has to do in this regard and it is rather simple to implement.

(see previous post that quotes the JDBC spec)

It's best to have a try/finally to manually close resources.

Everybody agrees that connections should be closed. The topic
is whether statements (and result sets) should be explicit closed
or not.

There are no difference in the specs regarding this for non pooled
and pooled connections.

Even if
there's no pooling now, it may be a required feature eventually.
Pooling can improve small query performance by 3x in simple systems.

With a sufficient broad definition of query performance absolutely
(it is connection establishment overhead that is removed).

Arne