[RANGER-3162] Deadlocks when trying to run parallel requests against REST api

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.1.0
• Component/s: admin, Ranger
• Assignee: Unassigned
• Reporter: Adam Rempter
• Created: 27/Jan/21 11:55
• Updated: 29/Nov/21 13:41

Description

We use Ranger 2.1.0 and latest Mariadb (single instance). We did setup MariaDb transaction level on DB side to READ COMMITED to ensure that we do not get inconsistent reads.

However we sometimes observe database deadlocks when using Ranger REST API with parallel requests.

We use API endpoint to delete and post series of five policy Json's:

DELETE http://localhost:6080/service/public/v2/api/policy and

POST http://localhost:6080/service/public/v2/api/policy

 

Ranger errors:

2021-01-27 11:33:21,481 [http-bio-6080-exec-304] INFO org.apache.ranger.common.RESTErrorUtil (RESTErrorUtil.java:312) - Operation error. response=VXResponse=

{org.apache.ranger.view.VXResponse@1810c845statusCode =\{1}

msgDesc=

{Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction Error Code: 1213 Call: INSERT INTO x_policy_ref_resource (ADDED_BY_ID, CREATE_TIME, policy_id, resource_def_id, resource_name, UPDATE_TIME, UPD_BY_ID) VALUES (?, ?, ?, ?, ?, ?, ?) bind => [7 parameters bound] Query: ValueReadQuery(name="x_policy_ref_resource_SEQ" sql="SELECT LAST_INSERT_ID()")}

messageList={null} }
javax.ws.rs.WebApplicationException

 

DB level status for innoDB:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-27 11:33:21 0x7fe624762700

• • • (1) TRANSACTION:
      TRANSACTION 24004, ACTIVE 0 sec inserting
      mysql tables in use 1, locked 1
      LOCK WAIT 17 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 2
      MySQL thread id 39, OS thread handle 140626830948096, query id 78458 172.21.0.1 ranger Update
      INSERT INTO x_policy_ref_resource (ADDED_BY_ID, CREATE_TIME, policy_id, resource_def_id, resource_name, UPDATE_TIME, UPD_BY_ID) VALUES (1, '2021-01-27 11:33:21', 417, 73, 'path', '2021-01-27 11:33:21', 1)
    • (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 64 page no 22 n bits 424 index x_policy_ref_res_UK_polId_resDefId of table `ranger`.`x_policy_ref_resource` trx id 24004 lock_mode X locks gap before rec insert intention waiting
      Record lock, heap no 358 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
      0: len 8; hex 80000000000001a3; asc ;;
      1: len 8; hex 8000000000000043; asc C;;
      2: len 8; hex 8000000000000dc4; asc ;;

• • • (2) TRANSACTION:
      TRANSACTION 24005, ACTIVE 0 sec inserting
      mysql tables in use 1, locked 1
      17 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 2
      MySQL thread id 74, OS thread handle 140626430928640, query id 78478 172.21.0.1 ranger Update
      INSERT INTO x_policy_ref_resource (ADDED_BY_ID, CREATE_TIME, policy_id, resource_def_id, resource_name, UPDATE_TIME, UPD_BY_ID) VALUES (1, '2021-01-27 11:33:21', 418, 53, 'schema', '2021-01-27 11:33:21', 1)
    • (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 64 page no 22 n bits 424 index x_policy_ref_res_UK_polId_resDefId of table `ranger`.`x_policy_ref_resource` trx id 24005 lock_mode X locks gap before rec
      Record lock, heap no 358 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
      0: len 8; hex 80000000000001a3; asc ;;
      1: len 8; hex 8000000000000043; asc C;;
      2: len 8; hex 8000000000000dc4; asc ;;

• • • (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 64 page no 22 n bits 424 index x_policy_ref_res_UK_polId_resDefId of table `ranger`.`x_policy_ref_resource` trx id 24005 lock_mode X locks gap before rec insert intention waiting
      Record lock, heap no 358 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
      0: len 8; hex 80000000000001a3; asc ;;
      1: len 8; hex 8000000000000043; asc C;;
      2: len 8; hex 8000000000000dc4; asc ;;

 

Please advice it this is something we could tune on Ranger side? Or maybe some suggestions from your side how to tune DB connection parameters?

 

Thanks in advance