Issue summary

Pull requests rescoping on large monorepo with hundreds of pull requests open is causing a series of deadlocks in sta_pr_rescope_request  table in MSSQL Server 

Enabling features such as ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT seem to not help.

The behaviour is aggravated when the following property is set:

 pullrequest.rescope.threads=XX

Environment

• Bitbucket 7.x and 8.x

Steps to Reproduce

Monorepository with thousands of forks and hundreds of PRs open.

Expected Results

Actual Results

 INFO  [pull-request-rescoping:thread-1]  c.a.s.i.c.HazelcastBucketedExecutor Attempt 1 of 2 at processing bucket 'pull-request-rescoping@' for executor 'pull-request-rescoping' failed: 'could not execute batch; SQL [delete from sta_pr_rescope_request where id=?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute batch'  

Workaround

The workaround requires creating an index on a column in a certain table. Execute the following SQL on your database to create the index.

-- Create index on Bitbucket <9.0
create index idx_sta_pr_rescope_req_change on sta_pr_rescope_request_change(request_id);

Important: Remove the index again before upgrading to Bitbucket 9.0 or later. Not doing so will cause the upgrade to 9.0 to fail.

-- Drop index BEFORE upgrading to Bitbucket >=9.0
drop index idx_sta_pr_rescope_req_change on sta_pr_rescope_request_change;