6 Replies Latest reply on Jan 17, 2008 9:18 AM by jaikiran

    Transactions broken? Trivial Hibernate example with EJB3 and

    smithmb

      Hello folks,

      I'm trying to create a trivial example of a transaction working correctly with MySQ L5.0.x and local-tx-datasource and Connector/J 3.x. I'm using Hibernate and EJB3, and JBoss 4.0.x. I cannot get transactions to rollback w/ SessionContext.setRollbackOnly() OR by throwing an uncaught EJB/Runtime exception.

      Rolling back via the mysql command line client works great, and the tables in question are InnoDB.

      Here is my documentation of the problem -- I'd love it if anyone can tell me anything that looks weird. Please help -- this is so trivial it SHOULD work!

      mysql-ds.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <datasources>
       <local-tx-datasource>
       <jndi-name>MySqlDS</jndi-name>
       <connection-url>jdbc:mysql://127.0.0.1:3306/scheduler_db</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>censored</user-name>
       <password>censored</password>
       <metadata>
       <type-mapping>mySQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>
      


      persistence.xml:
      <persistence>
       <persistence-unit name="persistence-unit">
       <jta-data-source>java:/MySqlDS</jta-data-source>
       <properties>
       <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
       <property name="hibernate.connection.release_mode" value="auto"/>
       <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
       </properties>
       </persistence-unit>
      </persistence>
      


      My EJB3 session bean:
      @Stateless
      public class TestBean implements TestBeanLocal, TestBeanRemote
      {
      
       @PersistenceContext(name = "persistence-unit")
       EntityManager em;
      
       @Resource
       SessionContext ctx;
      
       public static final String RemoteJNDIName = "example/" + TestBean.class.getSimpleName() + "/remote";
       public static final String LocalJNDIName = "example/" + TestBean.class.getSimpleName() + "/local";
      
       @TransactionAttribute(TransactionAttributeType.REQUIRED)
       public void test()
       {
       Book book = new Book(null, "My first book", "Person 1");
       em.persist(book);
      
       Book book2 = new Book(null, "My second book", "Person 2");
       em.persist(book2);
      
       System.out.println("CURRENT TRANSACTION STATUS 1: " + (ctx.getRollbackOnly() ? "ROLLBACK" : "NOT ROLLBACK"));
       ctx.setRollbackOnly();
       System.out.println("CURRENT TRANSACTION STATUS 2: " + (ctx.getRollbackOnly() ? "ROLLBACK" : "NOT ROLLBACK"));
      
       Book book3 = new Book(null, "My third book", "Person 3");
       em.persist(book3);
      
       List someBooks = em.createQuery("from Book").getResultList();
      
       for (Iterator iter = someBooks.iterator(); iter.hasNext();)
       {
       Book element = (Book) iter.next();
       em.remove(element);
       }
       }
      


      My test client:
       public static void main(String[] args) throws Exception
       {
       Properties properties = new Properties();
       properties.put("java.naming.factory.initial", "org.jnp.interfaces.NamingContextFactory");
       properties.put("java.naming.factory.url.pkgs", "=org.jboss.naming:org.jnp.interfaces");
       properties.put("java.naming.provider.url", "localhost:1099");
       Context context = new InitialContext(properties);
      
       TestBeanRemote beanRemote = (TestBeanRemote) context.lookup(TestBean.RemoteJNDIName);
       beanRemote.test();
       }
      


      Output log from JBoss:
      2008-01-10 17:15:40,755 DEBUG [org.jboss.remoting.transport.socket.ServerThread] WAKEUP in SERVER THREAD
      2008-01-10 17:15:40,991 DEBUG [org.jboss.remoting.transport.socket.ServerThread] beginning dorun
      2008-01-10 17:15:41,211 DEBUG [org.hibernate.impl.SessionImpl] opened session at timestamp: 4915213685571584
      2008-01-10 17:15:41,211 DEBUG [org.hibernate.ejb.AbstractEntityManagerImpl] Looking for a JTA transaction to join
      2008-01-10 17:15:41,211 DEBUG [org.hibernate.jdbc.JDBCContext] successfully registered Synchronization
      2008-01-10 17:15:41,211 DEBUG [org.hibernate.ejb.AbstractEntityManagerImpl] Looking for a JTA transaction to join
      2008-01-10 17:15:41,211 DEBUG [org.hibernate.ejb.AbstractEntityManagerImpl] Transaction already joined
      2008-01-10 17:15:41,220 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] executing identity-insert immediately
      2008-01-10 17:15:41,224 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
      2008-01-10 17:15:41,224 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
      2008-01-10 17:15:41,352 DEBUG [org.hibernate.SQL] insert into book (title, author) values (?, ?)
      2008-01-10 17:15:41,420 DEBUG [org.hibernate.id.IdentifierGeneratorFactory] Natively generated identity: 1
      2008-01-10 17:15:41,420 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
      2008-01-10 17:15:41,420 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
      2008-01-10 17:15:41,420 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
      2008-01-10 17:15:41,420 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] executing identity-insert immediately
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.SQL] insert into book (title, author) values (?, ?)
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.id.IdentifierGeneratorFactory] Natively generated identity: 2
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
      2008-01-10 17:15:41,421 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
      2008-01-10 17:15:41,421 INFO [STDOUT] CURRENT TRANSACTION STATUS 1: NOT ROLLBACK
      2008-01-10 17:15:41,422 INFO [STDOUT] CURRENT TRANSACTION STATUS 2: ROLLBACK
      2008-01-10 17:15:41,422 DEBUG [org.jboss.ejb3.entity.ManagedEntityManagerFactory] ************** closing entity managersession **************
      2008-01-10 17:15:41,447 DEBUG [org.jboss.remoting.transport.socket.ServerThread] begin thread wait
      


      Book.java (the hibernate entity):
      @Entity
      @Table(name = "book")
      @SequenceGenerator(name = "book_sequence", sequenceName = "book_id_seq")
      public class Book implements Serializable
      {
       /**
       *
       */
       private static final long serialVersionUID = -9108127436362270765L;
       private Integer id;
       private String title;
       private String author;
      
       public Book()
       {
       super();
       }
      
       public Book(Integer id, String title, String author)
       {
       super();
       this.id = id;
       this.title = title;
       this.author = author;
       }
      
       @Override
       public String toString()
       {
      
       return "Book: " + getId() + " Title " + getTitle() + " Author " + getAuthor();
       }
      
       /**
       * @return the author
       */
       public String getAuthor()
       {
       return author;
       }
      
       /**
       * @param author the author to set
       */
       public void setAuthor(String author)
       {
       this.author = author;
       }
      
       /**
       * @return the id
       */
       @Id
       @GeneratedValue(strategy = GenerationType.AUTO, generator = "book_sequence")
       public Integer getId()
       {
       return id;
       }
      
       /**
       * @param id the id to set
       */
       public void setId(Integer id)
       {
       this.id = id;
       }
      
       /**
       * @return the title
       */
       public String getTitle()
       {
       return title;
       }
      
       /**
       * @param title the title to set
       */
       public void setTitle(String title)
       {
       this.title = title;
       }
      
      }
      


        • 1. Re: Transactions broken? Trivial Hibernate example with EJB3
          smithmb

          This should be so simple, yet I'm still stuck!

          • 2. Re: Transactions broken? Trivial Hibernate example with EJB3
            jaikiran

            Based on your post in the JCA forum, i guess the auto-commit is true for your MySQL datasource. Try adding relaxAutoCommit=true in the connection URL of your datasource and see if it works. Here's how you can pass this parameter through the connection URL:


            <?xml version="1.0" encoding="UTF-8"?>
            <datasources>
             <local-tx-datasource>
             <jndi-name>MySqlDS</jndi-name>
             <connection-url>jdbc:mysql://127.0.0.1:3306/scheduler_db?relaxAutoCommit=true</connection-url>
             <driver-class>com.mysql.jdbc.Driver</driver-class>
             <user-name>censored</user-name>
             <password>censored</password>
             <metadata>
             <type-mapping>mySQL</type-mapping>
             </metadata>
             </local-tx-datasource>
            </datasources>


            • 3. Re: Transactions broken? Trivial Hibernate example with EJB3
              smithmb

              Thank you for the reply -- setting relaxAutoCommit does not seem to work. Data is still committed immediately by the transaction manager, using the datasource I listed above PLUS relaxAutoCommit=true.

              I even tried a very basic session bean method:

               @TransactionAttribute(TransactionAttributeType.REQUIRED)
               public void test()
               {
              
               try {
               javax.sql.DataSource ds = (javax.sql.DataSource) new InitialContext().lookup("java:/MySqlDS");
               java.sql.Connection conn = ds.getConnection();
              
               System.out.println("IS AUTOCOMMIT: " + (conn.getAutoCommit()));
              
               Statement statement = conn.createStatement();
               statement.execute("DELETE FROM BOOK");
               statement.execute("INSERT INTO BOOK(title,author) VALUES(\"Should not see this title\",\"Should not see this author\")");
              
              
               ctx.setRollbackOnly();
              
               statement.close();
               conn.close();
              
               } catch (Exception ex) {
               System.out.println(ex);
               }
               }
              


              I *do* see the data that was inserted when it shouldn't have been. If, in that method above, I call conn.getAutoCommit(), it is set to false. I think the transaction manager is doing something wrong, but I have no way of investigating this further =/.

              Please help!

              • 4. Re: Transactions broken? Trivial Hibernate example with EJB3
                smithmb

                Thank you for the reply -- setting relaxAutoCommit does not seem to work. Data is still committed immediately by the transaction manager, using the datasource I listed above PLUS relaxAutoCommit=true.

                I even tried a very basic session bean method:

                 @TransactionAttribute(TransactionAttributeType.REQUIRED)
                 public void test()
                 {
                
                 try {
                 javax.sql.DataSource ds = (javax.sql.DataSource) new InitialContext().lookup("java:/MySqlDS");
                 java.sql.Connection conn = ds.getConnection();
                
                 System.out.println("IS AUTOCOMMIT: " + (conn.getAutoCommit()));
                
                 Statement statement = conn.createStatement();
                 statement.execute("DELETE FROM BOOK");
                 statement.execute("INSERT INTO BOOK(title,author) VALUES(\"Should not see this title\",\"Should not see this author\")");
                
                
                 ctx.setRollbackOnly();
                
                 statement.close();
                 conn.close();
                
                 } catch (Exception ex) {
                 System.out.println(ex);
                 }
                 }
                


                I *do* see the data that was inserted when it shouldn't have been. If, in that method above, I call conn.getAutoCommit(), it is set to false. I think the transaction manager is doing something wrong, but I have no way of investigating this further =/.

                Please help!

                • 5. Re: Transactions broken? Trivial Hibernate example with EJB3
                  jaikiran

                  I decided to give this a try on my local setup. I used MySQL-5.0.20 with JBoss-4.2.2. I wrote a similar EJB as yours to try this out:


                  /**
                   *
                   */
                  package org.myapp.ejb.impl;
                  
                  import java.io.BufferedReader;
                  import java.io.InputStreamReader;
                  import java.sql.SQLException;
                  import java.sql.Statement;
                  import java.util.HashSet;
                  import java.util.List;
                  import java.util.Set;
                  
                  import javax.annotation.Resource;
                  import javax.ejb.Remote;
                  import javax.ejb.SessionContext;
                  import javax.ejb.Stateless;
                  import javax.ejb.TransactionAttribute;
                  import javax.ejb.TransactionAttributeType;
                  import javax.naming.InitialContext;
                  import javax.persistence.EntityManager;
                  import javax.persistence.PersistenceContext;
                  import javax.persistence.Query;
                  
                  import org.jboss.annotation.ejb.RemoteBinding;
                  import org.myapp.ejb.UserManager;
                  import org.myapp.entity.Account;
                  import org.myapp.entity.User;
                  import org.myapp.exception.ApplicationException;
                  
                  /**
                   * @author Jaikiran Pai
                   * @since
                   */
                  @Stateless
                  @Remote ({UserManager.class})
                  @RemoteBinding (jndiBinding = "RemoteUserManagerBean")
                  public class UserManagerBean implements UserManager {
                  
                   @PersistenceContext
                   private EntityManager entityManager;
                  
                   @Resource
                   private SessionContext sessionContext;
                  
                  
                  
                   public void test(String userName)
                   {
                   java.sql.Connection conn = null;
                   try {
                   javax.sql.DataSource ds = (javax.sql.DataSource) new InitialContext().lookup("java:/EJB3PersistenceDS");
                   conn = ds.getConnection();
                  
                   System.out.println("IS AUTOCOMMIT: " + (conn.getAutoCommit()));
                  
                   Statement statement = conn.createStatement();
                  
                   statement.execute("INSERT INTO USER (NAME) VALUES('" + userName + "')");
                   System.out.println("Inserted user " + userName);
                   System.out.println("Session Context is " + sessionContext);
                  
                   System.out.println("Called setRollbackOnly");
                   System.out.println("Is it set = " + sessionContext.getRollbackOnly());
                  
                  
                   if (true) {
                   System.out.println("Throwing exception");
                   throw new RuntimeException("Intentional exception");
                   }
                   statement.close();
                  
                  
                  
                   } catch (RuntimeException re) {
                   throw re;
                   } catch (Exception ex) {
                   System.out.println(ex);
                   ex.printStackTrace();
                   } finally {
                   if (conn != null) {
                   try {
                   conn.close();
                   } catch (SQLException e) {
                   // TODO Auto-generated catch block
                   e.printStackTrace();
                   }
                   }
                   }
                   }
                  
                  }
                  


                  And yes, i too see the same behaviour. The insert is not rolled back. I then switched my database to MS SQLServer to see if this is specific to MySQL and observed that the insert was rolled back on that DB server. So this looks like specific to MySQL.

                  I have read in many places that MySQL globally set autocommit to true. I tried various ways to disable this, including what's been mentioned at
                  http://www.oreillynet.com/databases/blog/2007/02/mysql_transactions_and_autocom.html, but havent been able to get this working. If you can somehow disable the autocommit on the MySQL server then i guess you should be able to get this working.

                  • 6. Re: Transactions broken? Trivial Hibernate example with EJB3
                    jaikiran

                    I should have noticed this before. The

                    System.out.println("IS AUTOCOMMIT: " + (conn.getAutoCommit()));


                    prints false which means that AutoCommit has already been disabled. So its not the problem with autocommit. A little bit of reading the MySQL documents made me realize that unless your tables use "InnoDB" engine, transactions are not going to work. So i changed my table definition to use InnoDB engine instead of MyISAM engine (which is what i was using earlier). So your table definition should look like:

                    CREATE TABLE `testtx` (
                    `Name` varchar(255) default NULL
                    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


                    Changing the table definition and running the code worked perfectly with the insert getting rolled back when a RuntimeException is thrown. Give it a try.