> On 三月 25, 2022, 4:23 a.m., Madhan Neethiraj wrote: > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefAccessTypeDao.java > > Lines 108 (patched) > > <https://reviews.apache.org/r/73913/diff/1/?file=2266989#file2266989line108> > > > > findByPolicyId(policyId) returns XXPolicyRefAccessType objects, which > > is unnecessary here since only id is needed. Consider adding a new named > > query to return ids. > > > > Also, would replacing current query in > > "XXPolicyRefAccessType.deleteByPolicyId" with the following help avoid gap > > lock, since SELECT in sub-query might not be treated as a 'locking read'? > > > > current: > > DELETE FROM XXPolicyRefGroup obj WHERE obj.policyId = :policyId > > > > replace with: > > DELETE FROM XXPolicyRefGroup obj > > WHERE obj.id in (SELECT obj2.id FROM XXPolicyRefGroup obj2 WHERE > > obj2.policyId = :policyId)
1. I tried this sql in my environment. ```sql DELETE FROM XXPolicyRefResource obj WHERE obj.id in (SELECT obj2.id FROM XXPolicyRefResource obj2 WHERE obj2.policyId = :policyId) ``` The results show that the deadlock problem still exists. The reason is myql side will decompose this sql into 4 stages: * create temporary table * insert into temporary table * delete from x_policy_ref_resource using temporary table * delete temporary table 2. I have updated the patch, the main change is adding a new named query to return ids. 3. The same tests were done as desbribed above, and everything went well. - Xuze ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/73913/#review224207 ----------------------------------------------------------- On 三月 25, 2022, 9:33 a.m., Xuze Yang wrote: > > ----------------------------------------------------------- > This is an automatically generated e-mail. To reply, visit: > https://reviews.apache.org/r/73913/ > ----------------------------------------------------------- > > (Updated 三月 25, 2022, 9:33 a.m.) > > > Review request for ranger, Abhay Kulkarni, Madhan Neethiraj, and Velmurugan > Periasamy. > > > Bugs: RANGER-3681 > https://issues.apache.org/jira/browse/RANGER-3681 > > > Repository: ranger > > > Description > ------- > > Modify delete sql to delete according to the primary key, to solve the > problem of deadlock under REPEATABLE-READ isolation level > > > Diffs > ----- > > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefAccessTypeDao.java > b9a60cb > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefConditionDao.java > e14bc14 > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefDataMaskTypeDao.java > 7e7b8d4 > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefGroupDao.java > 5f9d9ed > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefResourceDao.java > 0ea7de9 > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefRoleDao.java > 3ae7e7a > > src/security-admin/src/main/java/org/apache/ranger/db/XXPolicyRefUserDao.java > 518139a > src/security-admin/src/main/resources/META-INF/jpa_named_queries.xml > 5813209 > > > Diff: https://reviews.apache.org/r/73913/diff/2/ > > > Testing > ------- > > 1. Calles the createPolicy() method 1000 times using two threads. The results > showed that all policies are successfully created without deadlock exception. > 2. Calles the updatePolicy() method 1000 times using two threads. The results > showed that all policies are successfully updated without deadlock exception. > 3. Calles the deletePolicy() method 1000 times using two threads. The results > showed that all policies are successfully deleted. > > > Thanks, > > Xuze Yang > >
