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

Nicolas Giraud nikokode at gmail.com
Thu Apr 14 11:07:28 CEST 2011


NAS-1816 <https://sbforge.org/jira/browse/NAS-1816> introduced this issue.
Before, the way DBConnect was implemented forced one connection per thread.

An initial refactoring, as I commented in NAS-1817, would take about a day
of work. But of course if we can make Derby force one connection per thread
it would be cool.

Nicolas

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

>
>
> From: Nicolas Giraud <nikokode at gmail.com>
> Reply-To: "netarchivesuite-devel at lists.gforge.statsbiblioteket.dk" <
> netarchivesuite-devel at lists.gforge.statsbiblioteket.dk>
> Date: Thu, 14 Apr 2011 10:33:10 +0200
> To: "netarchivesuite-devel at lists.gforge.statsbiblioteket.dk" <
> 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>
>
>> 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>
>>
>>> 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>
>>> Date: Thu, 14 Apr 2011 09:15:37 +0200
>>> To: Mikis Seth Sørensen <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: [image: Bug] <https://sbforge.org/jira/browse/NAS-1845> Bug
>>> Status: [image: Triage] Triage  Priority: [image: Blocker] 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 is an alias for kb.dk)
>>> iii) Run a snapshot harvest with byte limit 100000
>>> iv) After the harvest is finished, edit sulnudu.dk to make it an alias
>>> of 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;
>>> 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
>>>
>>> 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
>
> ---------------------------------------------------------------------------------------------
>
>
> _______________________________________________
> Netarchivesuite-devel mailing list
> 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
---------------------------------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ml.sbforge.org/pipermail/netarchivesuite-devel/attachments/20110414/daa9cfbc/attachment-0002.html>


More information about the Netarchivesuite-devel mailing list