Hi all, Picked this up and was working on it for sometime. Still no breakthrough. Was successful with #1, but that needs a fix to the schema to remove the constraints. #3 is what I'm looking into right now and I'm trying out multiple combinations of possible re-orderings. But I still couldn't get all tests to pass. Will continue and update. The aim is to fix this for C4 Beta.
Thanks, Senaka. On Fri, Mar 2, 2012 at 12:52 PM, Anjana Fernando <[email protected]> wrote: > 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 > > -- *Senaka Fernando* Member - Integration Technologies Management Committee; 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
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
