Dear Knut,
many thanks for the tip. For others who need something similar here is the
complete code:
package xarecovery;
import java.sql.SQLException;
import java.util.logging.Level;
import javax.sql.XAConnection;
import javax.sql.XADataSource;
import javax.transaction.xa.XAException;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;
import org.apache.derby.jdbc.EmbeddedDataSource;
import org.apache.derby.jdbc.EmbeddedXADataSource;
/**
* Remove obsolete lock records caused by not gracefully removing database
that was under transaction manager control.br /
* This can be observed by having records in TRANSACTION_TABLE (and related
in LOCK_TABLE) with state PREPARED:br /
* SELECT * FROM SYSCS_DIAG.LOCK_TABLE;br /
* SELECT * FROM SYSCS_DIAG.TRANSACTION_TABLE;
*
* @author Knut Anders Hatlen, Peter Ondruška (just slightly modified)
*
*/
public class Recover {
private static final java.util.logging.Logger LOGGER =
java.util.logging.Logger.getLogger(Recover.class.getName());
public static void main(final String[] args) {
final EmbeddedDataSource eds = new EmbeddedXADataSource();
eds.setDatabaseName(pathtodatabase);
final XADataSource ds = (EmbeddedXADataSource) eds;
try {
final XAConnection xac = ds.getXAConnection();
final XAResource xar = xac.getXAResource();
for (final Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
LOGGER.log(Level.INFO, Recover using rollback Xid {0},
xid.toString());
xar.rollback(xid);
}
xac.close();
} catch (final SQLException | XAException e) {
LOGGER.log(Level.WARNING, null, e);
}
try {
eds.setShutdownDatabase(shutdown);
eds.getConnection();
} catch (final SQLException e) {
LOGGER.log(Level.INFO, This exception is OK, e);
}
}
}
On 25 November 2014 at 12:49, Knut Anders Hatlen knut.hat...@oracle.com
wrote:
Peter Ondruška peter.ondru...@kaibo.eu writes:
Dear all,
I have a database that has locks in SYSCS_DIAG.LOCK_TABLE. How do I
remove those locks? I restarted the database but the locks are still
there. SYSCS_DIAG.TRANSACTION_TABLE also has related record with
status PREPARED. This database was used with XA on application server
but it was removed for troubleshooting.
Hi Peter,
You probably need to run XA recovery and commit or roll back the
prepared transactions. Something like this:
XADataSource ds = ;
XAConnection xac = ds.getXAConnection();
XAResource xar = xac.getXAResource();
for (Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
xar.rollback(xid);
// or, if you prefer, xar.commit(xid, false);
}
Hope this helps,
--
Knut Anders
--
Peter Ondruška
