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 <darsh...@wso2.com> wrote: > Hi Indunil, > > On Fri, Sep 25, 2015 at 10:15 AM, Indunil Upeksha Rathnayake > <indu...@wso2.com> 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 indu...@wso2.com > > > > > -- > Regards, > > Darshana Gunawardana > Senior Software Engineer > WSO2 Inc.; http://wso2.com > E-mail: darsh...@wso2.com > Mobile: +94718566859 > Lean . Enterprise . Middleware -- Ramith Jayasinghe Technical Lead WSO2 Inc., http://wso2.com lean.enterprise.middleware E: ram...@wso2.com P: +94 777542851 _______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
