Add WithHeldLockRetries for watched lock acquisition on busy tables

Imanpal Singhrequested to merge
587858-guarded-lock-acquisition into master

What does this MR do and why?

Migrations that need heavyweight locks on busy tables currently make up to 615 short attempts over ~40 minutes. Every timeout surrenders our place in the lock queue, so each attempt faces a fresh wall of traffic, and the attempts themselves repeatedly freeze the table for everyone queued behind us. When all attempts fail, the migration becomes a manual change request run during quiet APAC hours, and the logs say only "lock timeout". See #587858 for the full problem statement and https://gitlab.com/gitlab-com/gl-infra/delivery/-/issues/21531 for the failure history.

This MR adds WithHeldLockRetries, a WithLockRetries subclass that runs in hold mode. WithLockRetries itself is unchanged, and nothing calls the new class yet: routing migrations to it is a follow-up, after staging and production tests. In hold mode:

  • The first attempt is a 1-second canary: on a quiet table it simply acquires and the migration is done; if it times out, the run continues into the held attempts with the watcher's meters already running.
  • Up to three further attempts of 7 seconds keep our queue position instead of surrendering it hundreds of times. Waiting past 5 seconds makes plain autovacuum yield to us automatically, and 7 seconds keeps two sequential lock waits in the lock statement itself under the 15s statement_timeout. A held attempt that PostgreSQL ends as a deadlock victim is retried like a lock timeout.
  • While we wait, a watcher on a second connection polls once per second. It logs who is blocking us and counts the sessions queued behind us, including only waits that started after ours: whoever was already stuck before we arrived is not our damage.
  • The watcher cancels our own wait (never any other session) as soon as the measured damage crosses a budget of 10 blocked-backend-seconds per attempt (20 per run), or earlier if the queue behind us reaches 40% of the server's max_connections (capped at 20), or the table proves too hot for any watched hold. The abort raises a typed error (WaiterPileupError with a machine-readable reason, or WatchLostError) instead of stalling the deploy.
  • No held attempt starts without a responsive watcher: if the watcher cannot arm, dies, goes blind, or wedges, the current wait is canceled or runs out its own lock_timeout, and the run either stops with WatchLostError or falls back to the remaining rungs of the default WithLockRetries ladder. A held attempt that finds our session on another backend stops with WatchLostError before its block runs.
  • Every typed abort and every hold attempt that ends in a lock timeout or a deadlock logs a census of the sessions holding locks on the declared tables (pid, transaction age, endpoint attribution, wraparound-vacuum flag). An outside cancel or statement_timeout re-raises without one.
flowchart TB
    classDef entry fill:#ede7f6,stroke:#7e57c2,color:#311b92
    classDef ok fill:#e8f5e9,stroke:#43a047,color:#1b5e20
    classDef abort fill:#fff3e0,stroke:#fb8c00,color:#e65100
    classDef same fill:#eceff1,stroke:#90a4ae,color:#37474f
    classDef watch fill:#e1f5fe,stroke:#039be5,color:#01579b

    M[Migration needs ACCESS EXCLUSIVE<br>on the declared tables]:::entry --> G{Called through<br>WithHeldLockRetries?}:::entry
    G -- no --> L([WithLockRetries:<br>not changed by this MR]):::same
    G -- yes --> W[Watcher arms on a second connection,<br>must complete its first poll<br>or fall back to the default ladder]:::watch
    W --> A1

    subgraph HOLD [" The migration: held attempts "]
        A1[Canary attempt<br>lock_timeout = 1s]
        A2[Attempts 2 to 4<br>lock_timeout = 7s, queue position kept.<br>Past 5s, plain autovacuum yields to us]
        A1 -- timed out --> A2
    end

    A1 -- granted --> OK([DDL runs in milliseconds,<br>queue drains]):::ok
    A2 -- granted --> OK
    A2 -- all timed out --> EX([AttemptsExhaustedError,<br>census logs who held the locks]):::abort

    subgraph WATCH [" The watcher: loops while an attempt is waiting "]
        P[LOOK, one query:<br>pg_blocking_pids: who blocks us,<br>pg_locks: who waits behind us,<br>counting only waits newer than ours]:::watch
        E{Any limit crossed?<br>queue at 40% of max_connections, max 20,<br>bill past 10s attempt / 20s run,<br>full on first look, blind}:::watch
        S[Sleep 1 second]:::watch
        P --> E
        E -- no --> S
        S --> P
        E -- yes --> K[pg_cancel_backend on OUR session,<br>only while it still waits for the lock]:::abort
    end

    A1 & A2 -. seen once per second .-> P
    K -- cancels our wait --> A1 & A2
    A1 & A2 -- wait canceled --> R([Typed error with the reason.<br>The run stops, never an unlimited wait]):::abort

How to review this version

  • All 14 files are new: WithLockRetries and its spec have no diff in this MR, and lib/gitlab/database/lock_acquisition/* has no code changes since dde0cb16, the version the backend review looked at.
  • WithHeldLockRetries is the hold-mode code that used to sit inside WithLockRetries plus the fixes below, as overrides that call super; git diff --color-moved=dimmed-zebra dde0cb16 origin/587858-guarded-lock-acquisition shows what moved.
  • New since dde0cb16: a deadlock during a held attempt is retried (S8), a session that reconnects to another backend between attempts stops the run with WatchLostError instead of holding unwatched (S9), and a Ctrl-C while the watcher arms no longer leaves its thread running.
  • The way in is WithHeldLockRetries.new(tables: [...]): hold_lock_acquisition: is gone, and passing timing_configuration: raises.

How to set up and validate locally

Save the script below anywhere (for example tmp/lock_hold_demo.rb) and run:

bin/rails runner tmp/lock_hold_demo.rb

Takes under a minute. Prints one PASS/FAIL line per scenario and exits non-zero if any fails.

lock_hold_demo.rb 
# frozen_string_literal: true

# Manual validation aid for WithHeldLockRetries (watched hold mode): real
# blocker and waiter sessions around the real code, one PASS/FAIL line per
# scenario, non-zero exit on any failure. Nothing is written; the only table
# touched is a throwaway one created and dropped here.
#
#   bin/rails runner tmp/lock_hold_demo.rb

require 'pg'

WLR = Gitlab::Database::WithHeldLockRetries
TABLE = '_test_lock_hold_demo'
TABLE2 = '_test_lock_hold_demo_two'

class EventLog
  def initialize
    @events = []
  end

  def info(payload)
    @events << payload
  end

  def find(message, **match)
    @events.find { |e| e[:message] == message && match.all? { |k, v| e[k] == v } }
  end

  def all(message)
    @events.select { |e| e[:message] == message }
  end
end

def mono
  Process.clock_gettime(Process::CLOCK_MONOTONIC)
end

def ddl
  ApplicationRecord.connection
end

def pg_session(app_name)
  c = ApplicationRecord.connection_db_config.configuration_hash
  PG.connect(**{ host: c[:host], port: c[:port], dbname: c[:database], user: c[:username],
                 password: c[:password], application_name: app_name }.compact)
end

MONITOR = pg_session('lock-hold-demo-monitor')
DDL_PID = ddl.select_value('SELECT pg_backend_pid()')
CEILING = (ddl.select_value('SHOW max_connections').to_i * 0.4).floor.clamp(2, 20)

# Below the 1s hold canary: WithLockRetries' own timings, whatever they are.
def default_ladder?(lock_timeout_log)
  !lock_timeout_log.nil? && lock_timeout_log[:lock_timeout_in_ms] < 1000
end

def run_hold(log, env: {}, tables: [TABLE], hold: true, &block)
  block ||= -> { ddl.execute("LOCK TABLE #{TABLE} IN ACCESS EXCLUSIVE MODE") }
  lock_retries = if hold
                   WLR.new(connection: ddl, logger: log, env: env, tables: tables)
                 else
                   Gitlab::Database::WithLockRetries.new(connection: ddl, logger: log, env: env)
                 end

  lock_retries.run(raise_on_exhaustion: true, &block)
end

def capture_error
  yield
  nil
rescue StandardError => e
  e
end

def open_blocker
  session = pg_session('lock-hold-demo-blocker')
  session.exec("BEGIN; LOCK TABLE #{TABLE} IN ACCESS SHARE MODE")
  session
end

def alive?(session)
  session.exec('SELECT 1') && true
rescue PG::Error
  false
end

def close_all(sessions)
  sessions.each do |s|
    s.cancel
    s.close
  rescue PG::Error
    nil
  end
end

# The DDL is only billed for waiters that queue after it started waiting, so
# every scenario waits for that moment before acting.
def wait_until_ddl_waiting(log, iteration, timeout: 15)
  deadline = mono + timeout
  loop do
    if log.find('Lock timeout is set', current_iteration: iteration)
      row = MONITOR.exec_params('SELECT wait_event_type FROM pg_stat_activity WHERE pid = $1', [DDL_PID])
      break if row.ntuples == 1 && row.getvalue(0, 0) == 'Lock'
    end

    raise "DDL never reached a lock wait in attempt #{iteration}" if mono > deadline

    sleep 0.02
  end
end

def drain(waiters)
  waiters.count do |w|
    w.block(5)
    w.get_last_result
    true
  rescue PG::Error
    false
  end
end

def scenario(id, title)
  @ran += 1
  started = mono
  ok, detail = yield
  @failed << id unless ok
  puts format('%-3s %-24s [%5.2fs] %s  %s', id, title, mono - started, ok ? 'PASS' : 'FAIL', detail)
rescue StandardError => e
  @failed << id
  puts format('%-3s %-24s [%5.2fs] FAIL  %s: %s', id, title, mono - started, e.class, e.message)
end

def pileup(waiter_count, expected_reason)
  log = EventLog.new
  blocker = open_blocker
  waiters = Array.new(waiter_count) { pg_session('lock-hold-demo-waiter') }
  arrived = nil
  spawner = Thread.new do
    wait_until_ddl_waiting(log, 2)
    arrived = mono
    waiters.each { |w| w.send_query("SELECT count(*) FROM #{TABLE}") }
  end
  error = capture_error { run_hold(log) }
  raised_at = mono
  spawner.join
  drained = drain(waiters)
  blocker_alive = alive?(blocker)
  cancel = log.find('Lock acquisition watcher canceling own session')
  reason = error.respond_to?(:reason) ? error.reason : nil
  ok = error.is_a?(WLR::WaiterPileupError) && reason == expected_reason && cancel&.dig(:signalled) == true &&
    drained == waiter_count && blocker_alive
  [ok, "#{error.class.name.demodulize}(#{reason}) #{(raised_at - arrived).round(1)}s after arrivals; " \
    "#{drained}/#{waiter_count} waiters drained; blocker alive=#{blocker_alive}; " \
    "ceiling=#{cancel&.dig(:count_ceiling)}"]
ensure
  close_all(waiters.to_a)
  close_all([blocker].compact)
end

@failed = []
@ran = 0
ddl.execute("DROP TABLE IF EXISTS #{TABLE}, #{TABLE2}; CREATE TABLE #{TABLE} (id int); CREATE TABLE #{TABLE2} (id int)")
puts "lock-hold demo: pg #{ddl.select_value('SHOW server_version')}, " \
  "max_connections=#{ddl.select_value('SHOW max_connections')}, derived ceiling=#{CEILING}, " \
  "deadlock_timeout=#{ddl.select_value('SHOW deadlock_timeout')}, " \
  "rails pool=#{ApplicationRecord.connection_pool.size}"
puts

begin
  scenario('S1', 'plain WithLockRetries') do
    log = EventLog.new
    run_hold(log, env: {}, hold: false)
    first = log.find('Lock timeout is set', current_iteration: 1)
    ok = default_ladder?(first) && log.all('Lock acquisition attempt blocked').empty? &&
      log.all('Lock acquisition watch').empty?
    [ok, "default ladder (first lock_timeout=#{first&.dig(:lock_timeout_in_ms)}ms), no watcher, no census"]
  end

  scenario('S2', 'clean hold') do
    log = EventLog.new
    blocker = open_blocker
    releaser = Thread.new do
      wait_until_ddl_waiting(log, 2)
      sleep 0.5
      blocker.exec('ROLLBACK')
    end
    error = capture_error { run_hold(log) }
    releaser.join
    census = log.find('Lock acquisition attempt blocked', current_iteration: 1)
    row = census&.dig(:blockers)&.find { |b| b['pid'] == blocker.backend_pid }
    billed = log.all('Lock acquisition watch').map { |e| e[:window_spent] }.max.to_f
    finished = log.find('Lock acquisition watch finished')
    ok = error.nil? && row && billed == 0 && finished && finished[:cancel_requested] == false
    [ok, "canary census named blocker pid #{blocker.backend_pid} #{row&.dig('lock_modes')} " \
      "(#{row&.dig('application_name')}); max window_spent=#{billed}; " \
      "acquired on attempt #{log.find('Migration finished')&.dig(:current_iteration)}"]
  ensure
    close_all([blocker].compact)
  end

  scenario('S3', 'pile-up (5 waiters)') { pileup(5, :accumulated_wait) }

  scenario('S4', "pool ceiling (#{CEILING})") { pileup(CEILING, :pool_exhaustion) }

  scenario('S5', 'watcher killed') do
    log = EventLog.new
    blocker = open_blocker
    killer = pg_session('lock-hold-demo-killer')
    thread = Thread.new do
      wait_until_ddl_waiting(log, 2)
      deadline = mono + 3
      while mono < deadline
        killer.exec("SELECT pg_terminate_backend(pid) FROM pg_stat_activity " \
          "WHERE application_name = 'lock_acquisition_watcher'")
        sleep 0.2
      end
      blocker.exec('ROLLBACK')
    end
    error = capture_error { run_hold(log) }
    thread.join
    # Two designed outcomes: the watcher degrades and cancels the watched
    # wait as its dying act (typed abort), or it survives the kills by
    # reconnecting and re-hardening between polls and watches through to a
    # clean acquisition. The only failure is an unwatched hold.
    lost = log.find('Lock acquisition watcher canceled watched wait on watch loss', signalled: true)
    failures = log.all('Lock acquisition watcher poll failed').size
    finished = log.find('Lock acquisition watch finished')
    died = error.is_a?(WLR::WatchLostError) && !lost.nil?
    recovered = error.nil? && failures >= 1 && finished && finished[:watcher_degraded] == false
    [died || recovered, "#{died ? 'dying act' : 'recovered'}: " \
      "#{error ? error.class.name.demodulize : 'acquired'}; poll failures=#{failures}; " \
      "degraded=#{finished&.dig(:watcher_degraded).inspect}; " \
      "dying-act signalled=#{lost&.dig(:signalled).inspect}"]
  ensure
    close_all([blocker, killer].compact)
  end

  scenario('S6', 'DISABLE_LOCK_RETRIES') do
    log = EventLog.new
    seen = []
    run_hold(log, env: { 'DISABLE_LOCK_RETRIES' => 'true' }) do
      seen << [ddl.transaction_open?, ddl.select_value('SHOW lock_timeout'),
        Thread.list.any? { |t| t.name == 'lock-acq-watcher' }]
    end
    disabled = log.find('DISABLE_LOCK_RETRIES environment variable is true, bypassing hold mode')
    ok = seen == [[false, '0', false]] && !disabled.nil?
    [ok, "block ran #{seen.size}x (transaction_open, lock_timeout, watcher_thread)=#{seen.first.inspect}; " \
      "bypass logged=#{!disabled.nil?}"]
  end

  scenario('S7', 'wrong table name') do
    log = EventLog.new
    error = capture_error { run_hold(log, tables: ['does_not_exist']) }
    demoted = log.find('Hold mode demoted')
    first = log.find('Lock timeout is set', current_iteration: 1)
    ok = error.nil? && demoted&.dig(:demotion_reason).to_s.include?('do not exist') &&
      default_ladder?(first)
    [ok, "demoted (#{demoted&.dig(:demotion_reason)}); " \
      "ran default ladder (first lock_timeout=#{first&.dig(:lock_timeout_in_ms)}ms)"]
  end

  scenario('S8', 'deadlock during a hold') do
    log = EventLog.new
    app = pg_session('lock-hold-demo-app')
    # Both sides use gprd's 5s; where the role cannot set it, both keep the
    # server value, and we still wait first, so our session is the victim.
    [-> { ddl.execute("SET deadlock_timeout = '5s'") }, -> { app.exec("SET deadlock_timeout = '5s'") }].each do |set|
      set.call
    rescue StandardError
      nil
    end
    app.exec("BEGIN; LOCK TABLE #{TABLE2} IN ROW EXCLUSIVE MODE")
    cycler = Thread.new do
      wait_until_ddl_waiting(log, 2)
      app.send_query("SELECT count(*) FROM #{TABLE}")
      app.get_last_result
      app.exec('COMMIT')
    end
    error = capture_error do
      run_hold(log, tables: [TABLE, TABLE2]) do
        ddl.execute("LOCK TABLE #{TABLE}, #{TABLE2} IN ACCESS EXCLUSIVE MODE")
      end
    end
    cycler.join(20)
    deadlock = log.find('Held attempt ended in a deadlock')
    acquired_on = log.find('Migration finished')&.dig(:current_iteration)
    ok = error.nil? && deadlock&.dig(:current_iteration) == 2 && acquired_on == 3
    [ok, "#{error ? "#{error.class.name.demodulize} raised" : 'no error'}; deadlock victim on attempt " \
      "#{deadlock&.dig(:current_iteration).inspect}, retried, acquired on attempt #{acquired_on.inspect}"]
  ensure
    begin
      ddl.execute('RESET deadlock_timeout')
    rescue StandardError
      nil
    end
    close_all([app].compact)
  end

  # Last: it makes Rails reconnect the DDL connection to a new backend.
  scenario('S9', 'killed between attempts') do
    log = EventLog.new
    blocker = open_blocker
    lock_retries = WLR.new(connection: ddl, logger: log, env: {}, tables: [TABLE])
    killed = false
    lock_retries.define_singleton_method(:sleep) do |seconds|
      MONITOR.exec("SELECT pg_terminate_backend(#{DDL_PID})") unless killed
      killed = true
      Kernel.sleep(seconds)
    end
    runs = 0
    error = capture_error do
      lock_retries.run(raise_on_exhaustion: true) do
        runs += 1
        ddl.execute("LOCK TABLE #{TABLE} IN ACCESS EXCLUSIVE MODE")
      end
    end
    moved = log.find('Hold session moved to a new backend')
    ok = error.is_a?(WLR::WatchLostError) && runs == 1 && moved
    [ok, "#{error ? error.class.name.demodulize : 'no error'}; block ran #{runs}x; " \
      "backend #{moved&.dig(:watched_pid) || DDL_PID} -> #{moved&.dig(:backend_pid) || ddl.select_value('SELECT pg_backend_pid()')}"]
  ensure
    close_all([blocker].compact)
  end
ensure
  ddl.execute("DROP TABLE IF EXISTS #{TABLE}, #{TABLE2}")
  MONITOR.close
end

puts
puts "#{@ran} scenarios, #{@ran - @failed.size} passed, #{@failed.size} failed#{@failed.any? ? " (#{@failed.join(', ')})" : ''}"
exit(1) if @failed.any?

What the script does

Setup:

  1. Creates two empty throwaway tables and drops them at the end.
  2. Opens separate Postgres sessions with the pg gem to act as blocker, waiters and monitor. Rails keeps its own connection for the migration.
  3. A blocker holds a reader lock on the table, then the real WithHeldLockRetries runs and tries to take an exclusive lock, so it has to wait. S2 to S5 act once the migration is really waiting; S8 builds a real lock cycle across the two tables, and S9 kills the migration session during the retry sleep.
  4. Every log line the code emits is captured so scenarios check what was logged, not only what was returned.

Scenarios:

Scenario Checks
S1 Plain WithLockRetries Default ladder runs (first lock_timeout below the 1s canary), no watcher, no census
S2 Clean hold Census names the blocker, nothing billed, lock acquired once the blocker releases
S3 Pile-up, 5 waiters Budget fills in ~2s, own session canceled with reason accumulated_wait, all waiters finish, blocker untouched
S4 Pool ceiling Ceiling read from SHOW max_connections, trips on first poll with reason pool_exhaustion
S5 Watcher killed Watcher backend terminated repeatedly mid-wait; passes on either designed outcome: the dying-act cancel ends the run with WatchLostError, or the watcher reconnects, re-hardens and watches through to acquisition. Fails only on an unwatched hold
S6 DISABLE_LOCK_RETRIES Block runs once, no transaction, no lock timeout, no watcher thread
S7 Wrong table name Hold demoted, default ladder runs
S8 Deadlock during a hold We hold one table and wait on the other while another session holds the other and queues on the first; our wait is the deadlock victim, the attempt is retried, and the lock is acquired on attempt 3
S9 Killed between attempts The migration's backend is terminated during the retry sleep; Rails reconnects, and the run stops with WatchLostError instead of holding the next attempt on a backend the watcher is not watching

Notes:

  • Nothing is written to any table. Blockers and waiters take reader locks, and S8 also takes a ROW EXCLUSIVE lock on the second throwaway table.
  • S8 sets deadlock_timeout to 5s on both sessions where the role may (it is superuser-only); otherwise both keep the server value, which works the same. S9 runs last because it makes Rails reconnect the migration connection.
  • S3 depends on 1 second polls. On a very loaded machine a poll gap over 2s turns it into WatchLostError. Just rerun.
  • The script proves the code reacts correctly to a pile-up. What production pile-ups look like is the Mimir data in the budget thread, and the peak change request is the test of both together.

Queries the watcher runs

Each poll is one statement on the watcher's own connection, at most once per second (pg_blocking_pids takes the lock manager's locks, so polls are kept at least 1s apart), bounded by a 500ms statement_timeout:

SELECT
  (SELECT array_to_string(pg_blocking_pids($DDL_PID), ',')) AS blocking_pids,
  (SELECT count(DISTINCT w.pid) FROM pg_locks w
    WHERE NOT w.granted AND w.database = $DB_OID AND w.pid <> $DDL_PID
      AND w.waitstart >= (SELECT min(o.waitstart) FROM pg_locks o
                          WHERE o.pid = $DDL_PID AND NOT o.granted)
      AND w.locktype = 'relation'
      AND w.relation IN (SELECT d.relation FROM pg_locks d
                         WHERE d.pid = $DDL_PID AND d.database = $DB_OID
                           AND d.relation IS NOT NULL)) AS queued_waiters,
  -- unfiltered_waiters: the same count without the locktype filter
  (SELECT wait_event_type FROM pg_stat_activity
    WHERE pid = $DDL_PID AND backend_start = $BACKEND_START) AS ddl_wait_event_type;

When a hold attempt ends in a lock timeout, a deadlock or a typed abort (at most 4 per run), the census reads the granted locks on the declared tables joined to pg_stat_activity. At arming, one read each of max_connections, the session identity, and pg_partition_tree for the declared tables, and each held attempt reads pg_backend_pid() once. All of these read system state, not application tables; plain WithLockRetries runs none of them.

Known limits, stated deliberately

  • Hold mode governs lock acquisition only. Once the lock is granted, behavior is exactly today's: the DDL runs and the lock is held until the transaction ends. The cluster-wide transaction_timeout rollout is the server-side backstop for that phase.

  • The budget is a measured estimator with one-poll slop, not a hard cap: a burst arriving right after a poll is billed at the next one, and the canary window gets at most one look.

  • On an otherwise idle table, a single query arriving behind the hold can wait up to the full 7s (nothing trips at depth 1) and may hit its own statement_timeout where today's 2s rungs would not.

  • The depth ceiling is a PostgreSQL queue-depth heuristic, not a PgBouncer pool guarantee: clients queued inside PgBouncer waiting for a server connection are not visible in pg_locks.

  • A statement locking 3+ relations (a partitioned parent counts every partition) can cumulatively exceed statement_timeout, and waiters on an already-acquired relation are not billed while we wait on the next one (the guards read low, never high). Declare two or fewer relations.

  • An acquired ACCESS EXCLUSIVE lock is WAL-logged at acquisition, so replicas briefly replay it even for a run that later aborts; replica lag and recovery-conflict cancellations are part of the rollout acceptance criteria.

  • The 7s attempts outlast deadlock_timeout, so in a lock cycle whichever waiter's deadlock check fires first is aborted. Ours is retried, but an app transaction that queued behind us on one table while holding the other can be the victim instead (for example against LOCK TABLE a, b); today's 2s rungs mostly end before either check runs.

  • Hold mode assumes no outer transaction: a retry cannot release locks the caller's own transaction already holds.

  • Direct PostgreSQL connections only, with no runtime guard: behind PgBouncer the pooled backend pid is not ours to watch. Through the load-balancing proxy the identity read at arming can go to a replica, which the backend check turns into WatchLostError on the first attempt. Migrations run under rake with load balancing off.

Follow-ups

  • Route migrations to WithHeldLockRetries (an option on the with_lock_retries helper) once the staging and production change requests pass, then opt in a migration that failed before.
  • Optional observe-only mode, if the change requests leave the budget or ceiling in doubt: the watcher records per-second queue depth and the census on real migrations, no cancels, behavior unchanged; the recorded data replays through the hold math.
  • A watcher-side lock-cycle check, so the watcher can usually cancel our own wait before PostgreSQL picks an app transaction as the deadlock victim.
  • deadlock_timeout grant for the gitlab role that migrations run as (GRANT SET ON PARAMETER deadlock_timeout), which would let the held attempts shrink from 7s to ~3s while keeping the autovacuum yield.
  • PgBouncer-aware admission: check the affected server pool's headroom before arming, instead of the max_connections-derived ceiling alone. Before enabling hold mode against small-headroom pools, run a PgBouncer-in-the-loop test (transaction pooling, mostly-occupied pool, waiters consuming the last server slots) with pass/fail latency and pool-recovery criteria set in advance, against the current ladder as baseline.
  • Re-derive the budget and ceiling from the change-request runs and production data (blocker types, arrival rates and per-table wait durations).
  • Replica metrics (replay lag, recovery-conflict cancellations) in the change-request acceptance criteria for the first live holds.

MR acceptance checklist

Evaluated against the MR acceptance checklist.

Changelog: added

Edited by Imanpal Singh

Merge request reports