db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lars Clausen ...@statsbiblioteket.dk>
Subject Locks not released on error
Date Thu, 22 Sep 2005 13:58:10 GMT
Hi!

We're using Derby version 10.1 (Bundle-Version: 10.1.1000000.208786)
embeddedly in our system.  Even though I've put extensive rollback and
statement closing handling in the code, we still occasionally see cases
where a table gets permanently locked after an error during update
(detail below).  Is there something other than closing open statements
and rolling back existing savepoints that could be required for
releasing locks?  Am I doing the closing/rolling back wrongly?  Any
other ideas on how to cure this would be welcome.

Thanks,
-Lars

The errors we get are as follows:
First we get the error during an update of the HARVEST_CONFIGS table
(covered by rollback and statement closing in finallys), then a little
later another thread (using a separate Connection) tries to obtain a
lock on the HARVESTDEFINITIONS table.  The latter table was updated
earlier in the same transaction that had the error, but the statement
that updated the HARVESTDEFINITIONS table has already been closed.

The methods used are shown below the exceptions.

dk.netarkivet.exceptions.IOFailure: SQL error while updating harvest definition HD #44: 'test'
	at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.update(HarvestDefinitionDBDAO.java:487)
	at dk.netarkivet.harvestdefinition.PartialHarvest.addSeeds(PartialHarvest.java:375)
	at dk.netarkivet.webinterface.EventHarvest.addConfigurations(EventHarvest.java:110)
	at org.apache.jsp.Definitioner_0002daddseeds_jsp._jspService(Definitioner_0002daddseeds_jsp.java:68)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:358)
	at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:294)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:567)
	at org.mortbay.http.HttpContext.handle(HttpContext.java:1807)
	at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:525)
	at org.mortbay.http.HttpContext.handle(HttpContext.java:1757)
	at org.mortbay.http.HttpServer.service(HttpServer.java:879)
	at org.mortbay.http.HttpConnection.service(HttpConnection.java:790)
	at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:961)
	at org.mortbay.http.HttpConnection.handle(HttpConnection.java:807)
	at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:197)
	at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:276)
	at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:511)
Caused by: SQL Exception: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by 'SQL050921094346420'
defined on 'HARVEST_CONFIGS'.
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
	at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.createHarvestConfigsEntries(HarvestDefinitionDBDAO.java:226)
	at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.update(HarvestDefinitionDBDAO.java:476)
	... 22 more
Sep 22, 2005 3:48:02 PM dk.netarkivet.harvestscheduler.HarvestScheduler$1 run
SEVERE: Exception while scheduling new jobs
dk.netarkivet.exceptions.IOFailure: SQL Error while asking for all harvest definitions
	at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.getAllHarvestDefinitions(HarvestDefinitionDBDAO.java:518)
	at dk.netarkivet.harvestdefinition.HarvestDefinitionDAO.generateJobs(HarvestDefinitionDAO.java:173)
	at dk.netarkivet.harvestscheduler.HarvestScheduler.scheduleJobs(HarvestScheduler.java:199)
	at dk.netarkivet.harvestscheduler.HarvestScheduler.access$100(HarvestScheduler.java:57)
	at dk.netarkivet.harvestscheduler.HarvestScheduler$1.run(HarvestScheduler.java:135)
	at java.util.TimerThread.mainLoop(Timer.java:512)
	at java.util.TimerThread.run(Timer.java:462)
Caused by: SQL Exception: A lock could not be obtained within the time requested
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown Source)
	at dk.netarkivet.harvestdefinition.DBConnect.selectLongList(DBConnect.java:308)
	at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.getAllHarvestDefinitions(HarvestDefinitionDBDAO.java:510)
	... 6 more

The method that causes the first error (in the second executeUpdate):

    /** Create the entries in the harvest_configs table that connect
     * PartialHarvests and their configurations.
     *
     * @param c DB connection
     * @param ph The harvest to insert entries for.
     * @param id The id of the harvest -- this may not yet be set on ph
     * @throws SQLException
     */
    private void createHarvestConfigsEntries(Connection c, PartialHarvest ph, long id) throws
SQLException {
        PreparedStatement s = null;
        try {
            // Create harvest_configs entries
            s = c.prepareStatement("DELETE FROM harvest_configs " +
                    "WHERE harvest_id = ?");
            s.setLong(1, id);
            s.executeUpdate();
            s.close();
            s = c.prepareStatement("INSERT INTO harvest_configs " +
                    "( harvest_id, config_id ) " +
                    "SELECT ?, config_id FROM configurations, domains " +
                    "WHERE domains.name = ? AND configurations.name = ?" +
                    "  AND domains.domain_id = configurations.domain_id");
            for (Iterator<DomainConfiguration> dcs = ph.getDomainConfigurations();
                 dcs.hasNext(); ) {
                DomainConfiguration dc = dcs.next();
                s.setLong(1, id);
                s.setString(2, dc.getDomain().getName());
                s.setString(3, dc.getName());
                s.executeUpdate();
            }
        } finally {
            DBConnect.closeStatementIfOpen(s);
        }
    }


The calling method that has the transaction handling (in this case, hd is a PartialHarvest):

    /**
     * Update an existing harvest definition with new info.
     *
     * @param hd An updated harvest definition
     * @see HarvestDefinitionDAO#update(HarvestDefinition)
     */
    public synchronized void update(HarvestDefinition hd) {
        ArgumentNotValid.checkNotNull(hd, "HarvestDefinition hd");
        if (hd.getOid() == null || !exists(hd.getOid())) {
            final String message = "Cannot update non-existing harvestdefinition "
                    + hd.getName();
            log.warning(message);
            throw new PermissionDenied(message);
        }
        Connection c = DBConnect.getDBConnection();
        Savepoint save = null;
        PreparedStatement s = null;
        try {

            save = c.setSavepoint("updatehd");
            s = c.prepareStatement("UPDATE harvestdefinitions SET " +
                    "name = ?, " +
                    "comments = ?, " +
                    "numevents = ?, " +
                    "submitted = ?," +
                    "isactive = ?," +
                    "edition = ? " +
                    "WHERE harvest_id = ? AND edition = ?");
            DBConnect.setName(s, 1, hd);
            DBConnect.setComments(s, 2, hd);
            s.setInt(3, hd.getNumEvents());
            s.setTimestamp(4, new Timestamp(hd.getSubmissionDate().getTime()));
            s.setBoolean(5, hd.getActive());
            long nextEdition = hd.getEdition() + 1;
            s.setLong(6, nextEdition);
            s.setLong(7, hd.getOid());
            s.setLong(8, hd.getEdition());
            int rows = s.executeUpdate();
            // Since the HD exists, no rows indicates bad edition
            if (rows == 0) {
                String message = "Somebody else must have updated " + hd
                        + " since edition " + hd.getEdition() + ", not updating";
                log.warning(message);
                throw new PermissionDenied(message);
            }
            s.close();
            if (hd instanceof FullHarvest) {
                FullHarvest fh = (FullHarvest) hd;
                s = c.prepareStatement("UPDATE fullharvests SET " +
                        "previoushd = ?, " +
                        "maxobjects = ? " +
                        "WHERE harvest_id = ?");
                if (fh.getPreviousHarvestDefinition() != null) {
                    s.setLong(1, fh.getPreviousHarvestDefinition().getOid());
                } else {
                    s.setNull(1, Types.BIGINT);
                }
                s.setLong(2, fh.getMaxCountObjects());
                s.setLong(3, fh.getOid());
                rows = s.executeUpdate();
            } else if (hd instanceof PartialHarvest) {
                PartialHarvest ph = (PartialHarvest) hd;
                s = c.prepareStatement("UPDATE partialharvests SET " +
                        "schedule_id = " +
                        "    (SELECT schedule_id FROM schedules WHERE schedules.name = ?),
" +
                        "nextdate = ?" +
                        "WHERE harvest_id = ?");
                s.setString(1, ph.getSchedule().getName());
                DBConnect.setDateMaybeNull(s, 2, ph.getNextDate());
                s.setLong(3, ph.getOid());
                rows = s.executeUpdate();
                s.close();
                createHarvestConfigsEntries(c, ph, ph.getOid());
            } else {
                String message = "Harvest definition " + hd
                        + " has unknown class " + hd.getClass();
                log.warning(message);
                throw new ArgumentNotValid(message);
            }
            c.commit();
            save = null;
            hd.setEdition(nextEdition);
        } catch (SQLException e) {
            throw new IOFailure("SQL error while updating harvest definition " +
                    hd, e);
        } finally {
            DBConnect.rollbackIfNeeded(save, c, "updating", hd);
            DBConnect.closeStatementIfOpen(s);
        }
    }


The DBConnect methods that handle cleanup:

    /**
     * Close a statement, if not closed already
     * Note: This does not throw any a SQLException, because
     * it is always called inside a finally-clause.
     * @param s a statement
     */
    static void closeStatementIfOpen(PreparedStatement s) {
        if (s != null) {
            try {
                s.close();
            } catch (SQLException e) {
                log.log(Level.WARNING, "Error closing SQL statement " + s, e);
            }
        }
    }

    /**
     * Method to perform a rollback, if needed.
     * It is needed, if the Savepoint 'save' is not null.
     * @param save the savepoint
     * @param c the db-connection
     * @param action The action going on, before calling this method
     * @param o The being acted upon by this action
     */
    static void rollbackIfNeeded(Savepoint save, Connection c,
                                 String action, Object o) {
        if (save != null) {
            try {
                c.rollback(save);
                c.releaseSavepoint(save);
            } catch (SQLException e) {
                String message = "SQL error doing rollback of " + save
                        + " while " + action + " " + o;
                log.log(Level.WARNING, message, e);
                // Can't throw here, we want the real exception
            }
        }
    }



Mime
View raw message