On Mon, Jun 18, 2012 at 10:32 PM, Senaka Fernando <[email protected]> wrote:
> Finally, I made a breakthrough, :).. Tests Underway.. Hoping for the best. Great! :) .. Cheers, Anjana. > > 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 > > -- *Anjana Fernando* Associate Technical Lead WSO2 Inc. | http://wso2.com lean . enterprise . middleware
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
