Handling fatal JDBC connection exceptions

[cbredesen]

Gents,

Would it make sense to offer an option to destroy all connections in the pool upon encountering a fatal exception? Currently a catastrophic failure of the database can cause all connections in the pool to become useless. Without a VCC (or SQL) configured, this means that each connection in the pool will be used one further time before it throws a SQLException which, when determined as fatal, will cause the connection to be destroyed.

If we offered such a setting, we could avoid this scenario without even having to do proactive validation. The setting would of course be optional and default to the current behavior.

Thoughts?

-Chris

[vickyk]

"cbredesen" wrote:

Would it make sense to offer an option to destroy all connections in the pool upon encountering a fatal exception?

Destroying the stale connection upon receiving the fatal exception Or destroying the stale connections when getting them from the Pool will effectively have the same effect.

Once all the connections in a pool become stale , the subsequent request for the connection will basically destroy all the connections .
So the connections are destroyed when they are being used , I am not able to understand what benefit it provides.

"cbredesen" wrote:

If we offered such a setting, we could avoid this scenario without even having to do proactive validation

I don't get you here .

[cbredesen]

Destroying the stale connection upon receiving the fatal exception Or destroying the stale connections when getting them from the Pool will effectively have the same effect.

No, it would not. Currently if you have no valid connection checker (or SQL), a fatal exception must be encountered before each individual connection is destroyed.

Consider a pool of 100 connections against a database that was restarted. All 100 connections are now invalid, but we don't know until we attempt to use one. 100 failed transactions later, we have a clean pool.

What I'm proposing (based on interaction with a WAS user) is a setting which allows a single fatal exception to kill all connections in the pool because we know they aren't going to be usable. This way you limit yourself to 1 (or very few) failed transactions. It's just another way of doing proactive pool management without doing background validation (or validate on match).

-Chris

[vickyk]

"cbredesen" wrote:

Consider a pool of 100 connections against a database that was restarted. All 100 connections are now invalid, but we don't know until we attempt to use one. 100 failed transactions later, we have a clean pool.

I don't think you will have a failed TX , the pool should transparently destroy all the stale connections and create a new one for the application that asks for it .

This will happen if you have configured the check-valid-connection .
All this should be transparent to application .

[cbredesen]

If you read my original posting, this scenario deals with pools that have (for whatever reason) not configured a valid connection checker or sql.

[vickyk]

"cbredesen" wrote:
If you read my original posting, this scenario deals with pools that have (for whatever reason) not configured a valid connection checker or sql.

It was not that clear to me ;(
okay now I understand that you want to get rid of stale connections even if you have not configured check-valid-connection.
Yes looks an good idea .

[cbredesen]

Now you're on board :) As you pointed out offline, this is a feature of WAS today:

http://publib.boulder.ibm.com/infocenter/wasinfo/v5r1//index.jsp?topic=/com.ibm.websphere.exp.doc/info/exp/ae/udat_conpoolset.html

See:

Purge Policy

Specifies how to purge connections when a stale connection or fatal connection error is detected.

Valid values are EntirePool and FailingConnectionOnly.

Love to hear some input from the core devs on this. I could maybe take a stab at implementing it.

[jesper.pedersen]

Sounds like a good idea - if you want to take a look see the org.jboss.resource.adapter.jdbc package.

That being said - we are about to start a new implementation of the JCA container where we will keep this feature request in mind.

Thanks !

[jimm]

Why not have 3 settings?
EntirePool, FailedConnectionOnly, AND UnusedConnectionsOnly

What is the point of closing in-use connections since the connection would throw an exception on the next call anyway if it is bad? There is no added benefit to closing the in-use connection but there is a potential negative if for some reason the connection is still good.

[cbredesen]

https://jira.jboss.org/jira/browse/JBAS-5824

[jesper.pedersen]

Jim, we can implement different policies on the pool in the new JCA implementation.

Then it is just to add an implementation for the specified interface and configure it.

The current JCA implementation uses ExceptionSorter and ValidConnectionChecker as described in the documentation.

[adrian.brock]

"jesper.pedersen" wrote:
Sounds like a good idea - if you want to take a look see the org.jboss.resource.adapter.jdbc package.

That being said - we are about to start a new implementation of the JCA container where we will keep this feature request in mind.
Thanks !

This would be a policy on the connection manager not the adapter.
It would be handled in BaseConnectionManager2::connectionErrorOccurred()
by invoking the pool.

Like Jim, I'd propose three options
* Failing connections only - what we do now
* Idle connections - i.e. just do pool.flush()
* All connections - invoke returnManagedConnection(true) on all the connections in the pool(s) regardless of whether they are in use or not.

I don't like the last one because unless it is a real database failure rather than
a transient failure on one connection, you're failing far more transactions than you should.
It would be better to let the ExceptionSorter trap whether there are real failures
to those in use connections.

[jesper.pedersen]

I agree with Adrian.

The ExceptionSorter and ValidConnectionChecker functionality we have today provide an excellent starting point in this area.

[vickyk]

* Idle connections - i.e. just do pool.flush()

How about Failing Connection + Idle Connections here ?

[adrian.brock]

"vickyk" wrote:

* Idle connections - i.e. just do pool.flush()

How about Failing Connection + Idle Connections here ?

Destroying the failed connection along with the idle connections is assumed,
anything else would be illogical. :-)