What is Distributed Transaction ?
Transaction that access and update two or more resources across network.
Example: Different database(s) (MySQL, Oracle, Sybase etc) located on single server or single instance of the database(s) located on different servers (Server1, Server2 ...) or combination of above.
How we can achieve distributed transaction in java ?
We can achieve distributed transaction using the XA datasource and JTA.
Important Interfaces:
There are three important interfaces:
1) UserTransaction - (javax.transaction.UserTransaction)
It provides application the ability to controls the trasaction boundary programatically. It starts the global transaction and associates that transaction to the calling thread.
2) Transaction Manager - (javax.transaction.TransactionManager)
This interface allows application server to controls the transaction boundary on behalf of application being managed. Transaction manager is responsible whether to commit or rollback any distributed transaction.
3) XAResource— (javax.transaction.xa.XAResource)
Is a Java mapping of the industry standard XA interface.
Note: JDBC driver should support XAResource portion of JTA.
How distributed transaction works?
Application send request to transaction manager to start the transaction via usertransaction object. Once the transaction is started it will associate itself to the current thread.
After that application can access different datasources belonging to different databases or same databases located on single or multiple servers.
Transaction manager treat the whole unit as one single logical unit irrespective of different database and will commit in case of sucess or rollback if any exception/s.
Implementation:
We implement in JBOSS and Tomcat Servers using mysql as database server.
Use Case:
User will enter id and name which will be stored into two different mysql databases. Table structure will be same in both database except one database will have primary key configured, so when user enters duplicate value it will throw sql exception.
SQL Script:
Database: DB1
CREATE TABLE `xadb`.`EmpTest` (
`id` int NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB;
Database: DB2
CREATE TABLE `EmpTest` (
`id` int NOT NULL,
`name` varchar(50) NOT NULL
)
ENGINE = InnoDB;
Directory Structure of war file:
xadir
|------ input.jsp
|------ bl.jsp
|------ WEB-INF (Folder)
|---- web.xml
|---- lib (Folder)
|---- classes (Folder)
|---- foo (Folder)
|--- XATest.java
JSP Code:
input.jsp
bl.jsp
Java Code:
package foo;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.transaction.SystemException;
import javax.transaction.UserTransaction;
public class XATest{
public String insertData(int id, String name) {
UserTransaction ut = null;
String returnStr = null;
Connection conn1 = null;
Connection conn2 = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
//Creating the context
Context ctx = new InitialContext();
//Getting datasource
DataSource ds1 = (DataSource)ctx.lookup("java:ds1");
DataSource ds2 = (DataSource)ctx.lookup("java:ds2");
//Getting the user transaction
ut = (UserTransaction)ctx.lookup("java:comp/UserTransaction");
//Starting the transaction
ut.begin();
printStr("Transaction Started .....................");
//Processing First Database
conn1 = ds1.getConnection();
pstmt1 = conn1.prepareStatement("insert into EmpTest values ( ?,?)");
pstmt1.setInt(1, id);
pstmt1.setString(2, name);
int exeVal = pstmt1.executeUpdate();
printStr("Statement Executed: "+exeVal);
//Processing Second Database
conn2 = ds2.getConnection();
pstmt2 = conn2.prepareStatement("insert into EmpTest values ( ?,?)");
pstmt2.setInt(1, id);
pstmt2.setString(2, name);
exeVal = pstmt2.executeUpdate();
printStr("Statement Executed: "+exeVal);
printStr("Committing the transaction ...... ");
ut.commit();
pstmt1.close();
pstmt2.close();
conn1.close();
conn2.close();
returnStr = "Transaction Successful";
}catch(Exception e) {
returnStr = "Transaction Failed";
e.printStackTrace();
try {
ut.rollback();
} catch (IllegalStateException ex) {
ex.printStackTrace();
} catch (SecurityException ex) {
ex.printStackTrace();
} catch (SystemException ex) {
ex.printStackTrace();
}
} finally {
try {
pstmt1.close();
pstmt2.close();
conn1.close();
conn2.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return returnStr;
}
private void printStr(String s) {
System.out.println(s);
}
}
)
Implementing in JBOSS (jboss-4.2.2):
1) create the file mysql-xa-ds.xml under jboss_home/server/default/deploy folder.
2) Add the driver jar file (mysql-connector-java-5.0.8-bin.jar) in jboss_home/server/default/lib folder.
3) Add the following to the file created in step-1
Note: When the JBOSS start make sure that JNDI are created properly.
4) create the war file and deploy the same in JBOSS.
5) Enter the test data.
a) Id= 1 Name = Milind
Result --Trasaction Sucessful
b) Id= 2 Name = Guest
Result --Trasaction Sucessful
c) Id= 1 Name = Guest1
Result --Trasaction Failed Reason -- Primary Key violation.
Check the table in DB1 and DB2 database both should not have record of 1,Guest1
