After closing connection also connection is unavailable from data connection pool

Question

I am getting Unable to acquire connection when there are active thread who has already done DB work and waiting for some other API calls.

This is flow and issue.

Request come and make DB select call. After getting data from DB, we make some API call to get some other data. If for some reason API call gets delay and at same time another thread come for flow then datasource timed out for getConnection for new request.

Original implementation is on Spring JPA, then I tried with JDBCTemplate and even old school code as getConnection, prepareStatement and then close one by one but every time same issue. If thread is busy then getconnection doesn't work.

In my webapp, I am using jdbc pool with 70 connection in production but in local using only 2 connection to reproduce issue. So it work fine with 2 request at same time, but if I make 3rd request while 2 are still in process, it throws error.

I am unable to understand, if I make connection close or release then why still datasource is not giving the connection.

Below is sample code by keeping all control by my hand.

try{
LOGGER.debug("Trying to get connection");
con = datasource.getConnection();
LOGGER.debug("Trying to prepareStatement");
ps = con.prepareStatement("select * from table where itemnumber=?");
ps.setString(1, itemnumber);
LOGGER.debug("Trying to executeQuery");
ResultSet rs = ps.executeQuery();
while(rs.next()) {
    ufiles.add(rowMap.mapRow(rs, 0));
}


} catch (Exception e) {
        LOGGER.error("Error in execution ",e);
    } finally {

        try {
            if(ps!=null && !ps.isClosed()) {
                ps.close();
            }

            if(con!=null && !con.isClosed()) {
                con.close();
            }
        } catch (Exception e) {
            LOGGER.error("Error in closing connection ",e);
        }

    }

Connection pool XML

<dataSource id="STREAMAPP" jndiName="jdbc/APPDB" type="javax.sql.ConnectionPoolDataSource">
        <jdbcDriver libraryRef="ORACLEDRIVERLIB" />
        <connectionManager connectionTimeout="10s" minPoolSize="1" maxPoolSize="2" maxIdleTime="30s" agedTimeout="7200s" />
</dataSource>

Application logs

Trying to get connection
Trying to prepareStatement
Trying to executeQuery

Converted java object gets return and then we make API calls in another class and methods. Meanwhile if another request come then after waiting for 10 seconds getConnections gets timeout.

Trying to get connection
Error in execution 
java.sql.SQLTransientConnectionException: Connection not available, Timed out waiting for 10000
    at com.ibm.ws.rsadapter.AdapterUtil.toSQLException(AdapterUtil.java:809)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:152)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:116)

I highly recommend switching your code to try-with-resources. In your current code a failure to close ps will skip closing con, leaking that connection. In any case, I'm not sure what you consider to be the problem. You say two requests are busy, so the third one fails after a timeout of 10 seconds: that is expected if you have a pool with size 2 (and the other 2 requests don't complete and return their connection to the pool before the timeout expires): all connections are in use, so there is no connection available for the third request. — Mark Rotteveel
@mark, when I say 2 threads are busy, they are done with db call. con.close is also executed and they are making some other rest api call in which they are waiting for response. — Parivesh Jain
Some frameworks will add transactional behaviour for the duration of a request: a connection once obtained will not be returned until the connection ends, and attempts to get a 'new' connection in the same request will then just return the same connection. Possibly that is happening here as well. — Mark Rotteveel

Parivesh Jain Parivesh Jain · Accepted Answer · 2020-05-23T21:06:32

After some more research all pointers leads to GC and connection pool implementation on server. I didn't find any solution in config so at then end I split the DAO call and API call in two inbound request.

Consumer (UI) make 1st call to get data from DB. then after successful response UI make another API to call to invoke rest of the flow by passing response received from 1st(from DB).

So as pool for actually releasing connection after thread get complete so after 1st request from UI, it immediately make connection available to next request.

By split that in two, now even I can make my app running with just one connection and having 20-30 request at same time because my DB call take just 100ms and connection timeout is 10 secs. So with just 1 one connection i can handle roughly 90-100 request. :)

After closing connection also connection is unavailable from data connection pool

1 Answers