2 Replies Latest reply on Sep 17, 2002 6:13 PM by 3004

    Rollback not performed after an SQLException with a thin XA driver, possible bug?

    3004
      Hi, I'm not sure this is the right group to post this at it involves some
      topics besides JDBC but I don't want to do crosspostings so...

      Our applications is running under the following platforrm:

      Sun's JDK 1.3.1_01 for Linux, Weblogic 6.1sp2 on Linux and ORACLE 8.7.1 on
      Solaris

      We have a quite complex application that uses two transactional resources,
      one using CMP EBs to persist bussines data on the RDBMS, and JMS to perform
      asynchronous process to deal with other ancient systems.

      In order to do this we are using an XA capable JDBC driver and the JMS
      provided with Weblogic. We have been using an OCI driver for some time but
      we were having strange VM crashes and reading the post here at Weblogic's
      newsgroups we decided to change the driver by a thin one.

      One or two days after the change on the production system we detected this
      error:

      <12-sep-02 13:55:54 CEST> <Debug> <T3Services> <55:54.399:No errors
      detected, performing COMMIT>
      java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
      ORA-01000: maximum open cursors exceeded
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
      at
      oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
      at
      oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1602
      )
      at
      oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1527)
      at
      oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
      :2045)
      at
      oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedState
      ment.java:395)
      at
      oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.j
      ava:446)
      at
      oracle.jdbc.xa.client.OracleXAResource.prepare(OracleXAResource.java:440)
      at
      weblogic.jdbc.jta.VendorXAResource.prepare(VendorXAResource.java:61)
      at weblogic.jdbc.jta.DataSource.prepare(DataSource.java:722)
      at
      weblogic.transaction.internal.ServerResourceInfo.prepare(ServerResourceInfo.
      java:1124)
      at
      weblogic.transaction.internal.ServerResourceInfo.prepare(ServerResourceInfo.
      java:370)
      at
      weblogic.transaction.internal.ServerSCInfo.startPrepare(ServerSCInfo.java:18
      6)
      at
      weblogic.transaction.internal.ServerTransactionImpl.localPrepare(ServerTrans
      actionImpl.java:1822)
      at
      weblogic.transaction.internal.ServerTransactionImpl.globalPrepare(ServerTran
      sactionImpl.java:1607)
      at
      weblogic.transaction.internal.ServerTransactionImpl.internalCommit(ServerTra
      nsactionImpl.java:218)
      at
      weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransaction
      Impl.java:190)
      at
      weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManag
      erImpl.java:247)
      at
      weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManag
      erImpl.java:240)
      at
      foo.bar.util.TransactionManager.FinishTransaction(TransactionManager.java:69
      2)
      at foo.bar.user.BussinesManager.run(BussinesManager.java:122)
      <12-sep-02 13:55:55 CEST> <Debug> <T3Services> <55:55.400:JMS message
      received>

      I have read some info about the ORA-01000: maximum open cursors exceeded
      error, I know it's a known ORACLE 'bug' but althought I have seen the
      ORA-01000 error some times mainly on SELECT statements against the DB and
      Weblogic seemed to detect the exeception and reexecuting it this time I
      think it's a quite different problem....

      First of all it happened during a TX COMMIT (as you can see in our debug
      statement) and the big problem is that Weblogic didn't 'nottice' the
      execption, as it didn't rolled back the transaction!

      If you read the stack trace, Weblogic didn't perform a rollback as I have
      seen other times that either it rollback the transaction or marks it for
      rollback.

      Of course the DB performs the rollback and nothing is stored on it but due
      to our 'dual' transactional resources as Weblogic didn't rollback the
      transaction the JMS message that is 'holded' on the queue (as it is part of
      the transaction) is delivered to the subscriber and then it fails as it
      expects some data to be stored in the DB in order to perform it's logic.

      What is even worse is that, as we don't receive a container exception, we
      can't tell users about the rollback on the DB and it ends in data being
      missed.

      I think this could be a Weblogic bug as it is very strange that a DB
      rollback isn't notticed from the container to the application.

      We have had to go back to the OCI driver with all it's 'native' problems but
      at least we have never seen a weird behaviour like the one above.

      Thanks in advance

      Ignacio G. Dupont


        • 1. Re: Rollback not performed after an SQLException with a thin XA driver, possible bug?
          3004
          Hi,

          I have a couple of comments on your posting below which I hope will help
          you:

          1) Regarding the "ORA-1000 maximum open cursors exceeded" exception, I
          wouldn't necessarily classify this as a "bug". Everytime a developer has
          approached me regarding this exception (I'm an Oracle DBA with a development
          background) we have always solved it by making sure that all Statements and
          ResultSets are EXPLICITLY closed. I know the JDBC API says that ResultSets
          will get closed the next time the Statement.execute() methods are called,
          but I insist on closing them explicitly, preferrably in the finally clause
          of the appropriate methods. I know you are using CMP but I wonder whether
          the application uses any BMP as well?

          2) It doesn't surprise me that the ORA-1000 occurred in the commit of the
          transaction. You will find that CMP may cache changes to Entity Beans until
          the transaction is commited as which point UPDATE statements are executed to
          flush the dirty beans to the database before the actual commit is called.
          If you have some code that is leaking cursors then is quite possible for
          ORA-1000 to be thrown during the commit.

          3) If I remember correctly container transactions will only rollback if it
          catches a system exception - it will commit for all application exceptions.
          Now because SQLException is an application exception if you do not catch it
          and then explicitly call EJBContext.setRollbackOnly() the transaction WILL
          commit. In your case, I wonder whether the database actually rolled back
          due to the failed statement? It could be that other successfully executed
          SQL statements got committed, and the SQL that caused the ORA-1000 obviously
          would not have effected the DB because it failed. Just because one
          statement failed doesn't mean all statements will fail and be rolled back.

          4) Having said that I just realised you said the exception was never thrown
          back to your application, is that correct? If so, then forget point 3.

          5) Regarding the JMS message that got enqueued, if the application missed
          calling the EJBContext.setRollbackOnly() method when the SQLException was
          thrown then it wouldn't surprise me that the message was enqueued as I would
          have expected the transaction to have commited. Are you using a transacted
          JMS session to the Queue or Topic?

          Regards,
          Craig.



          "Ignacio G. Dupont" <igdtl@yahoo.com> wrote in message
          news:3d84d6d5@newsgroups.bea.com...
          Hi, I'm not sure this is the right group to post this at it involves some
          topics besides JDBC but I don't want to do crosspostings so...

          Our applications is running under the following platforrm:

          Sun's JDK 1.3.1_01 for Linux, Weblogic 6.1sp2 on Linux and ORACLE 8.7.1 on
          Solaris

          We have a quite complex application that uses two transactional resources,
          one using CMP EBs to persist bussines data on the RDBMS, and JMS to
          perform
          asynchronous process to deal with other ancient systems.

          In order to do this we are using an XA capable JDBC driver and the JMS
          provided with Weblogic. We have been using an OCI driver for some time but
          we were having strange VM crashes and reading the post here at Weblogic's
          newsgroups we decided to change the driver by a thin one.

          One or two days after the change on the production system we detected this
          error:

          <12-sep-02 13:55:54 CEST> <Debug> <T3Services> <55:54.399:No errors
          detected, performing COMMIT>
          java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
          ORA-01000: maximum open cursors exceeded
          at
          oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
          at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
          at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
          at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
          at
          oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
          at
          oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1602
          )
          at
          oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1527)
          at
          oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
          :2045)
          at
          oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedState
          ment.java:395)
          at
          oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.j
          ava:446)
          at
          oracle.jdbc.xa.client.OracleXAResource.prepare(OracleXAResource.java:440)
          at
          weblogic.jdbc.jta.VendorXAResource.prepare(VendorXAResource.java:61)
          at weblogic.jdbc.jta.DataSource.prepare(DataSource.java:722)
          at
          weblogic.transaction.internal.ServerResourceInfo.prepare(ServerResourceInfo.
          java:1124)
          at
          weblogic.transaction.internal.ServerResourceInfo.prepare(ServerResourceInfo.
          java:370)
          at
          weblogic.transaction.internal.ServerSCInfo.startPrepare(ServerSCInfo.java:18
          6)
          at
          weblogic.transaction.internal.ServerTransactionImpl.localPrepare(ServerTrans
          actionImpl.java:1822)
          at
          weblogic.transaction.internal.ServerTransactionImpl.globalPrepare(ServerTran
          sactionImpl.java:1607)
          at
          weblogic.transaction.internal.ServerTransactionImpl.internalCommit(ServerTra
          nsactionImpl.java:218)
          at
          weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransaction
          Impl.java:190)
          at
          weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManag
          erImpl.java:247)
          at
          weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManag
          erImpl.java:240)
          at
          foo.bar.util.TransactionManager.FinishTransaction(TransactionManager.java:69
          2)
          at foo.bar.user.BussinesManager.run(BussinesManager.java:122)
          <12-sep-02 13:55:55 CEST> <Debug> <T3Services> <55:55.400:JMS message
          received>

          I have read some info about the ORA-01000: maximum open cursors exceeded
          error, I know it's a known ORACLE 'bug' but althought I have seen the
          ORA-01000 error some times mainly on SELECT statements against the DB and
          Weblogic seemed to detect the exeception and reexecuting it this time I
          think it's a quite different problem....

          First of all it happened during a TX COMMIT (as you can see in our debug
          statement) and the big problem is that Weblogic didn't 'nottice' the
          execption, as it didn't rolled back the transaction!

          If you read the stack trace, Weblogic didn't perform a rollback as I have
          seen other times that either it rollback the transaction or marks it for
          rollback.

          Of course the DB performs the rollback and nothing is stored on it but due
          to our 'dual' transactional resources as Weblogic didn't rollback the
          transaction the JMS message that is 'holded' on the queue (as it is part
          of
          the transaction) is delivered to the subscriber and then it fails as it
          expects some data to be stored in the DB in order to perform it's logic.

          What is even worse is that, as we don't receive a container exception, we
          can't tell users about the rollback on the DB and it ends in data being
          missed.

          I think this could be a Weblogic bug as it is very strange that a DB
          rollback isn't notticed from the container to the application.

          We have had to go back to the OCI driver with all it's 'native' problems
          but
          at least we have never seen a weird behaviour like the one above.

          Thanks in advance

          Ignacio G. Dupont

          • 2. Re: Rollback not performed after an SQLException with a thin XA driver, possible bug?
            3004
            Hi,

            Thanks so much for your reply

            ----- Original Message -----
            From: "Craig Munday" <csmunday@dingoblue.net.au>
            Newsgroups: weblogic.developer.interest.jdbc
            Sent: Tuesday, September 17, 2002 12:00 PM
            Subject: Re: Rollback not performed after an SQLException with a thin XA
            driver, possible bug?

            Hi,

            I have a couple of comments on your posting below which I hope will help
            you:

            1) Regarding the "ORA-1000 maximum open cursors exceeded" exception, I
            wouldn't necessarily classify this as a "bug". Everytime a developer has
            approached me regarding this exception (I'm an Oracle DBA with a
            development
            background) we have always solved it by making sure that all Statements
            and
            ResultSets are EXPLICITLY closed. I know the JDBC API says that
            ResultSets
            will get closed the next time the Statement.execute() methods are called,
            but I insist on closing them explicitly, preferrably in the finally clause
            of the appropriate methods. I know you are using CMP but I wonder whether
            the application uses any BMP as well?
            We only use CMP EJBs although the persistence is provided by TOPLink instead
            of Weblogic. Well, I can't be sure they (TOPLink people) are ALWAYS clossing
            the Statements and the ResultSets but I don't think the problem is there, as
            far as I can tell I don't remember seeing ORA-01000 errror while using the
            OCI driver.

            Even when we are not using BMPs EBs in our system, we have some batch
            processes scheduled that access the DB via JDBC so I will take a look at
            them to see if there could be any unexpected case in wich they could exit
            without properly clossing the JDBC stuff.
            2) It doesn't surprise me that the ORA-1000 occurred in the commit of the
            transaction. You will find that CMP may cache changes to Entity Beans
            until
            the transaction is commited as which point UPDATE statements are executed
            to
            flush the dirty beans to the database before the actual commit is called.
            If you have some code that is leaking cursors then is quite possible for
            ORA-1000 to be thrown during the commit.
            Yep you are right, TOPLink delays until the tx.commit() in order to perform
            all the INSERT UPDATE and DELETE statements so it isn't so strange seeing
            such an error during the tx.commit(), what I was surprised of, was of seeing
            that exception during normal SELECT statements via EB finders.
            3) If I remember correctly container transactions will only rollback if it
            catches a system exception - it will commit for all application
            exceptions.
            Now because SQLException is an application exception if you do not catch
            it
            and then explicitly call EJBContext.setRollbackOnly() the transaction WILL
            commit. In your case, I wonder whether the database actually rolled back
            due to the failed statement? It could be that other successfully executed
            SQL statements got committed, and the SQL that caused the ORA-1000
            obviously
            would not have effected the DB because it failed. Just because one
            statement failed doesn't mean all statements will fail and be rolled back.
            Maybe I didn't explain it right in the first post, but our application
            didn't catch any exception. In our applications everything goes OK so we
            execute the tx.commit(). The exception is raised during the tx.commit()
            sentence and that should be, at the last point, performed and managed by the
            container.
            4) Having said that I just realised you said the exception was never
            thrown
            back to your application, is that correct? If so, then forget point 3.
            Ok. ;-)
            5) Regarding the JMS message that got enqueued, if the application missed
            calling the EJBContext.setRollbackOnly() method when the SQLException was
            thrown then it wouldn't surprise me that the message was enqueued as I
            would
            have expected the transaction to have commited. Are you using a
            transacted
            JMS session to the Queue or Topic?
            Yep, the JMS resource is involved into the transaction, we relay in the
            correct behaviour of transacted JMS in order to our bussiness logic to do a
            succesful work. Anyway, it worked the right way since a lot of months so I'm
            pretty sure that part is working as expected, the only problem we have had
            is the one I'm posting here.
            Regards,
            Craig.
            Thanks again and regards,
            Ignacio