8 Replies Latest reply on Aug 31, 2003 11:48 AM by Adrian Brock

    Destroy connection pool -- Bug or Design Feature

    mcoyne Newbie

      We have a test that a Java client via JBOSS JCA connection pool (JBoss version 3.2.1), calling an Oracle stored procedure performing a long trasaction (purposely). While the transaction (on the database server is running), we destroy the connection pool. The observation was (1) the transaction completely sucessfully (2) Java Client application gets error (3) servelt also get an error.
      Frome the database side, at the moment of destroying the pool, a connection is still active. Then when the [long running] stored prodedure finishes, the connection is gone.

      Question:
      1. What exactly destroy connection pool does? Waiting for a connection return to the pool and then destroy it?
      2. Jboss should inform the client application that it the transaction is successful. This isnot the case. Is this a bug or it works as design?

      Thanks in advance

        • 1. Re: Destroy connection pool -- Bug or Design Feature
          Adrian Brock Master

          How are you accessing the db?

          An SQLException falls under the Application Exception
          category. If you handle the Exception it is up to you to
          handle the error and through a suitable RuntimeException,
          e.g. EJBException or perform setRollbackOnly()

          I would be surprised if Oracle allowed the transation to
          commit in this state? It probably didn't regard it as an
          error?

          JBoss will only rollback if it sees an
          EJBException
          RemoteException
          RuntimeException (e.g. NullPointer)
          Error
          or you have voted rollback
          or if the transaction fails to commit.

          On the destroy, the connection is marked as detroyed
          but isn't fully destroyed until all references are released.
          For Oracle this happens at transaction end.

          Regards,
          Adrian

          • 2. Re: Destroy connection pool -- Bug or Design Feature
            mcoyne Newbie

            Dear Adrian:
            Thanks again. It takes a little time to answer your e-mail. We have tried to run our tests again to gather some information for you.

            WRT: How are you accessing the db?
            >>> We are using stored procedure. Tests are done on both JDBC and Oracle JDBC Thick Driver. Both tests show exactly same results

            WRT: An SQLException falls under the Application Exception
            category
            >>> There is absolutely no SQLException receives from the Java Client Application. (We have validated that this is the case.)

            WRT: I would be surprised if Oracle allowed the transation to commit in this state.

            >>> The stored procedure is written like this:

            Start

            statement 1
            commit;
            wait 10 Sec
            statement 2

            When the 'destroy' is at 'wait 10 sec' then looks like the statement 2 is not executing, indicating the transaction is abandoned while executing. --> This should behave as expected, shouldn't this?

            I think that it is still a bug that JBoss did not give exception to application. In this scenario, either way (successfull or non-successfull) application will close its connection. Therefore, JBOSS get the connection reference are released that leads Jboss to close the connection. Truth is, application does NOT know that its transaction was not done successfully.

            Thoughts?

            Thanks again.







            • 3. Re: Destroy connection pool -- Bug or Design Feature
              mcoyne Newbie

              One more little details:
              WRT: An SQLException falls under the Application Exception
              category
              >>> There is absolutely no SQLException receives from the Java Client Application. (We have validated that this is the case.)

              >>> However, when application tries to close the connection a java.lang.NullPointerException is getting --> looks like JBoss already 'destroy' or 'close' this connection ????

              • 4. Re: Destroy connection pool -- Bug or Design Feature
                Adrian Brock Master

                Your last problem has already been fixed.

                You didn't answer my question. I meant what are you doing
                inside jboss (how do you run the store procedure).
                JBoss know's nothing about what happens on the database.

                Regards,
                Adrian

                • 5. Re: Destroy connection pool -- Bug or Design Feature
                  mcoyne Newbie

                  Let me try again and hope I answer your question.
                  --Using JBoss mainly for its connection pooling. JBoss version is 3.2.1
                  --In this test, all business logic is in a stored procedure on Oracle db
                  --Using Callablestatement to invoke the stored procedure. (Is it what you're looking for?)

                  if connection!=null
                  connection.close();

                  Or
                  if !connection.isClosed()
                  connection.close();

                  Both statment issues java.lang.NullPointerException at the attempt to close it. This leads me to believe that the connection had been destroyed.

                  Thanks

                  • 6. Re: Destroy connection pool -- Bug or Design Feature
                    Adrian Brock Master

                    Which method on the callable statement?

                    All we do is rethrow the SQLException after destroying
                    the connection. If you don't get an SQLException it is
                    because the DB didn't throw one.

                    Like I said before, the NullPointer problem has been fixed,
                    it is irrelevent to the problem, it is just noise.

                    Regards,
                    Adrian

                    • 7. Re: Destroy connection pool -- Bug or Design Feature
                      mcoyne Newbie

                      callable_statement = connection.prepareCall(ProcedureCall_Statement);
                      //
                      // register input and output parameters to the stored procedure
                      //
                      callable_statement.registerOutParameter(1, OracleTypes.INTEGER);
                      callable_statement.setString(2, in0);
                      callable_statement.registerOutParameter(3, OracleTypes.VARCHAR);
                      callable_statement.registerOutParameter(4, OracleTypes.VARCHAR);
                      callable_statement.registerOutParameter(5, OracleTypes.ARRAY,"JLIMS.ERROR_LTYPE");
                      //Executing procedure
                      callable_statement.execute();

                      A. When the transaction ends, Oracle did not throw an exception, because, Jboss did not really close the connection (just marked for closing/destroying). JBoss finally close the connection when the transactions returned.

                      Although you have said that the null pointer is a noise, from the Java client, it does not know how to handle the exception:
                      (a) it does not know if the transaction that it just requested is succesful or not. (note that Jboss knows that the transaction is sucessful, because of A- above)
                      (b) it does not know if it is a 'serious' or 'ignorable' exception

                      I suppose:
                      1. Destroy of a pool should not be using lightly (well, you might say it is a common sense?)
                      2. Jboss is already intelligent enough to marke connections for destroying, and will not closing them until the all references releases. It should consider the release from the client application as well, and not just from the database. Since the connection is marked for destroyed, when client application request a close, why should Jboss throw exception, it should consider one more reference has been released so that it can go ahead and completely close. Thoughts?

                      I truly appreciate your help and your time in helping anwering many of my questions. I've learned quite a bit with this session. We have tested Jboss with Oracle RAC (Oracle high availability solution) as well.

                      Thank you very much.

                      • 8. Re: Destroy connection pool -- Bug or Design Feature
                        Adrian Brock Master

                        Let me define the terminology then I'll re-explain.

                        A handle - this is the connection the user sees.
                        Managed Connection (MC) - This is the jboss implementation
                        from the JCA spec
                        Connection - The real JDBC connection that is pooled.

                        The relationship is
                        handle >- MC - Connection where >- means many to one

                        When the SQLException occurs, the Connection IS closed.
                        This is the real connection to Oracle. You can implement
                        a handler to decide which SQLExceptions force
                        a destroy based on DB specific return codes.

                        It is the MC that is marked as destroyed because we need
                        to wait for handles to be closed. When this happens
                        the handle and MC can be cleaned up.

                        The NullPointer occurs because there was a problem
                        with destroy processing. This was fixed in 3.2.2RC3.
                        It did not affect any processing, but propagated back
                        to the client which is confusing.

                        Now back to your problem. We have an handler for
                        Oracle errors that decides which SQLExceptions cause
                        a destroy. It is
                        org.jboss.resource.adapter.vendor.OracleExceptionSorter

                        If it is not fatal error we do not close the Connection.
                        It is your decision to rollback on an SQLException.

                        Regards,
                        Adrian