How to end pending transactions with jdbc

From: Matthias <geiss_at_in.tum.de>
Date: 16 Sep 2002 11:17:27 -0700
Message-ID: <42327577.0209161017.78a3b8c4@posting.google.com>

Hi,

I use the oracle.jdbc.xa package to controll transactions. The normal usage works fine but if I simulate a "crash" after a transaction has been prepared I cannot end this transaction later on due to a the XAException "The XID is not valid". (Accessing a 8.1.7 Oracle server with the 8.1.7 JDBC thin driver)

I use the class below to test this and the error occurs only if I wait for a while between the two executions ("prepare" and "end").

If I execute 'java XARecovery "prepare"' and directly afterwards 'java XARecovery "end"' everything works fine and the transaction is rolled back. So I conclude that the xid and test program itself cannot be that faulty.

If I wait inbetween for about a minute the execution of 'java XARecovery "end"' is unsuccessfull. The invocation of xaRes.rollback(xid); throws the XAException "The XID is not valid". Precisely I get the following output:

XA Error is -4
XA Error message The XID is not valid.
SQL Error is 24756
oracle.jdbc.xa.OracleXAException

	at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:498)
	at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:617)
	at XARecovery.main(XARecovery.java:85)

I find an entry in the view SYS.DBA_PENDING_TRANSACTIONS with exactly the same information (formatid.globaltransactionid and branchid) like the xid in my test program. (What I checked by using the XAResource recover method and comparing the recovered xid with the created one)

Why can't the transaction be rolled back then???

Thanks for your help,
Matthias

/*
Test to use one Xid for a transaction that gets prepared in a first run and
ended only in a second run.
I use it like this (obviously with the oracle driver in the classpath) java XARecovery "prepare"
java XARecovery "end"
*/

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 XARecovery
{

    // the connection data is secret of course

    private static final String db_url = "jdbc:oracle:thin:@...";
    private static final String db_username = "...";
    private static final String db_password = "...";


    public static void main (String args [])
            throws SQLException
    {
        Connection helpConn = null;
        Statement helpStmt = null;

        XAConnection xaConn  = null;
        Connection conn1 =  null;
        XAResource xaRes =  null;

        try
        {
            DriverManager.registerDriver(new OracleDriver());

            helpConn =
                    DriverManager.getConnection (db_url, db_username,
db_password);
            helpStmt = helpConn.createStatement ();



            // Create the Xid

            Xid xid = createXid(8, 1);



            // Create a XADataSource instance

            OracleXADataSource oxds = new OracleXADataSource();
            oxds.setURL(db_url);
            oxds.setUser(db_username);
            oxds.setPassword(db_password);


            // Get a XA connection to the underlying data source

            xaConn  = oxds.getXAConnection();
            conn1 = xaConn.getConnection();
            xaRes = xaConn.getXAResource();

            if (args[0].equals("prepare")){

                // prepare the test table
                try {
                    helpStmt.execute ("drop table my_table");
                } catch (SQLException e) {
                }
                try {
                    helpStmt.execute ("create table my_table (col1
int)");
                } catch (SQLException e) {
                }

                // Start a Tx, do something, end it
                xaRes.start(xid, XAResource.TMNOFLAGS);
                DoSomeWork (conn1);
                xaRes.end(xid, XAResource.TMSUCCESS);
                // do the 2PC prepare
                int prp =  xaRes.prepare (xid);
                System.out.println("Return value of prepare is " +

prp);

            } else if (args[0].equals("end")){
                // I could call the xaRes.recovery() method to get the
                // xid again, but I have a fixed one anyway
                xaRes.rollback(xid);
                System.out.println("Transaction rolled back.");
            }

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


            // Close connections

            if (helpStmt != null) helpStmt.close();
            if (helpConn != null) helpConn.close();
            if (conn1 != null) conn1.close();
            if (xaConn != null) xaConn.close();
        }

    }

    static Xid createXid(int gids, int bids)

            throws XAException
    {

        byte[] gid = new byte[1]; gid[0]= (byte) gids;
        byte[] bid = new byte[1]; bid[0]= (byte) bids;
        byte[] gtrid = new byte[64];
        byte[] bqual = new byte[64];
        System.arraycopy (gid, 0, gtrid, 0, 1);
        System.arraycopy (bid, 0, bqual, 0, 1);
        Xid xid = new OracleXid(0x1234, gtrid, bqual);
        return xid;

    }

    private static void DoSomeWork (Connection conn)

            throws SQLException
    {

        // Insert 4321 into my_table
        Statement stmt = conn.createStatement ();
        int cnt = stmt.executeUpdate ("insert into my_table values
(4321)");
        System.out.println("No of rows Affected " + cnt);
        stmt.close();
        stmt = null;

    }
} Received on Mon Sep 16 2002 - 13:17:27 CDT