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 <[email protected]>
wrote:
> Peter Ondruška <[email protected]> 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
