Hi Amila, Anjana,
DimuthuG did this sometime back and also produced a spreadsheet (should be
on google docs) on the sequences. But something that we probably missed (as
in this case) is the locking granularity. There is a subtle difference when
row-level and table-level locks are acquired in and not-in combination,
which is not a frequent use-case; but some like these might still exist
unnoticed.
Thanks,
Senaka.
On Mon, Feb 6, 2012 at 12:04 PM, Anjana Fernando <anj...@wso2.com> wrote:
> 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
>
>
--
*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
