Removing actor from project role can make Jira unresponsive

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