[Commons-DBCP] Connections not being closed.

[Nathan Smith <na...@labpro2000.com>]

Hi all,
 
We have had a major problem over the last few weeks with respect to
connections not being closed. The problem started to occur after installing
our software for a new client. I suppose we hadn't tested the software as
well we should have, but we also trust and use a lot of software from ASF. 
 
The DBCP software was correctly keeping track of the numbers such as
minIdle, maxIdle, pool size etc. The problem was the connection, if deemed
to be unusable and therefore not placed back in the pool, was not being
closed. The number of users/connections available on the Database side
slowly became used up and the application unusable. I managed, after some
lengthy logging and debugging and profiling sessions to track down where
this was happening. I have since edited the source code for Commons-DBCP,
rebuilt the distribution and supplied it as part of a patch to our client. 

 
I was just wondering about some things that I hoped somebody might be able
to clear up.
 
1. Why is Commons-DBCP trying to reproduce the behaviour that a JDBC driver
should carry out? i.e. Having DelegatedStatement and DelegatedResultSet
objects hanging off a DelegatedConnection, which are then closed when the
Connection is closed. The code as it stands at the moment assumes the user
is not going close ResultSet's and Statement's in their own code.
NullPointerException's can be thrown also SQLException's. The implementation
of the JDBC driver we use throws an SQLException if a Statement or ResultSet
have already been closed. Thats OK the exceptions can be ignored, the
Connection can still be used.
 
 
Here are the changes I have made to the code:
NOTE: Theses changes fall to pieces when using a StatementPoolFactory.
 
DelegatedConnection.java
 
    public void close() throws SQLException
    {
        try {
            passivate();
        } catch(SQLException sqle) {
            throw sqle;
        }
        finally {
            if(_conn != null) {
                _conn.close();
            }
        }
    }

    protected void passivate() throws SQLException {
        log.debug("[DelegatingConnection.passivate] Connection (" + _conn +
')');
        try {
                    
            ...
        
                    if(set[i] != null) {
                        set[i].close();
                    }
                
            ...
                            
        }
        finally {
            _closed = true;
        }
    }
 
 

Nathan Smith
Software Developer
LabPro 2000 Limited
Phone: +64 4 586 6840
Facsimile: +64 4 586 6841
 <ma...@labpro2000.com> nathan@labpro2000.com
 <http://www.labpro2000.com/> www.labpro2000.com

Level 6, Riverside Tower on Daly, 15 Daly Street
Lower Hutt, New Zealand