Subtransaction detected in migrations on canary at 12.12.2022
Description
A database transaction with a sub-transaction has been detected on the CNY environment today (12.12.2022): https://log.gprd.gitlab.net/app/discover#/doc/7092c4e2-4eb5-46f2-8305-a7da2edad090/pubsub-rails-inf-gprd-014588?id=tpKsBYUBR8eYS4p60b4h
A single subtransaction running on GitLab.com can trigger a database contention and a full database outage.
It seems that this sub-transaction originates from a migration, run on canary environment targeting the CI database.
Backtrace:
lib/gitlab/database/transaction/observer.rb:42:in `block in register!'
lib/gitlab/database/with_lock_retries.rb:172:in `execute'
lib/gitlab/database/with_lock_retries.rb:130:in `block in run_block_with_lock_timeout'
lib/gitlab/database/with_lock_retries.rb:129:in `run_block_with_lock_timeout'
lib/gitlab/database/with_lock_retries.rb:97:in `run'
lib/gitlab/database/lock_writes_manager.rb:114:in `with_lock_retries'
lib/gitlab/database/lock_writes_manager.rb:89:in `block in with_retries'
lib/gitlab/database/lock_writes_manager.rb:98:in `with_statement_timeout_retries'
lib/gitlab/database/lock_writes_manager.rb:88:in `with_retries'
lib/gitlab/database/lock_writes_manager.rb:81:in `execute_sql_statement'
lib/gitlab/database/lock_writes_manager.rb:61:in `lock_writes'
lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:69:in `lock_writes_on_table'
lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:23:in `block in exec_migration'
lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:22:in `each'
lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:22:in `exec_migration'
lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
lib/gitlab/database/with_lock_retries.rb:134:in `block in run_block_with_lock_timeout'
lib/gitlab/database/with_lock_retries.rb:129:in `run_block_with_lock_timeout'
lib/gitlab/database/with_lock_retries.rb:97:in `run'
lib/gitlab/database/migrations/lock_retry_mixin.rb:34:in `ddl_transaction'