Configuring XA transactions with mysql database in ESB

In this example scenario I'm showing you how to configure XA-Transaction with ESB using mysql database. First you need to login to the mysql database and create a database call "test". Inside that database execute the following command.

CREATE TABLE Persons
(
PersonID int,
Name varchar(255)
);

Use case:

1. create new transaction
2. insert into database
3. Call a rest endpoint. 
4. When response is received commit the  transaction.

When configuring XA transactions the best practice is to use datasource class name instead of using the driver class name. Please follow the below instructions to configure master-datasources.xml with datasource class name.

• Open <ESB_HOME>/repository/conf/datasources/master-datasources.xml.
• Add the below configuration under <datasources> tag. (You can change the datasource name to any name you like.)

<datasource>
    <name>MYSQLDS</name>
    <jndiConfig>
        <name>jdbc/MYSQLDS</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <dataSourceClassName>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</dataSourceClassName>
            <defaultAutoCommit>false</defaultAutoCommit>
            <maxActive>150</maxActive>
            <InitialSize>100</InitialSize>
            <maxWait>60000</maxWait>
            <validationQuery>select 1</validationQuery>
            <validationInterval>30000</validationInterval>
            <testWhileIdle>true</testWhileIdle>
            <maxIdle>25</maxIdle>
            <minIdle>20</minIdle>
            <removeAbandonedTimeout>60</removeAbandonedTimeout>
            <removeAbandoned>true</removeAbandoned>
            <logAbandoned>true</logAbandoned>
            <testOnBorrow>true</testOnBorrow>
            <accessToUnderlyingConnectionAllowed>true</accessToUnderlyingConnectionAllowed>
            <dataSourceProps>
                <property name="serverName">localhost</property>
                <property name="portNumber">3306</property>
                <property name="databaseName">test</property>
                <property name="user">root</property>
                <property name="password">root</property>
            </dataSourceProps>
        </configuration>
    </definition>
</datasource>

• Copy mysql driver library to <ESB_HOME>/repository/components/lib folder. 
• Re-start the ESB server. 

Below is the ESB API configuration for this use case.

 <api context="/bpmn" name="xa_transaction_example">

        <resource faultSequence="faultSequenceREST" methods="POST" uri-template="/test">

            <inSequence>

                <log level="custom">

                    <property name="text" value="** In Sequencce started**"/>

                </log>

                <transaction action="new"/>

                <dbreport useTransaction="true">

                    <connection>

                        <pool>

                            <dsName>jdbc/MYSQLDS</dsName>

                        </pool>

                    </connection>

                    <statement>

                        <sql><![CDATA[INSERT INTO Persons (PersonID,Name) VALUES ('1234','Tom')]]></sql>

                    </statement>

                </dbreport>

                <call blocking="true">

                    <endpoint>

                        <address uri="http://www.mocky.io/v2/577f4bc2100000152df26247"/>

                    </endpoint>

                </call>

                <log level="full"/>

                <transaction action="commit"/>

                <log level="custom">

                    <property name="text" value="**Transaction commited**"/>

                </log>

                <respond/>

            </inSequence>

        </resource>

    </api>