db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: how can I force a rollback of an XA transaction - is there a way to get the database into this same state
Date Tue, 06 Dec 2011 18:20:47 GMT
I need a test program to get the database into this same state.  I have tried the following
in attempt to get the database into the state.  If I query syscs_diag.transaction_table, I
see

but if I restart the database service, the ACTIVE transaction is no longer present.  So it
seems that this does not get the database into the same state where there are ACTIVE XA transactions
but no connections associated with these transactions.

In trying to write a utility, I need first to get the database into the same state.  I cannot
risk shutting down the customer's system if it is going to take hours to come back up.  I
need some way of either committing or rolling back these transactions while the system is
up and running.

    public static void main(String[] args) {
        // Create a variable for the connection string.
        String connectionUrl = "jdbc:derby://localhost:1527/csemdb" + ";user=CSEM;password=CSEM";

        try {
            // Establish the connection.
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            // Create a test table.
            Statement stmt = con.createStatement();
            try {
                stmt.executeUpdate("DROP TABLE XAMin");
            } catch (Exception e) {
            }
            stmt.executeUpdate("CREATE TABLE XAMin (f1 int, f2 varchar(1024))");
            stmt.close();
            con.close();

            // Create the XA data source and XA ready connection.
            ClientXADataSource ds = new ClientXADataSource();
            ds.setUser("CSEM");
            ds.setPassword("CSEM");
            ds.setServerName("localhost");
            ds.setPortNumber(1527);
            ds.setDatabaseName("csemdb");
            XAConnection xaCon = ds.getXAConnection();
            con = xaCon.getConnection();

            // Get a unique Xid object for testing.
            XAResource xaRes = null;
            Xid xid = null;
            xid = XidImpl.getUniqueXid(1);

            // Get the XAResource object and set the timeout value.
            xaRes = xaCon.getXAResource();
//            xaRes.setTransactionTimeout(150);

            // Perform the XA transaction.
            System.out.println("Write -> xid = " + xid.toString());
            xaRes.start(xid, XAResource.TMNOFLAGS);
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO XAMin (f1,f2) VALUES
(?, ?)");
            pstmt.setInt(1, 1);
            pstmt.setString(2, xid.toString());
            pstmt.executeUpdate();

            // Commit the transaction.
//            xaRes.end(xid, XAResource.TMSUCCESS);
//            xaRes.prepare(xid);
//            try {
//                //xaRes.commit(xid, true);
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//
//            // Cleanup.
//            pstmt.close();
//            con.close();
//            xaCon.close();
//
//            // Open a new connection and read back the record to verify that it worked.
//            con = DriverManager.getConnection(connectionUrl);
//            ResultSet rs = con.createStatement().executeQuery("SELECT * FROM XAMin");
//            rs.next();
//            System.out.println("Read -> xid = " + rs.getString(2));
//            rs.close();
//            con.close();
        } // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        System.exit(1);


-----Original Message-----
From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Tuesday, December 06, 2011 11:45 AM
To: derby-dev@db.apache.org
Subject: RE: how can I force a rollback of an XA transaction

The database has the following transactions being show from querying syscs_diag.transaction_table:

NPCAcv# sh /opt/csem/canogaview/app/scripts/solaris/gettrans.sh
ij version 10.8
ij> ij> XID            |GLOBAL_XID                                                 
                                                                    |USERNAME            
                                                                                         
                 |TYPE                          |STATUS  |FIRST_INSTANT       |SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1089187896     |(4871251,588b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188131     |(4871251,638b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188159     |(4871251,648b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187793     |(4871251,518b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187804     |(4871251,508b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188329     |(4871251,6e8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187874     |(4871251,558b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102388777     |(4871251,43e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188296     |(4871251,6b8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187948     |(4871251,598b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102388811     |(4871251,49e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188058     |(4871251,5e8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188423     |(4871251,6f8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188227     |(4871251,668b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102388774     |(4871251,42e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187872     |(4871251,568b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188129     |(4871251,628b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188425     |(4871251,718b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187780     |(4871251,4c8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188258     |(4871251,6a8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188088     |(4871251,608b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187779     |(4871251,4e8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187873     |(4871251,578b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188008     |(4871251,5d8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188233     |(4871251,678b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |(462881,845067)     |NULL
1089188322     |(4871251,6c8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1097595231     |(4871251,bbde2b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187814     |(4871251,528b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102388810     |(4871251,48e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102383932     |(4871251,41e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187870     |(4871251,538b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102388800     |(4871251,47e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187871     |(4871251,548b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187771     |(4871251,4b8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |(462881,826529)     |NULL
1089188007     |(4871251,5c8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188086     |(4871251,618b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102388796     |(4871251,46e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188323     |(4871251,6d8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089187986     |(4871251,5a8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188426     |(4871251,708b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1102376671     |(4871251,40e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188238     |(4871251,688b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188226     |(4871251,658b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL
1089188085     |(4871251,5f8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
                    |CSEM                                                                
                                                           |UserTransaction              
|ACTIVE  |NULL                |NULL

44 rows selected


I have stopped the application server so I know that it is not active and there are no other
connections open to the database beside the connection being used by IJ.  I tried the little
utility to see if I could cause these transactions to rollback, but it acts as if it does
not see these transactions because they are active.

The utility is basically what you suggested Knut.

Any ideas on where to go from here?

-----Original Message-----
From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Tuesday, December 06, 2011 9:50 AM
To: derby-dev@db.apache.org
Subject: RE: how can I force a rollback of an XA transaction

I will give this a try and that is exactly what I was just trying to write.  This is the second
time in about 5 years that this has happened :(  I need to get away from using XA but the
application server was bitching without it.

Thank you for your help on this.  It would have taken me much longer to come up with this
myself.

Brett

-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
Sent: Tuesday, December 06, 2011 9:27 AM
To: derby-dev@db.apache.org
Subject: Re: how can I force a rollback of an XA transaction

"Bergquist, Brett" <BBergquist@canoga.com> writes:

> The JVM that derby was running in crashed with a SEGV.  There
> application server also went down.  When I brought both up, the
> database shows two XA transactions in the PREPARE state (looking at
> syscs_diag.transaction_table) but the application server has no
> reference to these transactions to force a rollback.  Files in the
> database “log” directory are being created, with 3862 being the
> current count.
>
> I need a way to get this to be cleaned up.  It would be best if this
> could be done with the database online as I don’t have days to wait
> for the database to come up.

Hi Brett,

If your application server doesn't manage to recover the transactions,
you could try to run the XA recovery procedure manually. Something like
this (untested) code:

XAConnection xac = ...; // get connection from the XADataSource
XAResource xar = xac.getXAResource();

for (Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
    xar.rollback(xid);
    // or commit:
    // xar.commit(xid, false);
}

Hope this helps,

--
Knut Anders

Mime
View raw message