You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Lars Clausen <lc...@statsbiblioteket.dk> on 2005/09/22 15:58:10 UTC
Locks not released on error
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
}
}
}
Re: Locks not released on error
Posted by Lars Clausen <lc...@statsbiblioteket.dk>.
On fre, 2005-09-23 at 09:43, Øystein Grøvlen wrote:
> >>>>> "LC" == Lars Clausen <lc...@statsbiblioteket.dk> writes:
>
> LC> Hi!
> LC> We're using Derby version 10.1 (Bundle-Version: 10.1.1000000.208786)
> LC> embeddedly in our system. Even though I've put extensive rollback and
> LC> statement closing handling in the code, we still occasionally see cases
> LC> where a table gets permanently locked after an error during update
> LC> (detail below). Is there something other than closing open statements
> LC> and rolling back existing savepoints that could be required for
> LC> releasing locks? Am I doing the closing/rolling back wrongly? Any
> LC> other ideas on how to cure this would be welcome.
>
> Lars,
>
> Note that rolling back to savepoints does not roll back the entire
> transaction. This means that locks set by the transaction are not
> released. In order to release locks you need to do call
> Connection.rollback() (without any parameters).
I see. Sun's javadoc is not entirely clear on that, but I now found it
deep in the Derby docs. So essentially, I should avoid using explicit
savepoints without a very good reason, but just do Connection.rollback
in the finally block (since it would normally come after the
Connection.commit call, it shouldn't matter when things go well, unless
there's a performance hit on trying a rollback right after a commit?)
Thanks for the help!
-Lars
Re: Locks not released on error
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
>>>>> "LC" == Lars Clausen <lc...@statsbiblioteket.dk> writes:
LC> Hi!
LC> We're using Derby version 10.1 (Bundle-Version: 10.1.1000000.208786)
LC> embeddedly in our system. Even though I've put extensive rollback and
LC> statement closing handling in the code, we still occasionally see cases
LC> where a table gets permanently locked after an error during update
LC> (detail below). Is there something other than closing open statements
LC> and rolling back existing savepoints that could be required for
LC> releasing locks? Am I doing the closing/rolling back wrongly? Any
LC> other ideas on how to cure this would be welcome.
Lars,
Note that rolling back to savepoints does not roll back the entire
transaction. This means that locks set by the transaction are not
released. In order to release locks you need to do call
Connection.rollback() (without any parameters).
--
Øystein