0

I'm using c3p0 ComboPooledDataSource to pool database connections (to an Oracle 10g database). I'm having problems with handling database connectivity outages.

If there is no connection to the database when the first connection is being acquired, the checkout timeout fires and it fails as expected.

However, if a connection outage occurs after one or more connections have been acquired and are already in the connection pool, calling getConnection() just hangs. No exception is thrown. I'm guessing this is because it's trying to use a pooled connection but that connection is no longer alive.

Is there a way to check whether the connection is valid before trying to use it? I tried setting testConnectionOnCheckout=true but it doesn't seem to have any effect.

This is the thread dump

C3P0PooledConnectionPoolManager[identityToken->2rvy8f8x1oujxrx1majv5s|be41d5]- HelperThread-#2" daemon prio=6 tid=0x0307a800 nid=0x840 in Object.wait() [0x03d1f000] java.lang.Thread.State: TIMED_WAITING (on object monitor) at java.lang.Object.wait(Native Method) - waiting on <0x28387f88> (a com.mchange.v2.async.ThreadPoolAsynchronousRunner) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:635) - locked <0x28387f88> (a com.mchange.v2.async.ThreadPoolAsynchronousRunner)

Locked ownable synchronizers: - None

Sam
  • 99
  • 1
  • 2
  • 8
  • can you dump stack traces during the hang to see what specifically clients are hung on? thanks. there is no condition under which clients should hang indefinitely on getConnection() if a checkoutTimout is set. – Steve Waldman Oct 21 '13 at 12:14
  • Thanks for the reply, I added thread dump. The checkoutTimeout works fine if there's no DB connection when the first connection is acquired. The hang takes place only when I try to checkout a 'dead' connection. – Sam Oct 21 '13 at 14:13
  • hi. that's a c3p0-internal helper Thread, waiting on tasks. it's fine. what we need are dumps of frozen client Threads. thanks! – Steve Waldman Oct 21 '13 at 17:37

1 Answers1

1

I encountered the same issue. In my case, it was caused by the JDBC driver not being setup to timeout on socket failures. I made the following additions to my C3P0 ComboPooledDataSource configuration:

cpds = new ComboPooledDataSource();
...

//--------------------------------------------------------------------------------------
// NOTE: Once you decide to use cpds.setProperties() to set some connection properties,
//       all properties must be set, including user/password, otherwise an exception
//       will be thrown
Properties prop = new Properties();
prop.setProperty("oracle.net.CONNECT_TIMEOUT",
    Integer.toString(JDBC_CONNECTION_TIMEOUT_IN_MILLISECONDS));
prop.setProperty("oracle.jdbc.ReadTimeout",
    Integer.toString(JDBC_SOCKET_TIMEOUT_IN_MILLISECONDS));
prop.setProperty("user", username);
prop.setProperty("password", password);
cpds.setProperties(prop);
//--------------------------------------------------------------------------------------

...

The Oracle driver properties are applied when C3P0 creates the Connection object. These two properties in particular will cause an exception to be thrown if the socket connection is inactive for more than 30 seconds.

If you're not connecting to an Oracle database, there are similar properties for other JDBC drivers for other database vendors. Some of them are shown near the bottom of this page.

Jim Tough
  • 14,843
  • 23
  • 75
  • 96