Can a JBoss JDBC connection be invalidated so that it doesn't get resupplied from the connection pool?

Question

I have a circumstance where a JDBC connection places the Oracle session to which it is attached into a particular state (i.e. DBMS_FLASHBACK enabled mode). It's possible for the exit of this mode to fail (at least theoretically) which means that the session is left in the state erroneously. In this case, the connection can be returned to the pool, and obtained by another thread with the Oracle session still in DBMS_FLASHBACK enabled mode.

I have proved that would actually happen. (JBoss 4.2.1)

What would be ideal would be is to catch the SQLException when the mode exit fails, and mark the connection as "bad" so that once it was returned to the pool, JBoss would destroy the connection and create a new one.

But I can't find any way to mark the connection for immediate destruction. Does anyone know of a way?

Answer 1

Its a good question, and I don't know the complete answer, but some avenues to investigate would be to start with the JBoss failover mechanism, where it tests the validity of the connection. The documentation for that is here. Then in the SQL that tests validity, if something could be put in that would fail if the connection is in the DBMS_FLASHBACK enabled mode, that should get JBoss to discard the connection. It probably tests on the next request for a connection, not when it is returned to the pool, although that should be acceptable.

Answer 2

Your Oracle database connection configuration should contain an exception sorter:

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

This attempts to determine when an exception occurs if the connection can be reused, or must be disconnected. This is on a best efforts basis, and doesn't work in every case. My preference for production installs is to mark all exceptions as fatal. To do this, simply set your exception sorter to org.jboss.resource.adapter.jdbc.GenericExceptionSorter.