[Netarchivesuite-devel] FW: FW: [SBForge JIRA] Commented: (NAS-1845) Database Deadlock
Nicolas Giraud
nikokode at gmail.com
Thu Apr 14 14:08:33 CEST 2011
Hi all,
Well the ThreadLocal approach will probably not mix very well with the
connection pool, which is supposed to be thread-safe. Besides, I would
rather not implement something that is relevant only for Derby...
So I have started progress on the refactoring. I'll let you know how fast it
goes.
Cheers,
Nicolas
2011/4/14 <aponb at gmx.at>
> This Database Deadlock does not happen with my MySQL-Environment!
>
> Hi Nicolas
>
> We are unable to find an Derby DB option for disabling the multiple
> connection pr. thread check. I don't think a 'force one connection pr.
> thread' option (which doesn't appear to be available either) would solve the
> problem, as it would just prevent code attempting to create a second
> connection in a thread from running, which again would cause a deadlock or
> exception.
>
> So it would be great if you could create a fast refactoring fix, so we
> could restart the release test.
>
> By the way, is this a Derby issue or does PostgreSQL and MySQL have the
> same constraint.
>
> ~Mikis
>
> From: Mikis Seth Sørensen <mss at statsbiblioteket.dk>
> Date: Thu, 14 Apr 2011 11:10:51 +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
>
> OK.
>
> Colin, could you have a estimate on whether a relevant Derby
> configuration option is available in a hour or so?
>
> ~Mikis
>
> 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 11:07:28 +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
>
> 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
>
> ---------------------------------------------------------------------------------------------
>
>
> _______________________________________________
> Netarchivesuite-devel mailing listNetarchivesuite-devel at lists.gforge.statsbiblioteket.dkhttps://lists.gforge.statsbiblioteket.dk/mailman/listinfo/netarchivesuite-devel
>
>
>
> _______________________________________________
> 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/f21f876a/attachment-0002.html>
More information about the Netarchivesuite-devel
mailing list