Yesterday AWS's RDS went down -- and so did our database.
When this happened, C3P0 would try to get a database connection and would hang. I would obviously like my application to return an error page in these instances, rather than just waiting forever for a response.
Here's what the code looks like:
ComboPooledDataSource db = new ComboPooledDataSource();
...
Connection conn = db.getConnection();
How can set a timeout for getting a connection from c3p0's connection pool?
I thought checkoutTimeout() would be it -- but it's not. It is "the number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted." Since the pool in not exhausted (it is just unavailable) this does not apply.
I also thought setAcquireRetryAttempts and setAcquireIncrement would work -- but they do not since a connection doesn't fail, it just doesn't respond.
When I pulled the whole stack, this is where it stalls:
SocketInputStream.socketRead0(FileDescriptor, byte[], int, int, int) line: not available [native method]
SocketInputStream.read(byte[], int, int) line: 129
ReadAheadInputStream.fill(int) line: 113
ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(byte[], int, int) line: 160
ReadAheadInputStream.read(byte[], int, int) line: 188
MysqlIO.readFully(InputStream, byte[], int, int) line: 2428
MysqlIO.reuseAndReadPacket(Buffer, int) line: 2882
MysqlIO.reuseAndReadPacket(Buffer) line: 2871
MysqlIO.checkErrorPacket(int) line: 3414
MysqlIO.sendCommand(int, String, Buffer, boolean, String) line: 1936
MysqlIO.sqlQueryDirect(StatementImpl, String, String, Buffer, int, int, int, boolean, String, Field[]) line: 2060
JDBC4Connection(ConnectionImpl).execSQL(StatementImpl, String, int, Buffer, int, int, boolean, String, Field[], boolean) line: 2542
JDBC4PreparedStatement(PreparedStatement).executeInternal(int, Buffer, boolean, boolean, Field[], boolean) line: 1734
JDBC4PreparedStatement(PreparedStatement).executeQuery() line: 1885
NewProxyPreparedStatement.executeQuery() line: 76
C3P0PooledConnectionPoolManager.initializeAutomaticTestTable(String, DbAuth) line: 799
C3P0PooledConnectionPoolManager.createPooledConnectionPool(DbAuth) line: 696
C3P0PooledConnectionPoolManager.getPool(DbAuth) line: 257
C3P0PooledConnectionPoolManager.getPool() line: 271
ComboPooledDataSource(AbstractPoolBackedDataSource).getNumThreadsAwaitingCheckoutDefaultUser() line: 203
NativeMethodAccessorImpl.invoke0(Method, Object, Object[]) line: not available [native method]
NativeMethodAccessorImpl.invoke(Object, Object[]) line: 39
DelegatingMethodAccessorImpl.invoke(Object, Object[]) line: 25
Method.invoke(Object, Object...) line: 597
BeansUtils.extractAccessiblePropertiesToMap(Map, Object, Collection) line: 359
BeansUtils.appendPropNamesAndValues(StringBuffer, Object, Collection) line: 324
ComboPooledDataSource.toString() line: 539
ComboPooledDataSource(AbstractPoolBackedDataSource).getPoolManager() line: 462
ComboPooledDataSource(AbstractPoolBackedDataSource).getConnection() line: 128
When I googled "socketRead0 timeout" and "socketRead0 hang" -- I see a lot of problems but no real solutions.
Is there any way to force a timeout period here?
The issue is within MySQL's ReadAheadInputStream, which uses blocking read. Native socket got blocked and never(?) returns an error code. So the connection hangs too.
I do not see a way to handle it short of placing your code into a thread and join() to it with timeout. I do not believe though the problem justifies the complications: I hope Amazon will make the right conclusions from the downtime, and won't let it happen again.