How to release abandoned connections?

[krokodylowy]

Hi

How configure pool to release all idle and abandoned connections?

The maxIdleTime and PoolShrinkingTask cleans only availablePool but already obtained idle or abandoned connections are in accessiblePool i inaccessiblePool and these pools are not cleaned.
This situation can occur in code without connection.close.
It seems to me that classic pools like TomcatDbcp or Jboss pools can handle this situation.
For example Apache Commons use such concept as AbandonedTrace, RemoveAbandonedOnMaintenance
and org.apache.commons.pool2.impl.GenericObjectPool.removeAbandoned

/**
* Recover abandoned objects which have been checked out but
* not used since longer than the removeAbandonedTimeout.
*
* @param ac The configuration to use to identify abandoned objects
*/
private void removeAbandoned(AbandonedConfig ac)

Of course it has a little impact on performance but server restart is not required if problem exist and can be gracefully logged..

[lorban]

Connections that were not closed aren't idle but leaked, this explains why the PoolShrinkingTask does not recover them.

It certainly would be possible to add an extra task with an extra config setting to claim back unclosed connections after a configured timeout but from memory, that used to have ill side-effects in the JDBC drivers, at least when I tried it years ago. Maybe the situation is different now.

You could try implementing that feature yourself and see how it ends up working.

[krokodylowy]

True.
Easiest way to fix is a patch of XaPool.expireOrCloseStatefulHolders() method
with additional foraeach on accessiblePool and inaccessiblePool and new
ExpireStatefulHolder with test like Math.min(expirationTime, endOfLife).

Similar solution is in commons-pool2 http://programtalk.com/vs/commons-pool/src/main/java/org/apache/commons/pool2/impl/GenericObjectPool.java removeAbandoned method use lastUsedTime/lastBorrowTime.

BTW
Bitronix expose XaPool.reset() to jmx but this method do nothing in above case. Most of people expect hard reset of whole pool. Currently it clean's only available connections.

[lorban]

I wouldn't conflate pool shrinking with leaked connections handling in the same task, this IMHO is looking for trouble, see below why.

Regarding reset(), I cannot remember all the details as that was years ago but I think I started working on exactly what you're looking for but gave up because, again from memory, about half the databases / JDBC drivers BTM supports were corrupting their internal state in an unrecoverable way when a connection with an in-flight XA transaction was forcibly rolled back and/or closed.

If you're going to implement that, I would strongly advise you to disable it by default unless you want to test that Oracle, DB2, Informix, Sybase, Firebird, Derby, SQL Server and maybe other databases I forgot about do work the way they should with multiple versions of their JDBC drivers.