Search code examples
javajdbcapache-commons-dbcp

DBCP returns closed connections

We are seeing situations where our database connection from org.apache.commons.dbcp.BasicDataSource is dying with socket write errors:

com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error

All subsequent attempts to write to the connection fail, of course:

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

After updating the code to catch such exceptions and request a new connection when it occurs, it failed again. Am I correct in suspecting that calling DataSource#getConnection() is not actually giving a new connection each time it is called? Isn't it just reusing the existing connection, which is closed?

If I am correct, what is the right way to throw away the old connection and request a new one?

EDIT: Here's a more succint version of what I'd like to know:

Connection c1, c2;
c1 = DatabaseManager.getConnection();
// c1.close() not called
c2 = DatabaseManager.getConnection();

Is "c1 == c2" a true statement? Or have two connections been allocated? And if it's the latter, would code like this represent a "connection pool leak":

Connection c1;
c1 = DatabaseManager.getConnection();
// c1.close() not called
c1 = DatabaseManager.getConnection();
Solution

  • The pooled connection has been closed by the DB. That can mean 2 things:

    1. The connection pool holds connections open for too long.
    2. The DB closes connections after a too short time.

    In theory, increasing/decreasing the timeout on either sides to align it should fix the problem.

    On DBCP, your best bet is to validate connections before returning by a testOnBorrow=true and a validationQuery setting, e.g. SELECT 1. You can find configuration options in the Tomcat JDBC data sources documentation.

    Update as per your update:

    Here's a more succint version of what I'd like to know:

    Connection c1, c2;
c1 = DatabaseManager.getConnection();
// c1.close() not called
c2 = DatabaseManager.getConnection();

    Is "c1 == c2" a true statement? Or have two connections been allocated?

    It are two distinct connections. Only if you call c1.close() then there's a reasonable chance that c2 returns the same connection.

    And if it's the latter, would code like this represent a "connection pool leak":

    Connection c1;
c1 = DatabaseManager.getConnection();
// c1.close() not called
c1 = DatabaseManager.getConnection();

    Yes, definitely it will leak the first connection as it's never been returned to the pool. You should always close all DB resources in the shortest possible scope in a try-finally block. A bit decent connection pool is however configureable to reap abandoned connections, but this should definitely not be used as "workaround".