4 Replies Latest reply on Feb 21, 2002 11:07 AM by fiftefora

    JBoss + MySQL (InnoDB) and transactions

    pfourie

      Hi Everyone,

      I am using JBoss 2.4.3 and MySQL with InnoDB tables for transactional
      support.

      I wrote a stateless session bean with one method called doTransaction(),
      this method has a transaction attribute of "Required" in the deployment
      descriptor.
      I then tried to execute the following code:
      public void doTransaction(){
      try {
      InitialContext ctx = new InitialContext();
      DataSource ds = (DataSource) ctx.lookup("java:/MyDS");

      InvoiceDAO dao = new InvoiceDAO();
      dao.insert(ds.getConnection());
      System.out.println("doTransaction called");

      InvoiceLineDAO dao2 = new InvoiceLineDAO();
      dao2.insert(ds.getConnection()); // This line throws an
      exception
      } catch (Exception e) {
      e.printStackTrace();
      }

      I purposefully caused the second data access call (dao2.insert()) to throw
      an exception. However if the second db calls fails it still writes the
      first db access to the database!?
      Why is this? I expected it to rollback the transaction completely.

      Does Jboss support transaction propagation?

      really puzzled...
      Philip Fourie

        • 1. Re: JBoss + MySQL (InnoDB) and transactions

          You know, I've seen the same thing although I've not fully investigated this yet.

          I suspect it has to do with autocommit. I know that the mm MySQL driver supports turning on autocommit, but nowhere in the configuration for the driver data source does it say you can set autocommit to be turned off. Since you don't really control the datasource's connections, you can't call the method directly either, especially not with CMP.

          I think if autocommit is on (and it is by default) every query is immediately committed, resulting in the behavior you see.

          • 2. Re: JBoss + MySQL (InnoDB) and transactions
            pfourie

            I have done some more testing and found the following which may mean something to someone ?!

            When I do the transaction handling my self (BMT) instead of the container (CMT), I get the expected result - this is the first transaction gets rolled back correctly. (See code sample below.)
            To me this suggests that the driver and database support transaction handling correctly, however when switching to CMT, the first transaction never rolls back!

            I did a sanity check to see if my deployment descriptor is correct and left the UserTransaction code in the bean even when it is set to CMT (Attribute=Required). When I then run the bean, I get the following (expected) result:
            java.lang.IllegalStateException: CMT beans are not allowed to get a UserTransaction.

            This means the container knows it should handle the bean as a CMT but somewhere something goes wrong.

            Some extra info on my environment:
            - I upgraded to JBoss 2.4.4 (problem still exists)
            - Using MySQL 3.23.46 with InnoDB tables
            - Running JBoss with Catalina
            - I am calling my bean for testing purposes directly from
            a JSP from the Web container.
            - Using the default JBoss transaction manager
            - Using org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl with mm MySQL 2.0.9 driver
            Any help will be greatly appreciated.

            ----------------------------------

            public void doTransaction() {
            UserTransaction ut = null;

            try {
            InitialContext ctx = new InitialContext();
            DataSource ds;
            ut = ejbContext.getUserTransaction();
            ut.begin();

            InvoiceDAO dao = new InvoiceDAO();
            ds = (DataSource) ctx.lookup("java:/ProcessManagerDS");
            dao.insert(ds.getConnection());
            System.out.println("doTransaction called");

            InvoiceLineDAO dao2 = new InvoiceLineDAO();
            ds = (DataSource) ctx.lookup("java:/ProcessManagerDS");
            dao2.insert(ds.getConnection());
            } catch (Exception e) {
            e.printStackTrace();
            try {
            if (ut != null)
            ut.rollback();
            } catch (Exception ex) {
            }
            } finally {
            try {
            if (ut != null)
            ut.commit();
            } catch (Exception e) {
            e.printStackTrace();
            }
            }

            • 3. Re: JBoss + MySQL (InnoDB) and transactions
              fiftefora

              I've been having a similar problem.

              MySQL has a log of queries, etc., which can be turned on.
              (e.g. by putting this in /etc/my.cnf:
              [server]
              log=/var/lib/mysql/logname.log
              )

              This allows one to see what MySQL thinks it's being told by JBoss combined with your JDBC driver.

              Well, I give a method Required or RequiresNe w trans-attribute, have the method throw an EJBException, and I still see what I always see in the MySQL log,
              the INSERT SQL statement, and then "commit". And the new record exists in the database, even though the create() call was in the method with the Required attribute. At the client side, I see "java.rmi.ServerException: Transaction rolled back", but I don't see "rollback" in the MySQL log. (However, I do see "rollback"'s there when JBoss deploys a new jar file and gets information from MySQL about what tables it has. So JBoss and mm.mysql know how to send a rollback command some of the time.) Also note that when JBoss starts up, you can see that it sends "SET autocommit=0" to MySQL. (This line is followed by "rollback", but a quick experiment seems to show that a rollback after a set autocommit does nothing.)

              I dunno. Maybe there's something crucial that I'm clueless about.

              Details:

              JBoss 2.2.2
              JDBC driver: mm.mysql-2.0.4, mm.mysql-2.0.11 (tried both)
              MySQL 3.23.36, using Innobase tables.

              This is inside <assembly-descriptor>...</assembly-descriptor> in ejb-jar.xml:
              <container-transaction>

              <ejb-name>SalespersonManager</ejb-name>
              <method-name>createSalesperson</method-name>

              <trans-attribute>Required</trans-attribute>
              </container-transaction>

              This is what's in SalespersonManagerBeanImpl, the (stateless) session bean implementation:
              [pre]
              public Salesperson createSalesperson () throws BusinessException, RemoteException {
              Salesperson salesperson;

              try {
              salesperson = getSalespersonHome().create ();
              } catch (CreateException e) {
              throw new BusinessException ("....", e);
              }

              if (true) {
              throw new EJBException ("Test.");
              }

              return salesperson;
              }
              [/pre]

              My understanding is that this code should cause a rollback, but nothing like that seems to be happening.

              • 4. Re: JBoss + MySQL (InnoDB) and transactions
                fiftefora

                I figured out my problem. I didn't know that in the descriptor for the session bean (SalespersonManager in the example in my previous post), I needed
                [pre]
                <transaction-type>Container</transaction-type>
                [/pre]
                rather than
                [pre]
                <transaction-type>Bean</transaction-type>
                [/pre]
                Now when the method with the Required trans-attribute throws EJBException, I see "rollback" in the MySQL log,
                and as you'd expect, the change to the database is not permanent.