5

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();
skaffman
  • 398,947
  • 96
  • 818
  • 769
Jim Burnell
  • 948
  • 3
  • 9
  • 21

2 Answers2

11

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

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • I assumed that the connection was closed because of the previously mentioned socket write error? Once it is closed, though, does calling getConnection() get a new connection? – Jim Burnell Sep 08 '11 at 15:52
  • No, closing a pooled connection will just return it to the pool (that's why you should ALWAYS close connections in `finally`, else pool may run out of connections). Remnant of handling the broken connections is up to the pool which you should control by configuration. You should however realize that DBCP is a poorly implemented pool. See also http://stackoverflow.com/questions/520585/connection-pooling-options-with-jdbc-dbcp-vs-c3p0/3481821#3481821 – BalusC Sep 08 '11 at 15:54
  • But what if close() is never called? What if the code just keeps calling getConnection()? does it still re-use the old, closed connection, or does it get a new connection from the pool? – Jim Burnell Sep 08 '11 at 16:00
  • 2
    If you never call `Connection#close()` (and `Statement#close()` and `ResultSet#close()`), then you've bigger problems with your application. Closing a pooled connection will return it to the pool. Not doing so will accumulate the connections until DB runs out of them (which in turn may force closing of older connections to reclaim them). See also http://stackoverflow.com/questions/5602772/how-often-should-statement-and-resultset-objects-be-closed-in-jdbc and http://stackoverflow.com/questions/2313197/jdbc-mysql-connection-pooling-practices/ – BalusC Sep 08 '11 at 16:01
  • Thanks for the information. If you don't mind, could I ask one more clarifying question? 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(); Sorry for all the questions – Jim Burnell Sep 08 '11 at 17:23
  • Ugh, that didn't format well. I'll add it to the original question – Jim Burnell Sep 08 '11 at 17:30
  • I updated the answer. I hope that you now understand that resource leaking is considered a serious programming bug which needs to be fixed asap. The same story applies to all other references pointing to external resources, such as `InputStream` and `OutputStream`. – BalusC Sep 08 '11 at 18:01
  • For those migrating from tomcat 6 to 7: The default value for `testOnBorrow` changed from **true** to **false**! This is a significant change in the default behaviour, but it is not explicitely mentioned in the documentation... – yamass Jun 18 '14 at 11:10
1

I was also facing the same issues. then i realized that i was making multiple asynchronous ajax calls that was causing the issue.

I serialized the calls and it fixed the issue.

Sunder
  • 61
  • 5