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. 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
