Hi Ajith, Can you please get the fix done to registry and test it out. IIRC, the deadlocking scenario in the code happens for update operations, the insert operation sequence seemed correct.
Cheers, Anjana. On Mon, Feb 6, 2012 at 1:47 PM, Senaka Fernando <[email protected]> wrote: > Hi Amila, Anjana, > > DimuthuG did this sometime back and also produced a spreadsheet (should be > on google docs) on the sequences. But something that we probably missed (as > in this case) is the locking granularity. There is a subtle difference when > row-level and table-level locks are acquired in and not-in combination, > which is not a frequent use-case; but some like these might still exist > unnoticed. > > Thanks, > Senaka. > > > On Mon, Feb 6, 2012 at 12:04 PM, Anjana Fernando <[email protected]> wrote: > >> Hi Amila, >> >> On Mon, Feb 6, 2012 at 11:50 AM, Amila Suriarachchi <[email protected]>wrote: >> >>> >>> >>> On Mon, Feb 6, 2012 at 11:35 AM, Anjana Fernando <[email protected]>wrote: >>> >>>> Hi Amila, >>>> >>>> On Mon, Feb 6, 2012 at 10:30 AM, Amila Suriarachchi <[email protected]>wrote: >>>> >>>>> >>>>> >>>>> On Mon, Feb 6, 2012 at 12:50 AM, Senaka Fernando <[email protected]>wrote: >>>>> >>>>>> Hi all, >>>>>> >>>>>> We discussed this offline, and the issue here is valid, Anjana can >>>>>> explain further if someone wants to understand it. >>>>> >>>>> >>>>> Can you please do that? >>>>> >>>>> I also thought (as Senaka mentioned) update sequence should be >>>>> consistent across different transaction scenarios. In that case we need to >>>>> study all the sql update patterns of the registry and put them to same >>>>> lock >>>>> acquiring sequence. >>>>> >>>> >>>> I guess, that's what we just did. Updating scenarios are basically >>>> registry "put" and "delete". The "put" operations actually does "delete" >>>> ones internally (when updating), so we aligned the actual inserts/delete >>>> sequences. So now, the separate "delete" operation also will also run in >>>> the same lock acquiring sequence. >>>> >>> >>> In that case you need to check all the transaction scenarios of the >>> registry and see whether they acquire the locks in same order. Otherwise >>> you will get deadlocks when the incompatible two transactions executed in >>> parallel. It does not matter whether one registry transaction calls or not >>> but finally all the database level transactions should acquire the locks in >>> same order. >>> >> >> Yes, I just assume these are the only locations we are doing the updates, >> or else if there any more related updates to the same tables, we should >> check those. Anyways, the most frequently occurred deadlock (maybe the only >> one), is the one to do with the put/delete, this should be fixed by this, >> and I don't think this change will give way to new deadlock situations, >> best thing to do is test it and see. >> >> Cheers, >> Anjana. >> >> >>> >>> thanks, >>> Amila. >>> >>> >>>> >>>> Cheers, >>>> Anjana. >>>> >>>> >>>>> >>>>> thanks, >>>>> Amila. >>>>> >>>>> >>>>>> But out of the three options, right now, only #3 seems to be possible >>>>>> without breaking consistency. But even this needs to be tested against >>>>>> all >>>>>> DBs, because we are momentarily fooling the DB, by inserting a NULL value >>>>>> as the content id. >>>>>> >>>>>> Anjana will work with someone (perhaps Ajith), who knows the registry >>>>>> kernel and get this fix checked in. We can easily test this with a bulk >>>>>> WSDL upload operation, that produces deadlocks. >>>>>> >>>>>> Thanks, >>>>>> Senaka. >>>>>> >>>>>> >>>>>> On Mon, Feb 6, 2012 at 12:08 AM, Senaka Fernando <[email protected]>wrote: >>>>>> >>>>>>> Hi again, >>>>>>> >>>>>>> And with regard to #3, if that's a safe option go for it. That's >>>>>>> probably due to somebody not realizing the possibility. Anyway, I'm not >>>>>>> the >>>>>>> expert when it comes to SQL stuff, may be Sumedha can provide some >>>>>>> insight? >>>>>>> >>>>>>> Thanks, >>>>>>> Senaka. >>>>>>> >>>>>>> >>>>>>> On Mon, Feb 6, 2012 at 12:06 AM, Senaka Fernando <[email protected]>wrote: >>>>>>> >>>>>>>> Hi Anjana, >>>>>>>> >>>>>>>> So is this a complete sequence of operations? If so, once a write >>>>>>>> lock is acquired, IIRC it will be held until the transaction completes >>>>>>>> - am >>>>>>>> I missing something? >>>>>>>> >>>>>>>> Also, there are some INSERT statements above the deletes. What >>>>>>>> about those? >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Senaka. >>>>>>>> >>>>>>>> On Mon, Feb 6, 2012 at 12:00 AM, Anjana Fernando >>>>>>>> <[email protected]>wrote: >>>>>>>> >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> I may have possibly found a solution for the registry deadlocking >>>>>>>>> for concurrent puts/deletes. I was checking the SQL statements >>>>>>>>> executed for >>>>>>>>> a put, and the list is as follows. >>>>>>>>> >>>>>>>>> SELECT REG_PATH_ID FROM REG_PATH WHERE >>>>>>>>> REG_PATH_VALUE='/_system/config/abc1' AND REG_TENANT_ID=0 >>>>>>>>> SELECT REG_VERSION FROM REG_RESOURCE WHERE REG_PATH_ID=4 AND >>>>>>>>> REG_NAME='abc1' AND REG_TENANT_ID=0 >>>>>>>>> SELECT REG_PATH_ID FROM REG_PATH WHERE >>>>>>>>> REG_PATH_VALUE='/_system/config/abc1' AND REG_TENANT_ID=0 >>>>>>>>> SELECT REG_MEDIA_TYPE, REG_CREATOR, REG_CREATED_TIME, >>>>>>>>> REG_LAST_UPDATOR, REG_LAST_UPDATED_TIME, REG_VERSION, REG_DESCRIPTION, >>>>>>>>> REG_CONTENT_ID FROM REG_RESOURCE WHERE REG_PATH_ID=4 AND REG_NAME = >>>>>>>>> 'abc1' >>>>>>>>> AND REG_TENANT_ID=0 >>>>>>>>> SELECT REG_CONTENT_DATA FROM REG_CONTENT WHERE REG_CONTENT_ID = >>>>>>>>> 105 AND REG_TENANT_ID=0 >>>>>>>>> SELECT REG_NAME, REG_VALUE FROM REG_PROPERTY P, >>>>>>>>> REG_RESOURCE_PROPERTY RP WHERE P.REG_ID=RP.REG_PROPERTY_ID AND >>>>>>>>> RP.REG_VERSION=2492 AND P.REG_TENANT_ID=0 AND RP.REG_TENANT_ID=0 >>>>>>>>> SELECT R.REG_PATH_ID, R.REG_NAME, R.REG_VERSION, R.REG_MEDIA_TYPE, >>>>>>>>> R.REG_CREATOR, R.REG_CREATED_TIME, R.REG_LAST_UPDATOR, >>>>>>>>> R.REG_LAST_UPDATED_TIME, R.REG_DESCRIPTION, R.REG_CONTENT_ID FROM >>>>>>>>> REG_RESOURCE R WHERE R.REG_PATH_ID=4 AND R.REG_NAME='abc1' AND >>>>>>>>> R.REG_TENANT_ID=0 >>>>>>>>> SELECT REG_CONTENT_DATA FROM REG_CONTENT_HISTORY WHERE >>>>>>>>> REG_CONTENT_ID = 105 AND REG_TENANT_ID=0 >>>>>>>>> SELECT REG_CONTENT_DATA FROM REG_CONTENT WHERE REG_CONTENT_ID = >>>>>>>>> 105 AND REG_TENANT_ID=0 >>>>>>>>> INSERT INTO REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_CONTENT_DATA, >>>>>>>>> REG_TENANT_ID) VALUES (105, '<Binary InputStream of length 9>', 0) >>>>>>>>> SELECT REG_PATH_ID FROM REG_RESOURCE_HISTORY WHERE >>>>>>>>> REG_VERSION=2492 AND REG_TENANT_ID=0 >>>>>>>>> INSERT INTO REG_RESOURCE_HISTORY (REG_PATH_ID, REG_NAME, >>>>>>>>> REG_VERSION, REG_MEDIA_TYPE, REG_CREATOR, REG_CREATED_TIME, >>>>>>>>> REG_LAST_UPDATOR, REG_LAST_UPDATED_TIME, REG_DESCRIPTION, >>>>>>>>> REG_CONTENT_ID, >>>>>>>>> REG_TENANT_ID) VALUES (4, 'abc1', 2492, 'text/plain', 'admin', >>>>>>>>> '02/05/2012 >>>>>>>>> 22:05:18.685', 'admin', '02/05/2012 22:05:55.959', 'XXXXXXXX', 105, 0) >>>>>>>>> DELETE FROM REG_RESOURCE WHERE REG_PATH_ID=4 AND REG_NAME='abc1' >>>>>>>>> AND REG_TENANT_ID=0 >>>>>>>>> DELETE FROM REG_CONTENT WHERE REG_CONTENT_ID = 105 AND >>>>>>>>> REG_TENANT_ID=0 >>>>>>>>> INSERT INTO REG_SNAPSHOT (REG_PATH_ID, REG_RESOURCE_NAME, >>>>>>>>> REG_RESOURCE_VIDS, REG_TENANT_ID) VALUES (4, 'abc1', '<Binary >>>>>>>>> InputStream >>>>>>>>> of length 8>', 0) >>>>>>>>> SELECT REG_TARGETPATH, REG_ASSOCIATION_TYPE FROM REG_ASSOCIATION >>>>>>>>> WHERE REG_SOURCEPATH='/_system/config/abc1' AND REG_TENANT_ID=0 >>>>>>>>> INSERT INTO REG_CONTENT (REG_CONTENT_DATA, REG_TENANT_ID) VALUES >>>>>>>>> ('<Binary InputStream of length 9>', 0) >>>>>>>>> INSERT INTO REG_RESOURCE (REG_PATH_ID, REG_NAME, REG_MEDIA_TYPE, >>>>>>>>> REG_CREATOR, REG_CREATED_TIME, REG_LAST_UPDATOR, >>>>>>>>> REG_LAST_UPDATED_TIME, >>>>>>>>> REG_DESCRIPTION, REG_CONTENT_ID, REG_TENANT_ID) VALUES (4, 'abc1', >>>>>>>>> 'text/plain', 'admin', '02/05/2012 22:05:18.685', 'admin', '02/05/2012 >>>>>>>>> 22:12:11.357', 'XXXXXXXX', 106, 0) >>>>>>>>> >>>>>>>>> Notice the red colored lines, it is an execution of a classical >>>>>>>>> deadlocking situation. Where the locked tables (REG_CONTENT, >>>>>>>>> REG_RESOURCE) >>>>>>>>> are not in the same order in the two sequences. So concurrent threads >>>>>>>>> can >>>>>>>>> deadlock. The rule of thumb in avoiding deadlocks is, always do the >>>>>>>>> updates >>>>>>>>> in the same table order, and you will not get a deadlock. Even though >>>>>>>>> the >>>>>>>>> rows are not the same that are updated in the INSERTS, those inserts >>>>>>>>> have >>>>>>>>> an auto_increment id, which will do a table wide lock, and will >>>>>>>>> affect the >>>>>>>>> above delete statements. >>>>>>>>> >>>>>>>>> So in the current way, we just reverse the DELETE statements, >>>>>>>>> because there is a foreign key constraint on it, it has to be deleted >>>>>>>>> in >>>>>>>>> that order. So we can do one of the following, >>>>>>>>> >>>>>>>>> 1. Remove the foreign key constraint from REG_RESOURCE for >>>>>>>>> REG_CONTENT_ID. >>>>>>>>> 2. Put a cascading delete when defining the REG_RESOURCE, the two >>>>>>>>> deletes then should happen atomically (hopefully). >>>>>>>>> 3. Swap the INSERTs and in the first INSERT to insert the >>>>>>>>> REG_RESOURCE, leave REG_CONTENT_ID empty, then insert the >>>>>>>>> REG_CONTENT, then >>>>>>>>> do an UPDATE on the earlier inserted REG_RESOURCE to set the >>>>>>>>> REG_CONTENT_ID. >>>>>>>>> >>>>>>>>> From the above, I guess 3'rd option would be the easiest to >>>>>>>>> implement with less changes. >>>>>>>>> >>>>>>>>> Cheers, >>>>>>>>> Anjana. >>>>>>>>> -- >>>>>>>>> *Anjana Fernando* >>>>>>>>> Senior Software Engineer >>>>>>>>> WSO2 Inc. | http://wso2.com >>>>>>>>> lean . enterprise . middleware >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Carbon-dev mailing list >>>>>>>>> [email protected] >>>>>>>>> http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> *Senaka Fernando* >>>>>>>> Product Manager - WSO2 Governance Registry; >>>>>>>> Associate Technical Lead; WSO2 Inc.; http://wso2.com* >>>>>>>> Member; Apache Software Foundation; http://apache.org >>>>>>>> >>>>>>>> E-mail: senaka AT wso2.com >>>>>>>> **P: +1 408 754 7388; ext: 51736*; *M: +94 77 322 1818 >>>>>>>> Linked-In: http://linkedin.com/in/senakafernando >>>>>>>> >>>>>>>> *Lean . Enterprise . Middleware >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> *Senaka Fernando* >>>>>>> Product Manager - WSO2 Governance Registry; >>>>>>> Associate Technical Lead; WSO2 Inc.; http://wso2.com* >>>>>>> Member; Apache Software Foundation; http://apache.org >>>>>>> >>>>>>> E-mail: senaka AT wso2.com >>>>>>> **P: +1 408 754 7388; ext: 51736*; *M: +94 77 322 1818 >>>>>>> Linked-In: http://linkedin.com/in/senakafernando >>>>>>> >>>>>>> *Lean . Enterprise . Middleware >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> *Senaka Fernando* >>>>>> Product Manager - WSO2 Governance Registry; >>>>>> Associate Technical Lead; WSO2 Inc.; http://wso2.com* >>>>>> Member; Apache Software Foundation; http://apache.org >>>>>> >>>>>> E-mail: senaka AT wso2.com >>>>>> **P: +1 408 754 7388; ext: 51736*; *M: +94 77 322 1818 >>>>>> Linked-In: http://linkedin.com/in/senakafernando >>>>>> >>>>>> *Lean . Enterprise . Middleware >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Carbon-dev mailing list >>>>>> [email protected] >>>>>> http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Amila Suriarachchi* >>>>> >>>>> Software Architect >>>>> >>>>> WSO2 Inc. ; http://wso2.com >>>>> lean . enterprise . middleware >>>>> >>>>> phone : +94 71 3082805 >>>>> >>>>> >>>>> _______________________________________________ >>>>> Carbon-dev mailing list >>>>> [email protected] >>>>> http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>>> >>>>> >>>> >>>> >>>> -- >>>> *Anjana Fernando* >>>> Senior Software Engineer >>>> WSO2 Inc. | http://wso2.com >>>> lean . enterprise . middleware >>>> >>>> _______________________________________________ >>>> Carbon-dev mailing list >>>> [email protected] >>>> http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>>> >>>> >>> >>> >>> -- >>> *Amila Suriarachchi* >>> >>> Software Architect >>> WSO2 Inc. ; http://wso2.com >>> lean . enterprise . middleware >>> >>> phone : +94 71 3082805 >>> >>> >>> _______________________________________________ >>> Carbon-dev mailing list >>> [email protected] >>> http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >>> >>> >> >> >> -- >> *Anjana Fernando* >> Senior Software Engineer >> WSO2 Inc. | http://wso2.com >> lean . enterprise . middleware >> >> _______________________________________________ >> Carbon-dev mailing list >> [email protected] >> http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev >> >> > > > -- > *Senaka Fernando* > Product Manager - WSO2 Governance Registry; > Associate Technical Lead; WSO2 Inc.; http://wso2.com* > Member; Apache Software Foundation; http://apache.org > > E-mail: senaka AT wso2.com > **P: +1 408 754 7388; ext: 51736*; *M: +94 77 322 1818 > Linked-In: http://linkedin.com/in/senakafernando > > *Lean . Enterprise . Middleware > > > _______________________________________________ > Carbon-dev mailing list > [email protected] > http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev > > -- *Anjana Fernando* Senior Software Engineer WSO2 Inc. | http://wso2.com lean . enterprise . middleware
_______________________________________________ Carbon-dev mailing list [email protected] http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
