Search code examples
javahibernatedatabase-connectionc3p0

Hibernate/C3P0: Is there a way to check if the connection is down in real time?

I have an application which we'll refer to as App.class that checks the database every second for real time events.

I encountered an instance where the database was down and App.class kept on retrying and throwing an exception.

What I want to achieve is to determine whether a valid connection is available or not in real time.

I can get the provider by:

C3P0ConnectionProvider provider = (C3P0ConnectionProvider) sessionFactoryImpl.getConnectionProvider()

and I could check if the connection is closed by doing: provider.getConnection().isClosed().

However, if the database is down and I try to invoke provider.getConnection(), App.class hangs for a bit (probably trying to look for a connection), then throws an exception.

I was wondering if there's an easy way to determine if a connection exists instead of just catching the error/exception when it happens.

Solution

  • So, c3p0 cannot tell you whether a database is "down". Metaphysically, epistemologically, it just has no access to that information. It observes only shadows of the DBMS on the walls of the network.

    There are a few thing c3p0 can do for you though:

    1. It can test Connections before giving them to you
    2. It can not leave you hanging
    3. You can ask it in advance whether there are likely to be Connections available without a wait.

    It sounds like you have already asked c3p0 to test Connections for you on checkout, either via hibernate.c3p0.validate or c3p0.testConnectionOnCheckout. If you hadn't, you wouldn't see your app hang on getConnection() when the database was down, you'd just see failures when you tried to use the Connection. (You might be testing Connections on check-in and idle. More on that possibility below.)

    Rather than putting up with a hang, you could set the config parameter c3p0.checkoutTimeout, so that if c3p0 doesn't have a good Connection to hand you relatively quickly, it sets you free with an Exception.

    Ideally, you might prefer to just know in advance whether a good Connection is available before trying to check it out and hanging even briefly. You can ask c3p0 whether Connections are likely to be immediately available, via methods on c3p0's PooledDataSource interface. If you had access to a PooledDataSource pds, you'd just do something like

    int idle = pds.getNumIdleConnectionsDefaultUser();

    and could know whether a call to getConnection() would be likely to success quickly if there are at least a few idle Connections. (You can't have a guarantee, because c3p0 and hibernate are very asynchronous, so between your check and your checkout attempt, things may change. And no, you can't try to use a synchronized block or something to make these things atomic, c3p0's pools do their locking internally, not at the outer DataSource level.)

    But even if there are idle Connections, you won't know if they are good until you test them, and if the DBMS is down, you'll hang as c3p0 tries all of its idle Connections, rejects them, and then waits in vain to try to acquire Connections. What you'd like is for c3p0 to have pre-tested the Connections, so that if none are good, none appear idle.

    You can't get that perfectly, but you can come close by replacing connection testing on checkout with connection testing on checking combined with frequent tests of idle Connections. See c3p0's docs for details on how. If you do this, bad Connections will quickly be evicted from the pool, and the idle Connection count will come to approximate the count of idle good Connections. So, you'll have some, albeit imperfect, confidence that, if you see idle Connections, the DBMS is up and a call to getConnection() will succeed quickly. Back that up with a checkoutTimeout for the period when the DBMS has just crashed, but c3p0 hasn't yet detected it, and you'll be pretty close to where you want to be.

    Oh, to get the PooledDataSource from a hibernate Connection provider, looking at the source code, should be something like...

    import com.mchange.v2.c3p0.PooledDataSource;

PooledDataSource pds = provider.unwrap(PooledDataSource.class);
int idle = pds.getNumIdleConnectionsDefaultUser();

    I haven't tried this code, and am writing this in a terrible rush (sorry!), but that should work, if it is worth this much trouble. I think most users just limit the time cost of a database outage using c3p0.checkoutTimeout without going to the trouble to get the PooledDataSource to ask about idle Connections. But if you need that, you can get it.