Distributed Transaction with WSO2 Data Services

What is distributed transaction ?

Distributed database transaction means executing multiple related actions/operations in a coordinated way. This is also known as  global transaction. Distributed transaction can occur in the same database level however, in most cases distributed transaction happens in different databases (typically different RDBMS types) and often in different locations. Distributed transactions are often described as ACID -- atomic, consistent, isolated, and durable.  Meaning changes made to the database during the transactions are tentative, if any of the operation fails then none of the other changes will get affected. In a typical distributed transaction, you have to make sure if one operation fails the whatever previously executed operation should roll back, undoing all the changes as if the transactions never took place. Even if your application crashes in the middle of the transaction, when it restarts, transaction recovery should roll back the open transaction.

A typical Distributed transaction example would be moving data from one data base to another database. For example lets say you want to delete customer from one location and add that same customer to the another location.You would not want either transaction committed without assurance that both will complete successfully. Therefore, for these kind of instances its important to have distributed transaction feature.

The above transactions involve the following steps:

1. Begin a transaction. 
2. Add Customer
3. Delete Customer
4. End transaction

Distributed Transaction with WSO2 Data Services Server

WSO2 data services server provide distributed transaction using Java Transaction API (JTA) which enables global level transaction across multiple X/Open XA resources in java environment.

When you use XA functionality, the transaction manager uses XA resource instances to prepare and coordinate each transaction branch and then to commit or roll back all each of individual transaction appropriately.

For each RDBMS type there is a specific XA-Datasource class and set of configuration properties. Therefore you need to know the XA-Datasource class and their configurations before creating the data service.

Lets see how we can create a data service for the above transaction.

For this example I will have two databases in postgres and mysql. For this demo I will be using a very simple customer table which has id and name and we will see how we can inset values to these two tables in each database in a coordinated manner

=========================== SQL script ==============================

CREATE TABLE customer (
cust_id int NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (cust_id)
)

========================data service configuration file======================

<data disableStreaming="true" enableBoxcarring="true" enableDTP="true" name="DTPDS">
   <config id="pos_ds">
      <property name="org.wso2.ws.dataservice.xa_datasource_class">org.postgresql.xa.PGXADataSource</property>
      <property name="org.wso2.ws.dataservice.xa_datasource_properties">
         <property name="ServerName">localhost</property>
         <property name="PortNumber">5432</property>
         <property name="DatabaseName">MyDB</property>
         <property name="User">postgres</property>
         <property name="Password">root</property>
      </property>
   </config>
   <config id="my_ds">
     <property name="org.wso2.ws.dataservice.xa_datasource_class">com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</property>
      <property name="org.wso2.ws.dataservice.xa_datasource_properties">
         <property name="URL">jdbc:mysql://localhost:3306/MyDB</property>
         <property name="User">root</property>
         <property name="Password">root</property>
      </property>
   </config>
   <query id="pos_q" useConfig="pos_ds">
      <sql>INSERT INTO customer VALUES(?,?)</sql>
      <param name="id" sqlType="INTEGER"/>
      <param name="name" sqlType="STRING"/>
   </query>
   <query id="my_q" useConfig="my_ds">
      <sql>INSERT INTO customer VALUES(?,?)</sql>
      <param name="id" sqlType="INTEGER"/>
      <param name="name" sqlType="STRING"/>
   </query>
   <operation disableStreaming="true" name="pos_insert" returnRequestStatus="true">
      <call-query href="pos_q">
         <with-param name="id" query-param="id"/>
         <with-param name="name" query-param="name"/>
      </call-query>
   </operation>
   <operation disableStreaming="true" name="my_insert" returnRequestStatus="true">
      <call-query href="my_q">
         <with-param name="id" query-param="id"/>
         <with-param name="name" query-param="name"/>
      </call-query>
   </operation>
</data>

You need to add the configuration file to WSO2DS_HOME/repository/deployment/server/dataservices in order to deploy this file

Step by step explanation

Data source configuration

I have created two data source configuration in the data service descriptor file

 <config id="pos_ds">
      <property name="org.wso2.ws.dataservice.xa_datasource_class">org.postgresql.xa.PGXADataSource</property>
      <property name="org.wso2.ws.dataservice.xa_datasource_properties">
         <property name="ServerName">localhost</property>
         <property name="PortNumber">5432</property>
         <property name="DatabaseName">MyDB</property>
         <property name="User">postgres</property>
         <property name="Password">root</property>
      </property>
   </config>
   <config id="my_ds">
     <property name="org.wso2.ws.dataservice.xa_datasource_class">com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</property>
      <property name="org.wso2.ws.dataservice.xa_datasource_properties">
         <property name="URL">jdbc:mysql://localhost:3306/MyDB</property>
         <property name="User">root</property>
         <property name="Password">root</property>
      </property>
   </config>

There we have specified the XA datasource classes "org.wso2.ws.dataservice.xa_datasource_class" in a property along with it's parameters. For each XA datasource class configuration properties may differ.

Query Configuration

  <query id="pos_q" useConfig="pos_ds">
      <sql>INSERT INTO customer VALUES(?,?)</sql>
      <param name="id" sqlType="INTEGER"/>
      <param name="name" sqlType="STRING"/>
   </query>
   <query id="my_q" useConfig="my_ds">
      <sql>INSERT INTO customer VALUES(?,?)</sql>
      <param name="id" sqlType="INTEGER"/>
      <param name="name" sqlType="STRING"/>
   </query>

I have created two queries pointing each data source, and also two distinct opperations mapping to each query.

<operation disableStreaming="true" name="pos_insert" returnRequestStatus="true">
      <call-query href="pos_q">
         <with-param name="id" query-param="id"/>
         <with-param name="name" query-param="name"/>
      </call-query>
   </operation>
   <operation disableStreaming="true" name="my_insert" returnRequestStatus="true">
      <call-query href="my_q">
         <with-param name="id" query-param="id"/>
         <with-param name="name" query-param="name"/>
      </call-query>
   </operation>

After you deploy this data service. You will see the deployed data service under the data service list. You can invoke this service using the try-it tool (or using your own class)

Invoke the operations in this order

1. begin_boxcar 
2. my_insert
3. pos_insert
4. end_boxcar

Please make sure you have set the  "max_prepared_transactions" to a non zero value in "/etc/postgres/postgres.conf in oorder this sample to work

In my next post, I will be explaining how we can call all these four operations using a single proxy service with the use of of WSO2 ESB.