Improve lock contention for groups and projects transfers

Shubham Kumarrequested to merge
fix-stale-transfer-state-and-lock-contention into master

What does this MR do and why?

Improve lock contention for groups and projects transfers

Project transfers via Projects::TransferWorker can exhaust all 3 retries and permanently fail due to PG::QueryCanceled (statement timeout) when lock_both_roots cannot acquire a FOR NO KEY UPDATE lock on the root namespace row.

On busy root namespaces (e.g., large organizations with frequent concurrent operations), another transaction can hold a lock on the same row for an extended period, causing the transfer's lock acquisition to exceed the Sidekiq statement timeout (15s).

Closes #598860

References

Screenshots or screen recordings

Before After

How to set up and validate locally

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Shubham Kumar

Merge request reports