Transaction Mediator Example

The Transaction mediator supports distributed transactionsusing Java transaction API (JTA). Distributed transactions access and update data on two or more networked computer resources (an example would be to have two databases or a database and a message queue such as JMS). You can use the Synapse configuration language to define the start and end of your transaction, pause transactions, and more. It is the responsibility of the user to define when to start, commit, or roll back the transaction. For example, you can mark the start of a transaction at the start of a database commit, end of the transaction at the end of the database commit, and mark a transaction for rollback if a failure occurs.

Transaction Mediator Scenario

Use the following scenario to see how the transaction mediator works. Assume we have a record in one database and we want to delete that record from the first database and add it to the second database (these two databases can be run on the same server or they can be in two remote servers). The database tables are defined in such a way that the same entry cannot be added twice. So, in the successful scenario, the record will be deleted from the first table (of the first database) and will be added to the second table (of the second database). In a failure scenario (the record is already in the second database), no record will be deleted from first table and no record will be added into the second database.

System Requirements

Note

This scenario applies to version 3.0. In versions after, there is a Transaction Manager in Carbon itself, so you don't have to put the ESB in an application server to get it working.

Windows, Linux or Solaris operating systems with WSO2 ESB installed.
Derby Database (db-derby-10.5.1.1-bin version was used).
Apache ActiveMQ 5.3.1.

Running the Example

Copy the Derby client JARs (derby.jar, derbynet.jar, and derbyclient.jar) from $DERBY_HOME/lib into <ESB_HOME>/repository/components/lib.

Copy and paste the following configuration into the Synapse configuration in $ESB_HOME/repository/conf/synapse-config/synapse.xml.
We use here a sample similar to sample 361. In the "In" sequence we will send a message to the service, and in the "Out" sequence we will delete an entry from the first database and update the second database with that entry. If we try to add an entry that is already in the second database, the whole transaction will roll back.

<definitions xmlns="http://ws.apache.org/ns/synapse">
   <sequence name="myFaultHandler">
        <log level="custom">
            <property name="text" value="** Rollback Transaction**"/>
        </log>
        <transaction action="rollback"/>
        <send/>
    </sequence>
    <sequence name="main" onError="myFaultHandler">
        <in>
            <send>
                <endpoint>
                    <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                </endpoint>
            </send>
        </in>
         <out>
            <transaction action="new"/>
            <log level="custom">
                <property name="text" value="** Reporting to the Database esbdb**"/>
            </log>
            <dbreport useTransaction="true" xmlns="http://ws.apache.org/ns/synapse">
                <connection>
                    <pool>
                        <dsName>java:jdbc/XADerbyDS</dsName>
                        <icClass>org.jnp.interfaces.NamingContextFactory</icClass>
                        <url>localhost:1099</url>
                        <user>esb</user>
                        <password>esb</password>
                    </pool>
                </connection>
                <statement>
                     <sql>delete from company where name =?</sql>
                     <parameter expression="//m0:return/m1:symbol/child::text()"
                       xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
                                 type="VARCHAR"/>
                </statement>
            </dbreport>
            <log level="custom">
                <property name="text" value="** Reporting to the Database esbdb1**"/>
            </log>
            <dbreport useTransaction="true" xmlns="http://ws.apache.org/ns/synapse">
                <connection>
                    <pool>
                        <dsName>java:jdbc/XADerbyDS1</dsName>
                        <icClass>org.jnp.interfaces.NamingContextFactory</icClass>
                        <url>localhost:1099</url>
                        <user>esb</user>
                        <password>esb</password>
                    </pool>
                </connection>
                <statement>
                    <sql>INSERT into company values (?,'c4',?)</sql>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
         xmlns:m1="http://services.samples/xsd" xmlns:m0="http://services.samples"
                               type="VARCHAR"/>
                    <parameter expression="//m0:return/m1:last/child::text()"
         xmlns:m1="http://services.samples/xsd" xmlns:m0="http://services.samples"
                               type="DOUBLE"/>
                </statement>
            </dbreport>
            <transaction action="commit"/>
            <send/>
        </out>
    </sequence>
</definitions>

To run the sample, you need two distributed Derby databases ("esbdb" and "esbdb1"); see Setting up with Derby for details on setting up the databases. The database table was created using the following SQL query. Note that in the table schema, you cannot have the same entry twice.
```
CREATE table company(name varchar(10) primary key, id varchar(10), price double);
```

Add a few records to the two tables as follows (the order of the records matters):
Database1:

INSERT into company values ('IBM','c1',0.0);
INSERT into company values ('SUN','c2',0.0);

Database2:

INSERT into company values ('SUN','c2',0.0);
INSERT into company values ('MSFT','c3',0.0);

In the master-datasources.xml file located at $CARBON_HOME/repository/conf/datasources, create data source declarations for the distributed databases, ensuring that the datasource file names are *-xa-ds.xml:
Datasource1: esb-derby-xa-ds.xml

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
    <xa-datasource>
        <jndi-name>jdbc/XADerbyDS</jndi-name>
        <isSameRM-override-value>false</isSameRM-override-value>
        <xa-datasource-class>org.apache.derby.jdbc.ClientXADataSource</xa-datasource-class>
        <xa-datasource-property name="portNumber">1527</xa-datasource-property>
        <xa-datasource-property name="DatabaseName">esbdb</xa-datasource-property>
        <xa-datasource-property name="User">esb</xa-datasource-property>
        <xa-datasource-property name="Password">esb</xa-datasource-property>
        <metadata>
            <type-mapping>Derby</type-mapping>
        </metadata>
    </xa-datasource>
</datasources>

Datasource2: esb-derby1-xa-ds.xml

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
    <xa-datasource>
        <jndi-name>jdbc/XADerbyDS1</jndi-name>
        <isSameRM-override-value>false</isSameRM-override-value>
        <xa-datasource-class>org.apache.derby.jdbc.ClientXADataSource</xa-datasource-class>
        <xa-datasource-property name="portNumber">1527</xa-datasource-property>
        <xa-datasource-property name="DatabaseName">esbdb1</xa-datasource-property>
        <xa-datasource-property name="User">esb</xa-datasource-property>
        <xa-datasource-property name="Password">esb</xa-datasource-property>
        <metadata>
            <type-mapping>Derby</type-mapping>
        </metadata>
    </xa-datasource>
</datasources>

Try the samples. Refer to sample set up guide to learn how to set up the server. Deploy the SimpleStockQuote service that comes with the WSO2 ESB samples.

Successful Scenario

To remove the IBM record from the first database and add it to the second database, run the sample with the following options.

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService
-Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

Check both databases to see how the record is deleted from the first database and added to the second database.

Failure Scenario

Try to add an entry that already exists in the second database. This time use the symbol SUN.

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService
-Dtrpurl=http://localhost:8280/ -Dsymbol=SUN

You will see how the fault sequence is executed and the whole transaction rolls back. Check both databases again; there is no record deleted from the first database and no record added into the second database.