Use with_lock_retries at the migration level

What does this MR do?

Use with_lock_retries at the migration level

For transactional migrations, apply with-lock-retries methodology at the
full transaction level. This allows us to remove usage of
with-lock-retries from helpers and we don't need to use it inside
migrations explicitly.

The overall intent here is to remove subtransactions.

See https://gitlab.com/gitlab-org/gitlab/-/issues/339115

• Related documentation change: !69873 (merged) or see docs directly

How to setup and validate locally (strongly suggested)

create table example (id serial primary key);

Example migration:

class TestMigration < Gitlab::Database::Migration[1.0]
  enable_lock_retries!  # Toggle this on/off

  def up
    execute 'LOCK TABLE example in EXCLUSIVE MODE'
    puts 'locked!'
  end

  def down
    puts 'DOWN'
  end
end

With the enable_lock_retries!, we expect to see control statements for SET lock_timeout... when migrating up.

In another psql session, run this while migrating up. We expect to see with_lock_retries methodology kicking in:

BEGIN;
LOCK TABLE example in EXCLUSIVE MODE;

Does this MR meet the acceptance criteria?

Conformity

• I have added/updated documentation, or it's not needed. (Is documentation required?)
• I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
• I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
• I have self-reviewed this MR per code review guidelines.
• This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
• I have followed the style guides.
• This change is backwards compatible across updates, or this does not apply.