1 Reply Latest reply on Jul 19, 2008 9:06 AM by jgaston

    Beginner's problem on XA transactions

    jgaston

      This possibly belongs to the Beginners Corner:
      I am trying to insert records in two tables on two XA datasources using a SLSB on JBoss 4.2.2.GA. The operation can fail because of a unique constraint at the de DB level in each table. I was (maybe naively) expecting both inserts to be rolled back by the server if any one of them failed. Unexpectedly, I find that one can succeed while the other one fails.

      Here is my ejb:

      @Stateless
      public class ArticleFacade implements ArticleFacadeRemote {
      
       @Resource (mappedName="java:jdbc/dsX1")
       private DataSource ds1;
      
       @Resource (mappedName="java:jdbc/dsX2")
       private DataSource ds2;
      
       public int create2Aricles(String code1, String code2) {
      
       Connection connect1 = null, connect2 = null;
       PreparedStatement stm1 = null, stm2= null;
       // Unique constraint on code in both article tables
       String query = "INSERT INTO article (code) VALUES(?)";
       Integer done = 0;
      
       try {
       connect1 = ds1.getConnection();
       connect2 = ds2.getConnection();
       stm1 = connect1.prepareStatement(query);
       stm1.setString(1, code1);
       stm2 = connect2.prepareStatement(query);
       stm2.setString(1, code2);
       done += stm1.executeUpdate();
       done += stm2.executeUpdate();
       } catch (Exception e) {
       throw new EJBException(e.getMessage(), e);
       } finally {
       ... close statements and connections
       }
       return done;
       }
      
      }
      

      and my datasources in mysql-xa-ds.xml:
      <datasources>
       <xa-datasource>
       <jndi-name>jdbc/dsX1</jndi-name>
       <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
       <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/dbX1</xa-datasource-property>
       <user-name>devX1</user-name>
       <password></password>
       <track-connection-by-tx/>
       </xa-datasource>
       <xa-datasource>
       <jndi-name>jdbc/dsX2</jndi-name>
       <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
       <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/dbX2</xa-datasource-property>
       <user-name>devX2</user-name>
       <password></password>
       <track-connection-by-tx/>
       </xa-datasource>
      </datasources>
      

      First call on create2Articles("CODE1", "CODE1") succeeds as expected (the tables are empty)
      Second call create2Articles("CODE2", "CODE1") fails on connection 2, as expected, because of the unique constraint.
      My problem is that the first aricle (CODE2) is inserted on connection 1. Should not both connections be enlisted
      in the same transaction, and the thrown EJBException force a rollback for both of them ?
      Am I missing something very basic ? Any pointer (an simple code example) would be appreciated.

      Regards,

      Gaston