Hi all, We discussed this offline, and the issue here is valid, Anjana can explain further if someone wants to understand it. 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
