Distributed transaction examples|V2.4.5|OceanBase Connector/J| docs|Distributed Database

Distributed transaction examples

Last Updated：2023-12-14 07:26:05 Updated

This topic describes how to use the Oracle XA feature to implement distributed transactions.

Import Oracle XA

To use the Oracle XA feature, execute the following statements to import related objects:

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 the connection pool feature, with XA-related classes as subclasses.

Alternatively, if the code is to be run inside the database and you need to access the database for SQL operations, import oracle.jdbc.xa.server instead of oracle.jdbc.xa.client. The syntax is as follows:

import oracle.jdbc.xa.server.*;

The client and server software packages provide versions of the OracleXADataSource, OracleXAConnection, and OracleXAResource classes. The abstract versions of these three classes are in the oracle.jdbc.xa package at the top level.

Oracle XA code examples

The following takes the processing of XA transactions for example:

Use XA START to start XA transactions and set them to be in the ACTIVE state.
For an XA transaction in the ACTIVE state, execute the SQL statements that constitute the transaction and then execute the XA END statement. The XA END statement sets the transaction to be in the IDLE state.
For an XA transaction in the IDLE state, you can execute the XA PREPARE or XA COMMIT ... ONE PHASE statement.
- The XA PREPARE statement sets the transaction to be in the PREPARED state. In this case, the XA RECOVER statement includes the value of xid of the transaction in its output, because XA RECOVER lists all XA transactions in the PREPARED state.
- The XA COMMIT ... ONE PHASE statement prepares and commits transactions. In this case, the XA RECOVER statement does not list the values of xid because the transactions are terminated.
For an XA transaction in the PREPARED state, you can execute the XA COMMIT statement to commit and terminate the transaction, or execute XA ROLLBACK to roll back and terminate the transaction.

public static void initClass() throws SQLException {
    createTable(tableName1, "c1 int,c2 int");
    createTable(tableName2, "c1 int,c2 int");
    createTable(tableName3, "c1 varchar(200)");
}

public void obOracleXAOne() throws Exception {
    Assume.assumeTrue(sharedUsePrepare());
    Connection conn = setConnection();  
    conn.createStatement().execute(" insert into " + tableName1 + "  values(1,2)");
    JDBC4MysqlXAConnection mysqlXAConnection = new JDBC4MysqlXAConnection(
        (OceanBaseConnection) conn);
    String gtridStr = "gtrid_test_wgs_ob_oracle_xa_one";
    String bqualStr = "bqual_test_wgs_ob_oracle_xa_one";
    Xid xid = new MysqlXid(gtridStr.getBytes(), bqualStr.getBytes(), 123);
    try {
        mysqlXAConnection.start(xid, XAResource.TMNOFLAGS);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        pstmt = conn.prepareStatement("select c1 from " + tableName1);
        rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
        pstmt.close();
        pstmt = conn.prepareStatement("insert into " + tableName1 + " (c1, c2) values(?, ?)");
        pstmt.setInt(1, 12);
        pstmt.setInt(2, 12);
        pstmt.executeUpdate();
        mysqlXAConnection.end(xid, XAResource.TMSUCCESS);
        mysqlXAConnection.prepare(xid);
        mysqlXAConnection.commit(xid, false);
    } catch (Exception e) {
        e.printStackTrace();
        mysqlXAConnection.rollback(xid);
        throw e;
    }
}

public void obOracleXAOnePhase() throws Exception {
    Assume.assumeTrue(sharedUsePrepare());
    Connection conn = null;

    conn = setConnection();
    conn.createStatement().execute(" insert into " + tableName2 + "  values(1,2)");

    JDBC4MysqlXAConnection mysqlXAConnection = new JDBC4MysqlXAConnection(
        (OceanBaseConnection) conn);
    String gtridStr = "gtrid_test_wgs_ob_oracle_xa_one_phase";
    String bqualStr = "bqual_test_wgs_ob_oracle_xa_one_phase";

    Xid xid = new MysqlXid(gtridStr.getBytes(), bqualStr.getBytes(), 123);
    try {
        mysqlXAConnection.start(xid, XAResource.TMNOFLAGS);
        // ps test
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        pstmt = conn.prepareStatement("select c1 from " + tableName2 + "");
        rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

        pstmt.close();

        pstmt = conn.prepareStatement("insert into " + tableName2 + " (c1, c2) values(?, ?)");
        pstmt.setInt(1, 12);
        pstmt.setInt(2, 12);
        pstmt.executeUpdate();

        mysqlXAConnection.end(xid, XAResource.TMSUCCESS);
        mysqlXAConnection.commit(xid, true);
    } catch (Exception e) {
        mysqlXAConnection.rollback(xid);
        throw e;
    }
}

Note

Call setConnection() to concatenate useServerPrepStmts=true to the URL in the format of jdbc:oceanbase://xx:xx/xx?useServerPrepStmts=true&....