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.
>
Hi Anjana,
Went through the code in ResourceDAO as well. Implementing #3 would be the
easiest. And it is logical as well. Add RESOURCE header & then the content.
/sumedha
>
> 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
>
>
--
/sumedha
+94 773017743
_______________________________________________
Carbon-dev mailing list
Carbon-dev@wso2.org
http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
