Hi all,
Dead lock detected again with oracle even though we didn't get any dead
locks with H2, after the fixes done on Friday evening. I got the following
dead lock situation when running worker1 class with 1000 iterations and 40
concurrent users.
Increasing default max wait time didn't help for me.
[2010-03-01 00:22:53,466] ERROR - A SQLException error has occurred when
trying to close result set or prepared statement
java.sql.SQLException: ORA-00060: deadlock detected while waiting for
resource
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at
org.wso2.carbon.registry.core.jdbc.dao.ResourceDAO.deleteContentStream(ResourceDAO.java:660)
at
org.wso2.carbon.registry.core.jdbc.dao.ResourceVersionDAO.versionResource(ResourceVersionDAO.java:1052)
at
org.wso2.carbon.registry.core.jdbc.VersionRepository.createVersionForResource(VersionRepository.java:191)
at
org.wso2.carbon.registry.core.jdbc.VersionRepository.createSnapshot(VersionRepository.java:77)
at
org.wso2.carbon.registry.core.jdbc.Repository.deleteSubTree(Repository.java:473)
at
org.wso2.carbon.registry.core.jdbc.Repository.delete(Repository.java:403)
at
org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.delete(EmbeddedRegistry.java:805)
at
org.wso2.carbon.registry.core.session.UserRegistry.delete(UserRegistry.java:625)
at
org.wso2.carbon.registry.app.RegistryAdapter.processDeleteRequest(RegistryAdapter.java:602)
at
org.wso2.carbon.registry.app.RegistryAdapter.extensionRequest(RegistryAdapter.java:124)
at
org.apache.abdera.protocol.server.impl.AbstractProvider.processExtensionRequest(AbstractProvider.java:196)
at
org.apache.abdera.protocol.server.impl.AbstractProvider.process(AbstractProvider.java:137)
at
org.apache.abdera.protocol.server.FilterChain.next(FilterChain.java:42)
at
org.apache.abdera.protocol.server.servlet.AbderaServlet.service(AbderaServlet.java:90)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90)
at
org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111)
at
org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:67)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.wso2.carbon.bridge.BridgeServlet.service(BridgeServlet.java:135)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
at
org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Thread.java:619)
[2010-03-01 00:22:56,553] ERROR - A SQLException error has occurred when
trying to close result set or prepared statement
java.sql.SQLException: ORA-00060: deadlock detected while waiting for
resource
Thanks,
Krishantha.
On Sun, Feb 28, 2010 at 11:57 AM, Senaka Fernando <[email protected]> wrote:
> Hi all,
>
> We finally found a viable solution to this problem. Now, we have made sure
> that all write operations will access database tables in the same order.
> Therefore, the situation of A locking X, and waiting for Y, and B locking Y
> and waiting for X is eliminated in the process.
>
> Looking forward to take a look into all the INSERT queries done within the
> registry kernel and make sure that this protocol is adhered. We also need to
> document this (inside the code perhaps), and make sure that people read
> this, before introducing/modifying queries and operations involving queries.
>
> Thanks,
> Senaka.
>
>
> On Fri, Feb 26, 2010 at 10:42 AM, Amila Suriarachchi <[email protected]>wrote:
>
>>
>>
>> On Fri, Feb 26, 2010 at 9:38 AM, Dimuthu Gamage <[email protected]> wrote:
>>
>>> Hi,
>>>
>>> I think What 'READ_COMMITED' do is, in a case of doing reading, it will
>>> continue reading the commited data from the tables locked by others.
>>
>> Again the question is why others have to lock. Are they in higher
>> transaction levels?
>>
>>
>>> So I think what 'READ_COMMITED' guarantee is , if all the operation are
>>> 'READ' then it will not dead lock. But Apparently we are doing some 'WRITE'
>>> operations in that test case. so deadlocks are possible.
>>>
>>> I'm trying to list the sequence of database accesses for simple GET, PUT,
>>> DELETE registry operations at here[1]. So we can identify which operation
>>> can cause the deadlocks.
>>>
>>
>> I think writing a sample app with two threads to check the explanation we
>> provide may helpful as well. i.e. we
>> can control the event happening order using some sleeps.
>>
>> Amila.
>>
>>>
>>> Thanks
>>> Dimuthu
>>>
>>> [1]
>>> https://spreadsheets.google.com/a/wso2.com/ccc?key=tX5iHIbwTIDDuyF8uwsERiw&hl=en
>>>
>>>
>>> On Fri, Feb 26, 2010 at 9:14 AM, Amila Suriarachchi <[email protected]>wrote:
>>>
>>>>
>>>>
>>>> On Fri, Feb 26, 2010 at 8:21 AM, Dimuthu Gamage <[email protected]>wrote:
>>>>
>>>>>
>>>>>
>>>>> On Fri, Feb 26, 2010 at 7:50 AM, Amila Suriarachchi <[email protected]>wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, Feb 24, 2010 at 12:03 PM, Sumedha Rubasinghe <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>>
>>>>>>> On Wed, Feb 24, 2010 at 11:57 AM, Ruwan Linton <[email protected]>wrote:
>>>>>>>
>>>>>>>> Senaka Fernando wrote:
>>>>>>>> > This could be due to overlapping transactions in this particular
>>>>>>>> > component that attempts to write to the registry at once.
>>>>>>>> So does this mean that Registry cannot handle concurrent connections
>>>>>>>> to
>>>>>>>> a particular registry resource/collection? Well, even in that case
>>>>>>>> it
>>>>>>>> shouldn't deadlock :-(
>>>>>>>>
>>>>>>>
>>>>>>> If an insert (write) happens the database locks the table allowing
>>>>>>> only read. So the only option available for us is to speed up write
>>>>>>> operation. working on it..
>>>>>>>
>>>>>>
>>>>>> hi Sumedha,
>>>>>>
>>>>>> What is the transaction isolation level you use for these
>>>>>> transactions?
>>>>>>
>>>>>
>>>>> It is read committed.
>>>>>
>>>> Do you know why this deadlocks occur?. At this isolation level
>>>> transactions do not have to lock the tables isn't it?
>>>>
>>>> Amila.
>>>>
>>>>
>>>>> Thanks
>>>>> Dimuthu
>>>>>
>>>>>>
>>>>>> Amila.
>>>>>>
>>>>>>>
>>>>>>> /sumedha
>>>>>>>
>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Ruwan
>>>>>>>> >
>>>>>>>> > Thanks,
>>>>>>>> > Senaka.
>>>>>>>> >
>>>>>>>> > On Wed, Feb 24, 2010 at 11:28 AM, Hiranya Jayathilaka
>>>>>>>> > <[email protected] <mailto:[email protected]>> wrote:
>>>>>>>> >
>>>>>>>> > Following exception encountered in the ESB while updating a
>>>>>>>> proxy
>>>>>>>> > service:
>>>>>>>> >
>>>>>>>> >
>>>>>>>> > Exception in thread "Thread-23"
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.ServiceBusPersistenceException:
>>>>>>>> > Error while saving mediation configuration changes
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.MediationPersistenceManager.handleException(MediationPersistenceManager.java:278)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.MediationPersistenceManager.access$1200(MediationPersistenceManager.java:48)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.MediationPersistenceManager$MediationPersistenceWorker.run(MediationPersistenceManager.java:320)
>>>>>>>> > Caused by:
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.ServiceBusPersistenceException:
>>>>>>>> > Unable to persist the proxy service in the path :
>>>>>>>> > /repository/synapse/proxy-services/FooProxy
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.registry.AbstractRegistryStore.handleException(AbstractRegistryStore.java:101)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.registry.ProxyServiceRegistryStore.persistElement(ProxyServiceRegistryStore.java:72)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.AbstractStore.saveToRegistry(AbstractStore.java:154)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.AbstractStore.save(AbstractStore.java:82)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.MediationPersistenceManager.persistElement(MediationPersistenceManager.java:335)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.MediationPersistenceManager.access$1000(MediationPersistenceManager.java:48)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.MediationPersistenceManager$MediationPersistenceWorker.run(MediationPersistenceManager.java:314)
>>>>>>>> > Caused by:
>>>>>>>> > org.wso2.carbon.registry.core.exceptions.RegistryException:
>>>>>>>> Unable
>>>>>>>> > to persist element
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.registry.AbstractRegistryStore.persistElement(AbstractRegistryStore.java:95)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.registry.ProxyServiceRegistryStore.persistElement(ProxyServiceRegistryStore.java:69)
>>>>>>>> > ... 5 more
>>>>>>>> > Caused by:
>>>>>>>> > org.wso2.carbon.registry.core.exceptions.RegistryException:
>>>>>>>> Failed
>>>>>>>> > to add log entry for resource
>>>>>>>> > /_system/config/repository/synapse/proxy-services. Deadlock
>>>>>>>> > detected. The current transaction was rolled back. Details:
>>>>>>>> > Session #9 (user: WSO2CARBON) is waiting to lock
>>>>>>>> > PUBLIC.REG_RESOURCE while locking PUBLIC.REG_ASSOCIATION
>>>>>>>> > (exclusive), PUBLIC.REG_LOG (exclusive).
>>>>>>>> > Session #12 (user: WSO2CARBON) is waiting to lock
>>>>>>>> PUBLIC.REG_LOG
>>>>>>>> > while locking PUBLIC.REG_CONTENT_HISTORY (exclusive),
>>>>>>>> > PUBLIC.REG_RESOURCE_HISTORY (exclusive), PUBLIC.REG_RESOURCE
>>>>>>>> > (exclusive), PUBLIC.REG_CONTENT (exclusive),
>>>>>>>> PUBLIC.REG_SNAPSHOT
>>>>>>>> > (exclusive).; SQL statement:
>>>>>>>> > INSERT INTO REG_LOG (REG_PATH, REG_USER_ID, REG_LOGGED_TIME,
>>>>>>>> > REG_ACTION, REG_ACTION_DATA, REG_TENANT_ID) VALUES (?, ?, ?,
>>>>>>>> ?, ?,
>>>>>>>> > ?) [40001-112]
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.jdbc.dao.LogsDAO.addLog(LogsDAO.java:79)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.jdbc.Repository.update(Repository.java:966)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.jdbc.Repository.updateParent(Repository.java:947)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.jdbc.Repository.delete(Repository.java:403)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.delete(EmbeddedRegistry.java:807)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.session.UserRegistry.delete(UserRegistry.java:625)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.mediation.initializer.persistence.registry.AbstractRegistryStore.persistElement(AbstractRegistryStore.java:79)
>>>>>>>> > ... 6 more
>>>>>>>> > Caused by: org.h2.jdbc.JdbcSQLException: Deadlock detected.
>>>>>>>> The
>>>>>>>> > current transaction was rolled back. Details:
>>>>>>>> > Session #9 (user: WSO2CARBON) is waiting to lock
>>>>>>>> > PUBLIC.REG_RESOURCE while locking PUBLIC.REG_ASSOCIATION
>>>>>>>> > (exclusive), PUBLIC.REG_LOG (exclusive).
>>>>>>>> > Session #12 (user: WSO2CARBON) is waiting to lock
>>>>>>>> PUBLIC.REG_LOG
>>>>>>>> > while locking PUBLIC.REG_CONTENT_HISTORY (exclusive),
>>>>>>>> > PUBLIC.REG_RESOURCE_HISTORY (exclusive), PUBLIC.REG_RESOURCE
>>>>>>>> > (exclusive), PUBLIC.REG_CONTENT (exclusive),
>>>>>>>> PUBLIC.REG_SNAPSHOT
>>>>>>>> > (exclusive).; SQL statement:
>>>>>>>> > INSERT INTO REG_LOG (REG_PATH, REG_USER_ID, REG_LOGGED_TIME,
>>>>>>>> > REG_ACTION, REG_ACTION_DATA, REG_TENANT_ID) VALUES (?, ?, ?,
>>>>>>>> ?, ?,
>>>>>>>> > ?) [40001-112]
>>>>>>>> > at org.h2.message.Message.getSQLException(Message.java:107)
>>>>>>>> > at org.h2.message.Message.getSQLException(Message.java:118)
>>>>>>>> > at org.h2.message.Message.getSQLException(Message.java:77)
>>>>>>>> > at org.h2.table.TableData.doLock(TableData.java:428)
>>>>>>>> > at org.h2.table.TableData.lock(TableData.java:375)
>>>>>>>> > at org.h2.command.dml.Insert.update(Insert.java:99)
>>>>>>>> > at
>>>>>>>> org.h2.command.CommandContainer.update(CommandContainer.java:71)
>>>>>>>> > at org.h2.command.Command.executeUpdate(Command.java:207)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:139)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:128)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
>>>>>>>> > at
>>>>>>>> >
>>>>>>>> org.wso2.carbon.registry.core.jdbc.dao.LogsDAO.addLog(LogsDAO.java:72)
>>>>>>>> > ... 12 more
>>>>>>>> >
>>>>>>>> > Thanks
>>>>>>>> > --
>>>>>>>> > Hiranya Jayathilaka
>>>>>>>> > Software Engineer;
>>>>>>>> > WSO2 Inc.; http://wso2.org
>>>>>>>> > E-mail: [email protected] <mailto:[email protected]>; Mobile:
>>>>>>>> +94
>>>>>>>> > 77 633 3491
>>>>>>>> > Blog: http://techfeast-hiranya.blogspot.com
>>>>>>>> >
>>>>>>>> > _______________________________________________
>>>>>>>> > Carbon-dev mailing list
>>>>>>>> > [email protected] <mailto:[email protected]>
>>>>>>>> > https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>>>>> >
>>>>>>>> >
>>>>>>>> >
>>>>>>>> >
>>>>>>>> > --
>>>>>>>> > Senaka Fernando
>>>>>>>> > Software Engineer
>>>>>>>> > WSO2 Inc.
>>>>>>>> > E-mail: senaka AT wso2.com <http://wso2.com>; Mobile: +94 77 322
>>>>>>>> 1818
>>>>>>>> >
>>>>>>>> > http://www.wso2.com/ - "Lean . Enterprise . Middleware"
>>>>>>>> >
>>>>>>>> ------------------------------------------------------------------------
>>>>>>>> >
>>>>>>>> > _______________________________________________
>>>>>>>> > Carbon-dev mailing list
>>>>>>>> > [email protected]
>>>>>>>> > https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>>>>> >
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Ruwan Linton
>>>>>>>> Technical Lead & Product Manager; WSO2 ESB; http://wso2.org/esb
>>>>>>>> WSO2 Inc.; http://wso2.org
>>>>>>>> email: [email protected]; cell: +94 77 341 3097
>>>>>>>> blog: http://blog.ruwan.org
>>>>>>>>
>>>>>>>> Lean . Enterprise . Middleware
>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> Carbon-dev mailing list
>>>>>>>> [email protected]
>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Carbon-dev mailing list
>>>>>>> [email protected]
>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Carbon-dev mailing list
>>>>>> [email protected]
>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Carbon-dev mailing list
>>>>> [email protected]
>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> Carbon-dev mailing list
>>>> [email protected]
>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>>
>>>>
>>>
>>> _______________________________________________
>>> Carbon-dev mailing list
>>> [email protected]
>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>>
>>>
>>
>> _______________________________________________
>> Carbon-dev mailing list
>> [email protected]
>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>>
>>
>
>
> --
> Senaka Fernando
> Software Engineer
> WSO2 Inc.
> E-mail: senaka AT wso2.com; Mobile: +94 77 322 1818
>
>
> http://www.wso2.com/ - "Lean . Enterprise . Middleware"
>
> _______________________________________________
> Carbon-dev mailing list
> [email protected]
> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
>
>
_______________________________________________
Carbon-dev mailing list
[email protected]
https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
