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
