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
Date Tue, 06 Dec 2011 16:45:27 GMT
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