last lock-retries iteration (without a timeout) doesn't open a transaction - causing LOCK TABLE to run illegally outside of a transaction block

Summary
- workaround
Steps to reproduce
Example Project
What is the current bug behavior?
What is the expected correct behavior?
Relevant logs and/or screenshots
Output of checks
- Results of GitLab environment info
- Results of GitLab application Check
Possible fixes

Summary

PG::NoActiveSqlTransaction: ERROR:  LOCK TABLE can only be used in transaction blocks

observed:

ticket link (link for GitLab team members)

stack trace / log output (fourth citation)

VERBOSE=true gitlab-rake db:migrate
main: == [advisory_lock_connection] object_id: 54960, pg_backend_pid: 4634
main: == 20240421014253 ValidateFkIdBigint4CiPipelinesPCiBuildsCiPipelinesConfigPCiStages: migrating
main: -- execute("ALTER TABLE ci_pipelines VALIDATE CONSTRAINT fk_262d4c2d19_tmp;")
main:    -> 0.0292s
main: -- execute("ALTER TABLE ci_pipeline_chat_data VALIDATE CONSTRAINT fk_64ebfab6b3_tmp;")
main:    -> 0.0007s
main: -- execute("ALTER TABLE ci_sources_pipelines VALIDATE CONSTRAINT fk_d4e29af7d7_tmp;")
main:    -> 0.0011s
main: -- execute("ALTER TABLE ci_sources_pipelines VALIDATE CONSTRAINT fk_e1bad85861_tmp;")
main:    -> 0.0009s
main: -- execute("ALTER TABLE ci_sources_projects VALIDATE CONSTRAINT fk_rails_10a1eb379a_tmp;")
main:    -> 0.0007s
main: -- execute("ALTER TABLE ci_pipeline_metadata VALIDATE CONSTRAINT fk_rails_50c1e9ea10_tmp;")
main:    -> 0.0007s
main: -- execute("ALTER TABLE ci_pipeline_messages VALIDATE CONSTRAINT fk_rails_8d3b04e3e1_tmp;")
main:    -> 0.0007s
main: -- execute("ALTER TABLE ci_pipelines_config VALIDATE CONSTRAINT fk_rails_906c9a2533_tmp;")
main:    -> 0.0006s
main: -- execute("ALTER TABLE ci_pipeline_artifacts VALIDATE CONSTRAINT fk_rails_a9e811a466_tmp;")
main:    -> 0.0008s
main: -- execute("ALTER TABLE ci_daily_build_group_report_results VALIDATE CONSTRAINT fk_rails_ee072d13b3_tmp;")
main:    -> 0.0189s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE public.ci_builds VALIDATE CONSTRAINT fk_87f4cefcda_tmp;")
main:    -> 0.0377s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE gitlab_partitions_dynamic.ci_builds_101 VALIDATE CONSTRAINT fk_87f4cefcda_tmp;")
main:    -> 0.0007s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE gitlab_partitions_dynamic.ci_builds_102 VALIDATE CONSTRAINT fk_87f4cefcda_tmp;")
main:    -> 0.0007s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: -- execute("LOCK TABLE ci_pipelines, p_ci_builds IN ACCESS EXCLUSIVE MODE")
main: == [advisory_lock_connection] object_id: 54960, pg_backend_pid: 4634
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::NoActiveSqlTransaction: ERROR:  LOCK TABLE can only be used in transaction blocks
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1370:in `block in execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:158:in `run_block_without_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:110:in `rescue in run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:98:in `run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:101:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1369:in `execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:276:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb:92:in `add_concurrent_partitioned_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:142:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: PG::NoActiveSqlTransaction: ERROR:  LOCK TABLE can only be used in transaction blocks
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1370:in `block in execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:158:in `run_block_without_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:110:in `rescue in run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:98:in `run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:101:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1369:in `execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:276:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb:92:in `add_concurrent_partitioned_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:142:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
PG::NoActiveSqlTransaction: ERROR:  LOCK TABLE can only be used in transaction blocks
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1370:in `block in execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:158:in `run_block_without_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:110:in `rescue in run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:98:in `run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:101:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1369:in `execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:276:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb:92:in `add_concurrent_partitioned_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:142:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
ActiveRecord::LockWaitTimeout: PG::LockNotAvailable: ERROR:  canceling statement due to lock timeout
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1370:in `block in execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:134:in `block in run_block_with_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:129:in `run_block_with_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:97:in `run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:101:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1369:in `execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:276:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb:92:in `add_concurrent_partitioned_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:142:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
PG::LockNotAvailable: ERROR:  canceling statement due to lock timeout
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1370:in `block in execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:134:in `block in run_block_with_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:129:in `run_block_with_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:97:in `run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:101:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1369:in `execute_add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:276:in `add_concurrent_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb:92:in `add_concurrent_partitioned_foreign_key'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:142:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20240421014253_validate_fk_id_bigint4_ci_pipelines_p_ci_builds_ci_pipelines_config_p_ci_stages.rb:141:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:40:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb:21:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:54:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/runner_backoff/active_record_mixin.rb:21:in `execute_migration_in_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:15:in `block in with_advisory_lock_connection'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/pg_backend_pid.rb:12:in `with_advisory_lock_connection'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

the last lock-retries iteration without a timeout doesn't open a transaction, so if it makes it that far, it'll then be unable to lock the table.

Other examples that seem to be the same problem; typically the 'workaround' is to try the migration when the database is quieter, ie: to avoid getting the end of the loop.

gitlab-org/release/tasks#8625 (closed)
gitlab-com/gl-infra/production#6198 (closed)
Support request for help (link for GitLab team members)

workaround

Self managed customers running into this issue need to identify what has the lock on the tables in question, and ensure that session is shut down.

Ensure there's no backup running
check if Autovacuum is running against the table
```
select pid,datname,query,backend_type from pg_stat_activity where query like '%VACUUM%';
```
- This may be impossible to stop, in which case ensure the vacuum operation is run manually so autovacuum no longer needs to run. Read more in a related issue about how to do this.

query for locks on the database

query code

with recursive activity as (
  select
    pg_blocking_pids(pid) blocked_by,
    *,
    age(clock_timestamp(), xact_start)::interval(0) as tx_age,
    -- "pg_locks.waitstart" – PG14+ only; for older versions:  age(clock_timestamp(), state_change) as wait_age
    age(clock_timestamp(), (select max(l.waitstart) from pg_locks l where a.pid = l.pid))::interval(0) as wait_age
  from pg_stat_activity a
  where state is distinct from 'idle'
), blockers as (
  select
    array_agg(distinct c order by c) as pids
  from (
    select unnest(blocked_by)
    from activity
  ) as dt(c)
), tree as (
  select
    activity.*,
    1 as level,
    activity.pid as top_blocker_pid,
    array[activity.pid] as path,
    array[activity.pid]::int[] as all_blockers_above
  from activity, blockers
  where
    array[pid] <@ blockers.pids
    and blocked_by = '{}'::int[]
  union all
  select
    activity.*,
    tree.level + 1 as level,
    tree.top_blocker_pid,
    path || array[activity.pid] as path,
    tree.all_blockers_above || array_agg(activity.pid) over () as all_blockers_above
  from activity, tree
  where
    not array[activity.pid] <@ tree.all_blockers_above
    and activity.blocked_by <> '{}'::int[]
    and activity.blocked_by <@ tree.all_blockers_above
)
select
  pid,
  blocked_by,
  case when wait_event_type <> 'Lock' then replace(state, 'idle in transaction', 'idletx') else 'waiting' end as state,
  wait_event_type || ':' || wait_event as wait,
  wait_age,
  tx_age,
  to_char(age(backend_xid), 'FM999,999,999,990') as xid_age,
  to_char(2147483647 - age(backend_xmin), 'FM999,999,999,990') as xmin_ttf,
  datname,
  usename,
  (select count(distinct t1.pid) from tree t1 where array[tree.pid] <@ t1.path and t1.pid <> tree.pid) as blkd,
  format(
    '%s %s%s',
    lpad('[' || pid::text || ']', 9, ' '),
    repeat('.', level - 1) || case when level > 1 then ' ' end,
    left(query, 1000)
  ) as query
from tree
order by top_blocker_pid, level, pid

If GitLab application activity is blocking the table lock, then shut down Puma, Sidekiq, Rails console sessions, Rails runner sessions. Look at pg_stat_activity to check what sessions are active on the database.
```
select * from pg_stat_activity;
```

Steps to reproduce

Lock a table that a migration needs to LOCK TABLE .. IN ACCESS EXCLUSIVE MODE and execute the migration code.

Example Project

What is the current bug behavior?

the code tries multiple times to get a lock, and on the final attempt does so outside of a transaction.

What is the expected correct behavior?

The lock need to be obtained inside of a transaction.

Relevant logs and/or screenshots

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info


(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check

(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)
(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)
(we will only investigate if the tests are passing)

Possible fixes

here we have raise_on_exhaustion: false, so if it tries through all the iterations and never gets the lock, it runs without a transaction

Edited Aug 20, 2024 by Ben Prescott_