Hi all, We applied the patch given by Indunil which had cascade delete on the tables. However, it gave a dead lock in 'DELETE FROM UM_PERMISSION WHERE UM_RESOURCE_ID = ? AND UM_TENANT_ID=?' and 'DELETE FROM UM_HYBRID_ROLE WHERE UM_ROLE_NAME = ? AND UM_TENANT_ID=?'(The stack trace is attached). Following are the code lines which give the error:
dbConnection = DatabaseUtil.getDBConnection(this.dataSource);
DatabaseUtil.updateDatabase(dbConnection, "DELETE FROM UM_HYBRID_ROLE
WHERE UM_ROLE_NAME = ? AND UM_TENANT_ID=?", new Object[]{roleName,
Integer.valueOf(this.tenantId)});
dbConnection.commit();
and
dbConnection1 = this.getDBConnection();
DatabaseUtil.updateDatabase(dbConnection1, "DELETE FROM UM_PERMISSION
WHERE UM_RESOURCE_ID = ? AND UM_TENANT_ID=?", new Object[]{resourceId,
Integer.valueOf(this.tenantId)});
this.permissionTree.clearResourceAuthorizations(resourceId);
dbConnection1.commit();
Also when I looked at the script, I found out the following
information regarding foreign key relationships:
1. UM_ROLE_PERMISSION has 2 parent tables, UM_PERMISSION and UM_DOMAIN
2. UM_HYBRID_USER_ROLE has 2 parent tables, UM HYBRID_ROLE and UM_DOMAIN
One possible reason for the deadlock is if oracle locks the parent
table when deleting from a child table. A similar issue is reported
at[1]
[1]
http://stackoverflow.com/questions/9373843/meet-deadlock-when-deleting-different-records-from-same-table-in-different-trans
On Fri, Sep 25, 2015 at 1:06 PM, Ramith Jayasinghe <[email protected]> wrote:
> Hi,
> If you are hoping to handle/catch
> 'IntegrityConstraintViolationException' consider following:
>
> 'IntegrityConstraintViolationException' is only thrown in Oracle and
> mysql database drivers ( check their driver source code and you will
> see). from what I know MSSQL drivers doesn't throw these. I have seen
> similar approach has been adapted in Hibernate (ORM) framework code as
> well.
>
> therefore, if you go down this path you need to check SQLState [1].
> You might find[2] useful as reference.
>
> [1]
> http://docs.oracle.com/javase/7/docs/api/java/sql/SQLException.html#getSQLState()
> [2]
> https://github.com/wso2/andes/blob/master/modules/andes-core/broker/src/main/java/org/wso2/andes/store/rdbms/RDBMSStoreUtils.java#115
>
> On Fri, Sep 25, 2015 at 11:16 AM, Darshana Gunawardana
> <[email protected]> wrote:
> > Hi Indunil,
> >
> > On Fri, Sep 25, 2015 at 10:15 AM, Indunil Upeksha Rathnayake
> > <[email protected]> wrote:
> >>
> >> Hi,
> >>
> >> The deadlock issue(https://wso2.org/jira/browse/MB-1326) come up in
> oracle
> >> and mssql when clearResourceAuthorizations where permissions are
> deleted in
> >> UM_ROLE_PERMISSIONS, UM_USER_PERMISSIONS and UM_PERMISSION tables
> through
> >> one transaction. The fix would be using cascade delete for above tables.
> >
> >
> > +1
> >
> >>
> >> But I think another issue(foreign key constraint violation) may come up
> >> when addAuthorizationForRole, as following example scenario. Ex: Node1
> may
> >> be retrieve the permission entry in UM_PERMISSION table (in
> >> addAuthorizationForRole()) and just after that node2 may delete the
> entry
> >> (in clearResourceAuthorizations()) and after that when node1 trying to
> >> delete & add permissions in UM_ROLE_PERMISSIONS (in
> >> addAuthorizationForRole()) will return the above exception since the
> entry
> >> in UM_PERMISSION is already deleted.
> >>
> >> I think the fix need to be, if UM_ROLE_PERMISSIONS and UM_PERMISSION
> >> tables are going to update, first have to lock both tables, until all
> the
> >> required operations are finished. Then meanwhile another node can't
> delete
> >> any entries.
> >
> >
> > Have you able to reproduce this behavior? I guess we cannot lock those
> > tables from the java code in db in dependent manner. Please check with
> our
> > db experts.
> >
> > In worst case we could handle IntegrityConstraintViolationException since
> > its ok to avoiding addAuthorization for a permission which got deleted
> > later.
> >
> > Thanks,
> >
> >>
> >> Please provide your opinions regarding this.
> >>
> >> Thanks and Regards
> >> --
> >> Indunil Upeksha Rathnayake
> >> Software Engineer | WSO2 Inc
> >> Email [email protected]
> >
> >
> >
> >
> > --
> > Regards,
> >
> > Darshana Gunawardana
> > Senior Software Engineer
> > WSO2 Inc.; http://wso2.com
> > E-mail: [email protected]
> > Mobile: +94718566859
> > Lean . Enterprise . Middleware
>
>
>
> --
> Ramith Jayasinghe
> Technical Lead
> WSO2 Inc., http://wso2.com
> lean.enterprise.middleware
>
> E: [email protected]
> P: +94 777542851
>
--
Sasikala Kottegoda
*Software Engineer*
WSO2 Inc., http://wso2.com/
lean. enterprise. middleware
Mobile: +94 774835928/712792401
ORA-00060_
Description: Binary data
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
