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
[email protected]
http://mail.wso2.org/cgi-bin/mailman/listinfo/carbon-dev
