Connection is not returned to the pool if connection.getTransactionIsolation() throws an exception about datasource-proxy HOT 1 CLOSED

datasource-proxy implements the JDBC API. In general, it is the caller's responsibility to perform the necessary clean-up operations when an error is thrown. However, it is a bit tricky in this case.

TL;DR, I believe it is an issue with Tomcat JDBC connection pool, as it is not invalidating its managed connection when an error occurred.

From the caller's perspective (spring JdbcTemplate/DataSourceUtils#getConnection), the operation to get a connection ended up as CannotGetJdbcConnectionException/SQLException. The caller considers this is a failure to retrieve a connection. This failure does not trigger any cleanup operations on the JDBC API because it does not have a connection object.

At the datasource-proxy level, the ProxyDataSource#getConnection performs two operations - DataSource#getConnection and Connection#getTransactionIsolation. The former succeeded, but the latter failed, so the original ProxyDataSource#getConnection call fails with SQLException. At this level, since ProxyDataSource does not know the underlying datasource(in this case, tomcat jdbc pool), it does not(cannot) do anything. It considers simply the underlying datasource/connection operation has failed and propagates the error to the caller.

At the tomcat jdbc pool level, the initial DataSource#getConnection operation succeeds and returns a pooled connection. However, the subsequent Connection#getTransactionIsolation operation fails. When an operation on a managed connection fails, the connection pool should invalidate the connection. Based on your observations, it seems that the pool is not doing so, resulting in the behavior you have described.

From the perspective of the JDBC API, when there is a failure in either DataSource#getConnection or Connection#getTransactionIsolation, there is no clean-up action required. It is up to either the caller or the actual JDBC implementor (in this case, tomcat jdbc pool) to respond to the failure.

In my opinion, tomcat jdbc pool needs to react to failures from its managed connections and ensure the stability of the connection pool.

from datasource-proxy.