On Mon, Feb 6, 2012 at 11:35 AM, Anjana Fernando <anj...@wso2.com> wrote:
> Hi Amila,
>
> On Mon, Feb 6, 2012 at 10:30 AM, Amila Suriarachchi <am...@wso2.com>wrote:
>
>>
>>
>> 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.
>>
>
> 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.
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 <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
>>
>>
>
>
> --
> *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
>
>
--
*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
