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