CODEWITHSUNDEEP
javajdbcthreadpoolconnection-poolingpool
mindiga
mindiga

Reputation: 539

Is there a resource (connection) pool that automatically returns borrowed resources (connections) when they are not needed?

Is there a resource (connection) pool that automatically of the borrowed resources (connections) when they are not needed?

I frequently encountered situations where Java software has bugs due to pooled connections not being returned (closed). Mostly built by external vendors, where I provide the long term maintenance services. I would like for the pool to automatically return the connection if it is idle (not used) for the specified amount of time (seconds). As long as the connection is used by the same thread, it should remain allocated to that thread, but if the thread has not made use of the connection for more than a few (5) seconds since the last call completed, the connection should get automatically returned to the pool.

I have done the prototype on a commercial web server with commercial RDBMS and the performance is stellar in our production. Since the pooled connections are sticky to the thread and the web server already uses thread pools, there is minimal switching of connections between different threads. Hence, performance is better than when using the traditional borrow and return approach, as once the connection is borrowed by a thread, it usually stays allocated to the same thread for quite a while, as it is frequently being used.

Apart from JDBC connections, this approach has worked well for other heavy pooled resources, such as JSON parsers, HTTP connections or Date Formatters. If you have fewer connections in the pool that parallel threads on the server, there isn't much value, as you won't be able to run that many threads in parallel anyway, since there are not enough connections. So why not have as many connections as many parallel threads.

Some existing pools can release connection after a period of inactivity, but the servlet that does not release its connections, would quickly exhaust all available connections, if it encounters the right conditions and it gets a large number of requests. With the new thread-sticky connections that is not the case, as the thread that does not release the connection, get the same connection again on borrow.

My question, what would be the main reason not to use this approach, as I find it a much better performing on servers that are reusing threads and maintaining thread pools.

Upvotes: 1

Views: 782

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 339482

I frequently encountered situations where Java software has bugs due to pooled connections not being returned (closed).

This is a problem with your own app code accessing the database, not a problem with your connection pool.

👉 Every client app is responsible for closing the JDBC database connection.

The try-with-resources syntax on Java was invented to make this easy. You can list resources within the parentheses if they implement the AutoCloseable interface. Such classes include java.sql.Connection, Statement, ResultSet, and so on.

DataSource dataSource = … 
…
try
(
   Connection conn = dataSource.getConnection() ;
)
{
    … use your database connection 
}
// At this point your database connection has been closed, whether having completed its work successfully or whether an exception was thrown. 
// If the connection came from a pool, the connection will actually be returned to the pool rather than actually closed, per policies of that particular pool implementation.

You asked:

if the thread has not made use of the connection for more than a few (5) seconds since the last call completed, the connection should get automatically returned to the pool.

This would be unwise. You would simply be putting wallpaper over a hole in the wall, masking the real problem rather than solving it. You should be auto-closing connections as seen above. And then investigate and resolve any cases of code hanging and connections not being returned. With your approach you would be ignoring such faults. And any temporarily delayed code might then resume to use a connection in use by other code.

By the way, asking for software recommendations is explicitly off-topic for Stack Overflow. For that, see the sister site Software Recommendations Stack Exchange.

Upvotes: 0

Related Questions