[Netarchivesuite-devel] FW: [SBForge JIRA] Commented: (NAS-1845) Database Deadlock

Mikis Seth Sørensen mss at statsbiblioteket.dk
Thu Apr 14 11:01:33 CEST 2011



From: Nicolas Giraud <nikokode at gmail.com<mailto:nikokode at gmail.com>>
Reply-To: "netarchivesuite-devel at lists.gforge.statsbiblioteket.dk<mailto:netarchivesuite-devel at lists.gforge.statsbiblioteket.dk>" <netarchivesuite-devel at lists.gforge.statsbiblioteket.dk<mailto:netarchivesuite-devel at lists.gforge.statsbiblioteket.dk>>
Date: Thu, 14 Apr 2011 10:33:10 +0200
To: "netarchivesuite-devel at lists.gforge.statsbiblioteket.dk<mailto:netarchivesuite-devel at lists.gforge.statsbiblioteket.dk>" <netarchivesuite-devel at lists.gforge.statsbiblioteket.dk<mailto:netarchivesuite-devel at lists.gforge.statsbiblioteket.dk>>
Subject: Re: [Netarchivesuite-devel] FW: [SBForge JIRA] Commented: (NAS-1845) Database Deadlock

Hi Colin and Mikis,

I have uploaded a patch NAS-1845, that solves the particular avatar of the issue I had. As I suspected, it would seem that Derby might detect deadlocks if the same thread opens several connections. However in this example, I don't understand why a deadlock happens, as we don't access the same tables in the nested DAO method (HarvestDefinitionDBDAO#getSparseDomainConfigurations) and the top-level call (HarvestDefinitionDBDAO#flipActive).

Can you guys have a look at this?

Currently I see two options to fix this:

1) refactor all DAO classes to make sure that only top-level DAO calls (i.e. called from JSPs) request a connection from the pool, and that all nested call reuse the same connection. That's probably a tad tedious and painful, but would enhance the code.
How long do you think a quick first step refactoring would require?

2) modify HarvestDBConnection to ensure that a same thread cannot have more than one connection opened at a time
I guess the way of ensuring this would not happen be to throw a RuntimeException, which might give us clearer idea of what is going on, but still crash/stop the application.

A 3. option might be to convince Derby to slack it's requirement on one connection pr. thread, as this isn't necessarily an error (is it?). Colin will look into this.

What's your opinion?
Have you got any idea of what change might have introduced this error? We also used the external Derby database during the last release.


Cheers,

Nicolas Giraud

2011/4/14 Nicolas Giraud <nikokode at gmail.com<mailto:nikokode at gmail.com>>
Hi Mikis,

I have a similar error when attempting to activate a harvest definition. I will continue investigating but so far I don't understand what's happening.

Regards,

Nicolas

2011/4/14 Mikis Seth Sørensen <mss at statsbiblioteket.dk<mailto:mss at statsbiblioteket.dk>>
Hi Nicolas

Have you got any idea of the cause for Colins problem is. Could you try to reproduce the error in your environment based on Colins instructions?

Note that this is a Blocker.

~Mikis

From: "Colin Rosenthal (JIRA)" <jira at sbforge.org<mailto:jira at sbforge.org>>
Date: Thu, 14 Apr 2011 09:15:37 +0200
To: Mikis Seth Sørensen <mss at statsbiblioteket.dk<mailto:mss at statsbiblioteket.dk>>
Subject: [SBForge JIRA] Commented: (NAS-1845) Database Deadlock

Issue (View Online<https://sbforge.org/jira/browse/NAS-1845>)

Key:    NAS-1845<https://sbforge.org/jira/browse/NAS-1845>
Issue Type:     <https://sbforge.org/jira/browse/NAS-1845>  Bug
Status:  Triage
Priority:        Blocker
Assignee:       Unassigned
Reporter:       Colin Rosenthal<https://sbforge.org/jira/secure/ViewProfile.jspa?name=csr>

Operations
  View all<https://sbforge.org/jira/browse/NAS-1845?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel>
  View comments<https://sbforge.org/jira/browse/NAS-1845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel>
  View history<https://sbforge.org/jira/browse/NAS-1845?page=com.atlassian.jira.plugin.system.issuetabpanels:changehistory-tabpanel>


Database Deadlock<https://sbforge.org/jira/browse/NAS-1845>
Updated: 14/Apr/11 9:14 AM   Created: 13/Apr/11 2:53 PM


The following comment has been added to this issue:     [ Permalink<https://sbforge.org/jira/browse/NAS-1845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=26252#comment-26252> ]
Author: Colin Rosenthal<https://sbforge.org/jira/secure/ViewProfile.jspa?name=csr>
Date: 14/Apr/11 9:14 AM
Comment:

The issue appears to be reproducible:
i) Create a new install
ii) add an alias (netarkivet.dk<http://netarkivet.dk> is an alias for kb.dk<http://kb.dk>)
iii) Run a snapshot harvest with byte limit 100000
iv) After the harvest is finished, edit sulnudu.dk<http://sulnudu.dk> to make it an alias of kb.dk<http://kb.dk>. This step deadlocks.



Project:        NetarchiveSuite<https://sbforge.org/jira/browse/NAS>
Components:     GUI
Affects Versions:       3.16.0
Fix Versions:   3.16.0


 Description

Received the following deadlock when trying to update alias information (Step 9 of Test 2).

SQL error updating domain Domain:sulnudu.dk<http://sulnudu.dk>;
Comment:;
Configurations:
defaultconfig;
Seedlists:
defaultseeds;
Passwords:
---------------
in database
SQLException trace:
SQL State:40XL2
Error Code:-1
java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested. The lockTable dump is:
2011-04-13 12:50:14.632 GMT
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 *
    *
       *   The following row is the victim ***
6641 |ROW |S |0 |(2,15) |WAIT |T |NULL |DOMAINS |
       *   The above row is the victim ***
6630 |ROW |X |4 |(2,15) |GRANT|T |NULL |DOMAINS |
6630 |TABLE |IX |4 |Tablelock |GRANT|T |NULL |DOMAINS |
6641 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |DOMAINS |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.executeUpdate(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at dk.netarkivet.harvester.datamodel.DomainDBDAO.updateHarvestInfo(DomainDBDAO.java:646)
at dk.netarkivet.harvester.datamodel.DomainDBDAO.update(DomainDBDAO.java:292)
at dk.netarkivet.harvester.webinterface.DomainDefinition.updateDomain(DomainDefinition.java:195)
at dk.netarkivet.harvester.webinterface.DomainDefinition.processRequest(DomainDefinition.java:138)
at org.apache.jsp.Definitions_002dedit_002ddomain_jsp._jspService(org.apache.jsp.Definitions_002dedit_002ddomain_jsp:125)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:109)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:389)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:486)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:380)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:401)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:322)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: org.apache.derby.client.am.SqlException: A lock could not be obtained within the time requested. The lockTable dump is:
2011-04-13 12:50:14.632 GMT
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 *
    *
       *   The following row is the victim ***
6641 |ROW |S |0 |(2,15) |WAIT |T |NULL |DOMAINS |
       *   The above row is the victim ***
6630 |ROW |X |4 |(2,15) |GRANT|T |NULL |DOMAINS |
6630 |TABLE |IX |4 |Tablelock |GRANT|T |NULL |DOMAINS |
6641 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |DOMAINS |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readExecute(Unknown Source)
at org.apache.derby.client.net.StatementReply.readExecute(Unknown Source)
at org.apache.derby.client.net.NetPreparedStatement.readExecute_(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.readExecute(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.executeUpdateX(Unknown Source)
... 29 more
End of SQLException trace





This message was automatically generated by Atlassian JIRA<http://www.atlassian.com/c/JIRA/10140> Enterprise Edition, Version: 4.2.1-b588-588 - Bug/feature request<http://jira.atlassian.com/default.jsp?clicked=footer>.
If you think it was sent incorrectly, contact one of this server's administrators .

_______________________________________________
Netarchivesuite-devel mailing list
Netarchivesuite-devel at lists.gforge.statsbiblioteket.dk<mailto:Netarchivesuite-devel at lists.gforge.statsbiblioteket.dk>
https://lists.gforge.statsbiblioteket.dk/mailman/listinfo/netarchivesuite-devel




--
Nicolas Giraud
---------------------------------------------------------------------------------------------
Développeur Archives du Web - Bibliothèque Nationale de France
Web Archiving Developper - National Library of France
---------------------------------------------------------------------------------------------



--
Nicolas Giraud
---------------------------------------------------------------------------------------------
Développeur Archives du Web - Bibliothèque Nationale de France
Web Archiving Developper - National Library of France
---------------------------------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ml.sbforge.org/pipermail/netarchivesuite-devel/attachments/20110414/3f13a8ca/attachment-0002.html>


More information about the Netarchivesuite-devel mailing list