Summary

In specific situations with few roles defined in the instance and large amount of users assigned to them, removing actor from a specific role can take several seconds causing all incoming requests that require permission validation to hang.

Steps to reproduce

1. Set up a vanilla Jira instance
2. Populate projectroleactor table to get ~100k results for the following query

   SELECT ID, PID, PROJECTROLEID, ROLETYPE, ROLETYPEPARAMETER FROM projectroleactor WHERE PROJECTROLEID='10100' 

   e.g. by:

1. 1. Generating ~100k users and assigning them to a single role (e.g. "Developers") in a single project, or
   2. Generating any amount of users, assigned to a single role in multiple projects so that users*projects=~100k
2. Remove a user from role

Expected result

Action completes successfully and lasts up to 100ms

Actual result

The action is executed for several seconds which in total includes:

1. Query to remove user from projectroleactor table:

   DELETE FROM projectroleactor WHERE ID IN ('?')

   executed for several seconds.

1. Removing entries for specified role from projectRoleActorCache
2. Loading all users for specified role into cache by executing:

SELECT ID, PID, PROJECTROLEID, ROLETYPE, ROLETYPEPARAMETER FROM projectroleactor WHERE PROJECTROLEID='?

which lasts for additional several seconds.

If the request is triggered several times, this can lead to minutes of system unresponsiveness as described in: PS-40349

Workaround

1. Add index on PROJECTROLEID column in projectroleactor table
2. Limit number of actors assigned to a given project role by:
   1. Archiving unused projects
   2. Splitting roles - e.g distributing users from large "Developers" roles as in example above into "Developers", "Testers", "IT Ops" etc.
3. Threshold

   DELETE /jira02/rest/api/2/project/?/role/ 

   requests or temporarily block them

4. Do not trigger removing role actors in peak hours and do not trigger removing same actor