Locks on crashed database

[Peter Ondruška <pe...@kaibo.eu>]

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.

Thanks

-- 
Peter Ondruška

Re: Locks on crashed database

[Peter Ondruška <pe...@kaibo.eu>]

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 <kn...@oracle.com>
wrote:

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

Re: Locks on crashed database

[Knut Anders Hatlen <kn...@oracle.com>]

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