On Mon, Feb 6, 2012 at 12:50 AM, Senaka Fernando <sen...@wso2.com> 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.
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 <sen...@wso2.com> 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 <sen...@wso2.com> 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 <anj...@wso2.com>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
>>>> Carbon-dev@wso2.org
>>>> 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
> Carbon-dev@wso2.org
> 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
Carbon-dev@wso2.org
http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
