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