Distributed Transactions

Implementing a Distributed Transaction

This section provides an example of how to implement a distributed transaction using Oracle XA functionality.

Summary of Imports for Oracle XA

You must import the following for Oracle XA functionality:

import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

The oracle.jdbc.pool package has classes for connection pooling functionality, some of which are subclassed by XA-related classes.

In addition, if the code will run inside an Oracle database and access that database for SQL operations, you must import the following:

import oracle.jdbc.xa.server.*;

(And if you intend to access only the database in which the code runs, you would not need the oracle.jdbc.xa.client classes.)

The client and server packages each have versions of the OracleXADataSource, OracleXAConnection, and OracleXAResource classes. Abstract versions of these three classes are in the top-level oracle.jdbc.xa package.

Oracle XA Code Sample

This example uses a two-phase distributed transaction with two transaction branches, each to a separate database.

Note that for simplicity, this example combines code that would typically be in a middle tier with code that would typically be in a transaction manager (such as the XA resource method invocations and the creation of transaction IDs).

For brevity, the specifics of creating transaction IDs (in the createID() method) and performing SQL operations (in the doSomeWork1() and doSomeWork2() methods) are not shown here. The complete sample is in "XA with Two-Phase Commit Operation--XA4.java".

For another complete sample, showing how to use XA resource functionality to suspend and resume a transaction, see "XA with Suspend and Resume--XA2.java".

This example executes the following sequence:

Start transaction branch #1.
Start transaction branch #2.
Execute DML operations on branch #1.
Execute DML operations on branch #2.
End transaction branch #1.
End transaction branch #2.
Prepare branch #1.
Prepare branch #2.
Commit branch #1.

Commit branch #2.

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

class XA4
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        String URL1 = "jdbc:oracle:oci8:@";
        String URL2 ="jdbc:oracle:thin:@(description=(address=(host=dlsun991)
                     (protocol=tcp)(port=5521))(connect_data=(sid=rdbms2)))";

        DriverManager.registerDriver(new OracleDriver());

        // You can put a database name after the @ sign in the connection URL.
        Connection conna =
          DriverManager.getConnection (URL1, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmta = conna.createStatement ();

        Connection connb =
          DriverManager.getConnection (URL2, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmtb = connb.createStatement ();

        try
        {
          // Drop the test table
          stmta.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmta.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        try
        {
          // Drop the test table
          stmtb.execute ("drop table my_tab");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmtb.execute ("create table my_tab (col1 char(30))");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create XADataSource instances and set properties.
        OracleXADataSource oxds1 = new OracleXADataSource();
        oxds1.setURL("jdbc:oracle:oci8:@");
        oxds1.setUser("scott");
        oxds1.setPassword("tiger");

        OracleXADataSource oxds2 = new OracleXADataSource();

        oxds2.setURL("jdbc:oracle:thin:@(description=(address=(host=dlsun991)
                   (protocol=tcp)(port=5521))(connect_data=(sid=rdbms2)))");
        oxds2.setUser("scott");
        oxds2.setPassword("tiger");
    
        // Get XA connections to the underlying data sources
        XAConnection pc1  = oxds1.getXAConnection();
        XAConnection pc2  = oxds2.getXAConnection();

        // Get the physical connections
        Connection conn1 = pc1.getConnection();
        Connection conn2 = pc2.getConnection();

        // Get the XA resources
        XAResource oxar1 = pc1.getXAResource();
        XAResource oxar2 = pc2.getXAResource();

        // Create the Xids With the Same Global Ids
        Xid xid1 = createXid(1);
        Xid xid2 = createXid(2);

        // Start the Resources
        oxar1.start (xid1, XAResource.TMNOFLAGS);
        oxar2.start (xid2, XAResource.TMNOFLAGS);

        // Execute SQL operations with conn1 and conn2
        doSomeWork1 (conn1);
        doSomeWork2 (conn2);

        // END both the branches -- IMPORTANT
        oxar1.end(xid1, XAResource.TMSUCCESS);
        oxar2.end(xid2, XAResource.TMSUCCESS);

        // Prepare the RMs
        int prp1 =  oxar1.prepare (xid1);
        int prp2 =  oxar2.prepare (xid2);

        System.out.println("Return value of prepare 1 is " + prp1);
        System.out.println("Return value of prepare 2 is " + prp2);

        boolean do_commit = true;

        if (!((prp1 == XAResource.XA_OK) || (prp1 == XAResource.XA_RDONLY)))
           do_commit = false;

        if (!((prp2 == XAResource.XA_OK) || (prp2 == XAResource.XA_RDONLY)))
           do_commit = false;

       System.out.println("do_commit is " + do_commit);
        System.out.println("Is oxar1 same as oxar2 ? " + oxar1.isSameRM(oxar2));

        if (prp1 == XAResource.XA_OK)
          if (do_commit)
             oxar1.commit (xid1, false);
          else
             oxar1.rollback (xid1);

        if (prp2 == XAResource.XA_OK)
          if (do_commit)
             oxar2.commit (xid2, false);
          else
             oxar2.rollback (xid2);

         // Close connections
        conn1.close();
        conn1 = null;
        conn2.close();
        conn2 = null;

        pc1.close();
        pc1 = null;
        pc2.close();
        pc2 = null;

        ResultSet rset = stmta.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));
  
        rset.close();
        rset = null;

        rset = stmtb.executeQuery ("select col1 from my_tab");
        while (rset.next())
          System.out.println("Col1 is " + rset.getString(1));
  
        rset.close();
        rset = null;

        stmta.close();
        stmta = null;
        stmtb.close();
        stmtb = null;

        conna.close();
        conna = null;
        connb.close();
        connb = null;

    } catch (SQLException sqe)
    {
      sqe.printStackTrace();
    } catch (XAException xae)
    {
      if (xae instanceof OracleXAException) {
        System.out.println("XA Error is " +
                      ((OracleXAException)xae).getXAError());
        System.out.println("SQL Error is " +
                      ((OracleXAException)xae).getOracleError());
      }
    }
  }

  static Xid createXid(int bids)
    throws XAException
  {...Create transaction IDs...}

  private static void doSomeWork1 (Connection conn)
   throws SQLException
  {...Execute SQL operations...}

  private static void doSomeWork2 (Connection conn)
   throws SQLException
  {...Execute SQL operations...}

}