3 Replies Latest reply on Nov 13, 2009 6:20 AM by palash.you

    problem in obtaining multiple connection in a single transac

    palash.you Nov 12, 2009 2:38 AM

      Hi All,

      We are stuck in an issue, probably related to distributed transaction related JBOSS server configuration. We are working on the migration activity of the existing application. Below are the details

      1. We are using EJB3.0 and nature of transaction is distributed
      2. We are using more than one databases in one transaction
      3. MySQL version: MySQL community 5.0.45
      4. Application Server: JBoss 4.2.3 GA
      4. MySQL storage Engine: INNODB
      5. MySQL Driver: mysql-connector-java-5.0.8-bin.jar

      Since we are using distributed transactions , we have defined
      xa-datasources "mysql-xa-ds.xml" like below.

      <xa-datasource>
<jndi-name>jdbc/ABC</jndi-name>
<use-java-context>false</use-java-context>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
<xa-datasource-property name="URL">jdbc:mysql://*.*.*.*:3306/npd_fw_common</xa-datasource-property>
<xa-datasource-property name="User">XXXXX</xa-datasource-property>
<xa-datasource-property name="Password">XXXXX</xa-datasource-property>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
<!--the minimum size of the connection pooL-->
<min-pool-size>10</min-pool-size>
<!--The maximum connections in a pool/sub-pool-->
<max-pool-size>50</max-pool-size>
</xa-datasource>


      Problem Scenario:

      1. Starts a transaction which connects to a database and fires an insert query: Successful
      2. In the same transaction try to connect one more database: Gets following error:




      Error
      2009-11-11 10:58:12,515 ERROR com.nucleus.finnonenext.framework.core.dbmanager.dao.CoreConnectionManager.getConnection(CoreConnectionManager.java:129) %m org.jboss.util.NestedSQLException: Transaction is not active: tx=TransactionImple < ac, BasicAction: a013351:e045:4afa4a3b:4f status: ActionStatus.ABORT_ONLY >; - nested throwable: (javax.resource.ResourceException: Transaction is not active: tx=TransactionImple < ac, BasicAction: a013351:e045:4afa4a3b:4f status: ActionStatus.ABORT_ONLY >)

      After this whole transaction gets rolled back.



      Would like to to know the approprite server configuration steps which can be used for distributed transaction .

      Would appriciate your help to resolve this issue

      regards,
      Palash

      • 3883 Views
      • Tags:
        • 1. Re: problem in obtaining multiple connection in a single tra
          vickyk
          vickyk Nov 12, 2009 4:21 AM (in response to palash.you)

           

          "palash.you" wrote:

          2. In the same transaction try to connect one more database: Gets following error:




          Error
          2009-11-11 10:58:12,515 ERROR com.nucleus.finnonenext.framework.core.dbmanager.dao.CoreConnectionManager.getConnection(CoreConnectionManager.java:129) %m org.jboss.util.NestedSQLException: Transaction is not active: tx=TransactionImple < ac, BasicAction: a013351:e045:4afa4a3b:4f status: ActionStatus.ABORT_ONLY >; - nested throwable: (javax.resource.ResourceException: Transaction is not active: tx=TransactionImple < ac, BasicAction: a013351:e045:4afa4a3b:4f status: ActionStatus.ABORT_ONLY >)

          After this whole transaction gets rolled back.



          This could be possible cause of TX being aborted
          http://www.jboss.org/community/wiki/Multiple1PC


            • Actions
          • 2. Re: problem in obtaining multiple connection in a single tra
            palash.you Nov 12, 2009 7:46 AM (in response to palash.you)

            Thanks for your quick reply

            We have tried to incorporate that also by implementing in file jbossjta-properties.xml located at {JBOSS_HOME}/server/default/conf

            
<property name="com.arjuna.ats.jta.allowMultipleLastResources" value="true"/>



            at the same time all the sql database connection has been configured in Mysql-XA-ds.xml. with the XA property only

            but still the same error is coming . Application is running successfully in Oracle DB +Oc4j server . Whats gone wrong with the configuration os
            JBOSS+MySQL that is why we are getting this problem

            regards,
            Palash



              • Actions
            • 3. Re: problem in obtaining multiple connection in a single tra
              palash.you Nov 13, 2009 6:20 AM (in response to palash.you)

              The problem has been resolved so I am sharing my workaround and the knowledge that we got during study

              Distributed transactions support in JBOSS+MySQL+Linux

              Worked with :-
              1.MySQL version: MySQL community 5.0.45
              2.Application Server: JBoss 4.2.3 GA
              3.MySQL Driver: mysql-connector-java-5.0.8-bin.jar

              XA MySQL Datasource used

<xa-datasource>
<jndi-name>jdbc/FON_FRAMEWORKDS</jndi-name>
<use-java-context>false</use-java-context>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
<xa-datasource-property name="URL">jdbc:mysql://*.*.*.*:3306/npd_fw_common</xa-datasource-property>
<xa-datasource-property name="User">XXX</xa-datasource-property>
<xa-datasource-property name="Password">XXX</xa-datasource-property>
<track-connection-by-tx/>
<metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
<!--the minimum size of the connection pooL-->
<min-pool-size>10</min-pool-size>
<!--The maximum connections in a pool/sub-pool-->
<max-pool-size>50</max-pool-size>
</xa-datasource>


              Remember Tag <track-connection-by-tx/> is mandatory for XA distributed transactions. This is required because connection is tracked with the transaction and hence not released to the connection pool till commit/rollback is applied. Connection will be in idle state till prepare is not applied.





              2 Phase protocol
              When a transaction involves multiple distributed resources, for example, a database server on each of two different network hosts, the commit process is somewhat complex because the transaction includes operations that span two distinct software systems, each with its own resource manager, log records, and so on. (In this case, the distributed resources are the database servers.)
              Two-phase commit is a transaction protocol designed for the complications that arise with distributed resource managers. With a two-phase commit protocol, the distributed transaction manager employs a coordinator to manage the individual resource managers.
              The commit process proceeds as follows:
              Phase 1
              Each participating resource manager coordinates local operations and forces all log records out:
              If successful, respond "OK"
              If unsuccessful, either allow a time-out or respond "OOPS"
              Phase 2
              If all participants respond "OK":
              Coordinator instructs participating resource managers to "COMMIT"
              Participants complete operation writing the log record for the commit
              Otherwise:
              Coordinator instructs participating resource managers to "ROLLBACK"
              Participants complete their respective local undos



              Useful Links

              1) Details of different datasource tags for XA Transactions
              http://www.jboss.org/community/wiki/ConfigDataSources

              2) Details on Tag track-connection-by-tx
              http://www.redhat.com/docs/manuals/jboss/jboss-eap-4.2/doc/Server_Configuration_Guide/Connectors_on_JBoss-Configuring_JDBC_DataSources.html

              3. Mysql XA states
              http://dev.mysql.com/doc/refman/5.0/en/xa-states.html



                • Actions