Hi All, I haven't seen the same dead lock situation when running with mysql and H2. The situation may be related oracle ojdbc14.jar
Thanks, Krishantha. On Mon, Mar 1, 2010 at 12:05 AM, Krishantha Samaraweera <krishan...@wso2.com > wrote: > 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 <sen...@wso2.com> 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 <am...@wso2.com>wrote: >> >>> >>> >>> On Fri, Feb 26, 2010 at 9:38 AM, Dimuthu Gamage <dimu...@wso2.com>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 <am...@wso2.com>wrote: >>>> >>>>> >>>>> >>>>> On Fri, Feb 26, 2010 at 8:21 AM, Dimuthu Gamage <dimu...@wso2.com>wrote: >>>>> >>>>>> >>>>>> >>>>>> On Fri, Feb 26, 2010 at 7:50 AM, Amila Suriarachchi >>>>>> <am...@wso2.com>wrote: >>>>>> >>>>>>> >>>>>>> >>>>>>> On Wed, Feb 24, 2010 at 12:03 PM, Sumedha Rubasinghe < >>>>>>> sume...@wso2.com> wrote: >>>>>>> >>>>>>>> >>>>>>>> On Wed, Feb 24, 2010 at 11:57 AM, Ruwan Linton <ru...@wso2.com>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 >>>>>>>>> > <hira...@wso2.com <mailto:hira...@wso2.com>> 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: hira...@wso2.com <mailto:hira...@wso2.com>; Mobile: >>>>>>>>> +94 >>>>>>>>> > 77 633 3491 >>>>>>>>> > Blog: http://techfeast-hiranya.blogspot.com >>>>>>>>> > >>>>>>>>> > _______________________________________________ >>>>>>>>> > Carbon-dev mailing list >>>>>>>>> > Carbon-dev@wso2.org <mailto:Carbon-dev@wso2.org> >>>>>>>>> > 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 >>>>>>>>> > Carbon-dev@wso2.org >>>>>>>>> > 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: ru...@wso2.com; cell: +94 77 341 3097 >>>>>>>>> blog: http://blog.ruwan.org >>>>>>>>> >>>>>>>>> Lean . Enterprise . Middleware >>>>>>>>> >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Carbon-dev mailing list >>>>>>>>> Carbon-dev@wso2.org >>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Carbon-dev mailing list >>>>>>>> Carbon-dev@wso2.org >>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> Carbon-dev mailing list >>>>>>> Carbon-dev@wso2.org >>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>>>> >>>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Carbon-dev mailing list >>>>>> Carbon-dev@wso2.org >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>>> >>>>>> >>>>> >>>>> _______________________________________________ >>>>> Carbon-dev mailing list >>>>> Carbon-dev@wso2.org >>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>> >>>>> >>>> >>>> _______________________________________________ >>>> Carbon-dev mailing list >>>> Carbon-dev@wso2.org >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>> >>>> >>> >>> _______________________________________________ >>> Carbon-dev mailing list >>> Carbon-dev@wso2.org >>> 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 >> Carbon-dev@wso2.org >> https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >> >> >
_______________________________________________ Carbon-dev mailing list Carbon-dev@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
