db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Ondruška <peter.ondru...@kaibo.eu>
Subject Re: Locks on crashed database
Date Thu, 27 Nov 2014 13:23:53 GMT
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.hatlen@oracle.com>
wrote:

> Peter Ondruška <peter.ondruska@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

Mime
View raw message