When do you validate connections in a connection pool?

Question

I'm implementing a connection pool in Java (i.e. a pool of java.sql.Connections). When should I check that connections are still valid? I don't want to do it before I lend them. Should I do it when they are returned? Every time? Is there a clever way to schedule checking?

Answer 1

Would this be influenced by how busy and fully-utilized your connection-pool is going to be? I would guess these 2 constraints should be met -

1. Dont ever deliver an invalid connection object to client
2. Dont take a huge amount of time to deliver a connection (i.e. dont start setting up a connection when they are at your door asking to borrow one).

So some measure of eagerness is good and if your usage patterns say frequent setup and teardown (from the client perspective), you will need to have an eager algorithm.

Certainly, two events that you can anchor to are the 1) new connection request 2)Close connection request.

Upon new connection request - Check for validity (maybe redundant). If the pool is fully used or threshold is exceeded, then provision new connections into the pool (i.e. expand if needed).

Upon close connection request - check the closed connection for validity, Replenish the pool if needed.

Your algorithm could get more eager than above, certainly based on your specific needs.

Answer 2

The answer should only be given after you get some metrics about how many connections will be used and the how many your system can handle.

Having that metrics, you can decide what is the best time to check and try to free some resources.

Answer 3

I think the answer to your question depends on how much effort you want to put into your connection pool.

The simple way will be to do what you don't want to do which is to check the connection when it is requested, I expect because you don't want to delay the requesting thread when creating a new connection.

You could have a background thread that tests the connections and recreates them as needed. I think you should always test before returning and if it is dead then grab another from the pool and schedule a new connection to replace it.

I take it you are writing your own for a good reason as there are many very good open source pools you can use like Jakarta DBCP & C3P0 and many more

Shawn

Answer 4

Have a surveillance thread that ensures that after X seconds in the pool without being used, a dummy request is issued (a heartbeat) like "select 1 from dual" wiht oracle. This should keep them alive.

Answer 5

A connection can 'go bad' at any time. Consequently, no matter when a connection is checked, even if it is checked just before being handed out from the pool, there is always a possibility that the user will try to use a bad connection.

The connection pooling projects I've worked on have chosen to deal with this uncertainty by not dealing with it. They assume the connection in the pool is good unless they have concrete evidence otherwise, meaning some kind of exception flows back from a user of the connection.

Some kinds of errors and exceptions can be know to mean that every connection in the pool is now invalidated.

I've seen some special case environments where this kind of approach is not ideal. In those cases, other solutions can be used. For instance, maybe the server that you're program is pooling connections for is rebooted every morning at 2 AM. In that case, a timer can run once a day after the server restarts (at 3 AM maybe) and test & likely invalidate all the connections in the pool.

I would suggest, however, that you let each special case dictate the solution as it is required.