Finally, I made a breakthrough, :).. Tests Underway.. Hoping for the best. Thanks, Senaka.
On Mon, Jun 18, 2012 at 7:57 PM, Senaka Fernando <[email protected]> wrote: > 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 > > -- *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
