## Background There have been a series of GitLab.com incidents recently that were caused by database subtransactions causing database-wide contention coming from `SubtransControlLocks` locking our database queries. The database contention is related to an internal PostgreSQL mechanism used to track nested subtransactions (SAVEPOINT or EXCEPTION SQL commands). This issue is not unique to any specific feature domain. 1. [2021-08-11: high numberPostgreSQL statement timeouts](https://gitlab.com/gitlab-com/gl-infra/production/-/issues/5333) 1. [2021-08-10: Postgres transactions showing high rate of statement timeouts](https://gitlab.com/gitlab-com/gl-infra/production/-/issues/5324) 1. [2021-08-06: Postgres transactions showing high rate of statement timeouts](https://gitlab.com/gitlab-com/gl-infra/production/-/issues/5306) There is an ongoing effort to increase the visibility of sub-transactions in [Extend subtransactions instrumentation to reveal the number of created subtransactions per transaction](https://gitlab.com/gitlab-org/gitlab/-/issues/337843). On the other hand, we may have reached a point that subtransactions are causing problems more than the benefits they offer. ## Proposal ~~A holistic approach to eliminate or prevent sub-transactions can be a viable and efficient solution to the lock contention, instead of handling individual queries at each feature group's level.~~ ### Short Term Identify immediate opportunities to reduce the risk of subtransactions causing production incidents. ### Long term 1. Find root cause of incidents via the data collected from the instrumentation work mentioned above. 1. Refactor or rewrite queries to eliminate long transactions that were known harmful to scalability. /cc @andrewn @stanhu @sgoldstein @craig-gomes