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

Discussion in 'Java' started by david.karr, Jul 18, 2009.

  1. david.karr

    david.karr Guest

    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, Jul 18, 2009
    #1
    1. Advertising

  2. david.karr

    David Karr Guest

    On Jul 17, 4:39 pm, "david.karr" <> wrote:
    > 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?
    David Karr, Jul 18, 2009
    #2
    1. Advertising

  3. david.karr

    Arne Vajhøj Guest

    david.karr wrote:
    > 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, Jul 18, 2009
    #3
  4. David Karr wrote:
    > On Jul 17, 4:39 pm, "david.karr" <> wrote:
    >> 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?


    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
    Arne Vajhøj, Jul 18, 2009
    #4
  5. Re: Will calling "close()" on a "java.sql.Connection" really close associated resources?

    david.karr wrote:

    > 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:
    PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

    Always remember: The answer is forty-two, there can only be wrong
    questions!
    Lothar Kimmeringer, Jul 18, 2009
    #5
  6. david.karr

    Lew Guest

    david.karr wrote:
    >> In billions of lines of code using JDBC connections, I constantly see


    Clearly you exaggerate.

    >> 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?


    Arne Vajhøj wrote:
    > 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.

    --
    Lew
    Lew, Jul 18, 2009
    #6
  7. david.karr

    Arne Vajhøj Guest

    Lothar Kimmeringer wrote:
    > david.karr wrote:
    >> 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.


    It could happen.

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

    Arne
    Arne Vajhøj, Jul 18, 2009
    #7
  8. Re: Will calling "close()" on a "java.sql.Connection" really close associated resources?

    Arne Vajhøj wrote:

    > Lothar Kimmeringer wrote:
    >> 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?


    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:
    PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

    Always remember: The answer is forty-two, there can only be wrong
    questions!
    Lothar Kimmeringer, Jul 19, 2009
    #8
  9. Re: Will calling "close()" on a "java.sql.Connection" really close associated resources?

    In article <4a612f53$0$48234$>,
    Arne Vajhøj <> wrote:

    > Lothar Kimmeringer wrote:
    > > david.karr wrote:
    > >> 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.

    >
    > 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.

    --
    I will not see your reply if you use Google.
    Kevin McMurtrie, Jul 19, 2009
    #9
  10. david.karr

    Arne Vajhøj Guest

    Kevin McMurtrie wrote:
    > In article <4a612f53$0$48234$>,
    > Arne Vajhøj <> wrote:
    >> Lothar Kimmeringer wrote:
    >>> david.karr wrote:
    >>>> 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.

    >> It could happen.
    >>
    >> But do you have any example of drivers not implementing it
    >> correctly?

    >
    > 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
    Arne Vajhøj, Jul 19, 2009
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Amir
    Replies:
    3
    Views:
    586
  2. HalcyonWild
    Replies:
    2
    Views:
    913
    HalcyonWild
    Dec 19, 2005
  3. Russell E. Owen
    Replies:
    0
    Views:
    693
    Russell E. Owen
    Sep 8, 2006
  4. Dani
    Replies:
    3
    Views:
    470
  5. Iñaki Baz Castillo
    Replies:
    7
    Views:
    826
    Iñaki Baz Castillo
    Jan 12, 2010
Loading...

Share This Page