1 2 Previous Next 16 Replies Latest reply on Aug 1, 2008 10:27 AM by adrian.brock

    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

        • 1. Re: Handling fatal JDBC connection exceptions
          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 .

          • 2. Re: Handling fatal JDBC connection exceptions
            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

            • 3. Re: Handling fatal JDBC connection exceptions
              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 .



              • 4. Re: Handling fatal JDBC connection exceptions
                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.

                • 5. Re: Handling fatal JDBC connection exceptions
                  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 .





                  • 6. Re: Handling fatal JDBC connection exceptions
                    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.

                    • 7. Re: Handling fatal JDBC connection exceptions
                      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 !

                      • 8. Re: Handling fatal JDBC connection exceptions
                        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.

                        • 9. Re: Handling fatal JDBC connection exceptions
                          cbredesen
                          • 10. Re: Handling fatal JDBC connection exceptions
                            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.

                            • 11. Re: Handling fatal JDBC connection exceptions

                               

                              "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.

                              • 12. Re: Handling fatal JDBC connection exceptions
                                jesper.pedersen

                                I agree with Adrian.

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

                                • 13. Re: Handling fatal JDBC connection exceptions
                                  vickyk

                                   

                                  "adrian@jboss.org" wrote:

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

                                  How about Failing Connection + Idle Connections here ?


                                  • 14. Re: Handling fatal JDBC connection exceptions

                                     

                                    "vickyk" wrote:
                                    "adrian@jboss.org" 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. :-)

                                    1 2 Previous Next