4 Replies Latest reply on Oct 22, 2002 4:15 AM by Greg

    Recovering from SQL Exception re: UserTransactions

    Greg Newbie

      Dear all,

      How do you recover from a SQL Exception in JBoss?

      Under Orion, I used to call UserTransaction.setRollbackOnly, and the db connection would then be ready for the next user request.

      Under JBoss, the next user HTTP request that is received (that requires a db connection) causes an exception saying that the transaction has been marked for rollback.

      I want the request that caused the SQL Error to put the db connection back into a usable state ready for the next request.

      Additionally, where I detect a business logic error, what is the correct technique for rolling back any database changes already made.

      Under Orion, I used to call UserTransaction.setRollbackOnly to do this.

      Regards,
      Greg.

        • 1. Re: Recovering from SQL Exception re: UserTransactions
          Joel Vogt Master

          Well this will depend where your transactions are going.
          try

          getSessionContext().setRollbackOnly();

          • 2. Re: Recovering from SQL Exception re: UserTransactions
            Greg Newbie

            What do you mean by "where your transactions are going?".

            I'm using a UserTransaction from a servlet to demarcate transactions, i.e. with tx.begin() and tx.commit().

            If a bug occurs relating to a SQL statement, then an exception is thrown and i'd like the db connection to be put back into a useable state, ready for the next request.

            • 3. Re: Recovering from SQL Exception re: UserTransactions
              Joel Vogt Master

              I meant where you are executing the transactions, in your case in a servlet. (My method above for example might be used in a Session Bean).
              Are you getting this db connection via jboss's connection pool or straight lookup?

              • 4. Re: Recovering from SQL Exception re: UserTransactions
                Greg Newbie

                I'm doing the following:

                1. Obtain UserTransaction InitialContext.lookup("java:comp/UserTransaction"). This is called from a servlet.

                2. call begin on UserTransaction

                3. call various business logic that calls BMP Entity beans directory -- i.e. no SLSB facade in between. The entity beans obtain a DataSource first to get a db transaction.

                4. call commit on UserTransaction


                If an error happens in step 3., I had previously called setRollbackOnly and skipped step 4. The database transaction was then in a usable state, i.e. it has been rolled back in the DBMS and the app server also seemed to be ok.

                I've done a bit more reading and have found notes saying that you're only supposed to use container managed transactions with entity beans.

                Based on that info, I removed the UserTransaction code, and made sure by BMP Entity beans were using "Required" as their transaction attribute.

                I then repeated the test.

                This time, the same business logic error happened of course but two differences occurred:

                a) Every call to an Entity bean seemed to result in a new database transaction, instead of previously, all calls within the scope of the tx.begin/tx.commit being in a database transaction.

                b) At least now the db transaction was left in a usable state, i.e. rolled back in the database, BUT i'm screwed because not all sql statements were rolled back and so the database is left in an inconsistent state.

                I could imagine that creating a truckload of SLSBs and wrapping my calls to the Entity beans with SLSBs might solve the problem but i'm hoping i don't have to do this.

                Is anyone able to help me shed light on this?

                Regards,
                Greg.