Rails 5 db migration: how to fix ActiveRecord::ConcurrentMigrationError

Question

A previous rake db:rollback stalled. Now when attempting a new migration we get the following error:

rake aborted!
ActiveRecord::ConcurrentMigrationError: 

Cannot run migrations because another migration process is currently running.

/home/me/.rvm/gems/ruby-2.4.1@global/gems/activerecord-    5.1.4/lib/active_record/migration.rb:1315:in `with_advisory_lock'
/home/me/.rvm/gems/ruby-2.4.1@global/gems/activerecord-5.1.4/lib/active_record/migration.rb:1148:in `migrate'
/home/me/.rvm/gems/ruby-2.4.1@global/gems/activerecord-5.1.4/lib/active_record/migration.rb:1007:in `up'
/home/me/.rvm/gems/ruby-2.4.1@global/gems/activerecord-5.1.4/lib/active_record/migration.rb:985:in `migrate'
/home/me/.rvm/gems/ruby-2.4.1@global/gems/activerecord-5.1.4/lib/active_record/tasks/database_tasks.rb:171:in `migrate'
/home/me/.rvm/gems/ruby-2.4.1@global/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/home/me/.rvm/gems/ruby-2.4.1/gems/rake-12.1.0/exe/rake:27:in `<top (required)>'
/home/me/.rvm/gems/ruby-2.4.1/bin/ruby_executable_hooks:15:in `eval'
/home/me/.rvm/gems/ruby-2.4.1/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

We are using Postresql

Volding

Answer 1

Advisory locking was added in Rails 5 to prevent unplanned concurrency errors during migration. The fix is to clear the DB lock that was left in place.

Review the locks by running this SQL against your DB:

SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.waiting, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)
FROM pg_stat_activity, pg_locks
WHERE pg_locks.pid = pg_stat_activity.pid

To clear a lock, run this SQL against your DB:

select pg_advisory_unlock({the pid of the lock you want to release})

Volding

I am having the same problem but with a remote mysql database, this database does not have a pg_stat_activity table, do you know if there is a way to solve the ConcurrentMigrationError in this setting ? – Pacifier

MySQL people: ConcurrentMigrationError was added by github.com/rails/rails/pull/22122 and uses GET_LOCK (named lock) – Capture

I cleared my db locks using DBeaver but this answer served as a guide to solve the situation. Thanks – Blumenthal

in my case the process holding the lock was still running, so pg_advisory_unlock(pid) didn't work, instead I could use pg_cancel_backend(pid) to cloase the process and this also cleared the lock – Limnetic

Answer 2

For me it was resolved in this way:

Select advisory locks:

SELECT pid, locktype, mode FROM pg_locks WHERE locktype = 'advisory';

SELECT pg_terminate_backend(<PID>);

Dorena

This solution works on Heroku and the others did not. – Bernice

Answer 3

So in my case the query was different

SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)
    FROM pg_stat_activity, pg_locks
    WHERE pg_locks.pid = pg_stat_activity.pid

That will basically tell you the pids

0 years 0 mons 0 days 0 hours 0 mins -0.01005 secs    360    true    PostgreSQL JDBC Driver    2019-04-03 16:57:16.873609    2019-04-03 16:58:00.531675    2019-04-03 16:58:00.541727    active    2019-04-03 16:58:00.541725    SELECT DISTINCT age(now(), query_start) AS age, pg_stat_acti
    17272    true    ""                        <insufficient privilege>
    22640    true    ""                        <insufficient privilege>
    29466    true    ""                        <insufficient privilege>

and after that you could simply unlock the pid with the following command: select pg_advisory_unlock(#{target_pid})

e.g.:

select pg_advisory_unlock(17272)
select pg_advisory_unlock(22640)
select pg_advisory_unlock(22640)
select pg_advisory_unlock(360)

Cheers!

Thomism

This should be the selected answer. Thanks! – Sunda

I get a WARNING: you don't own a lock of type ExclusiveLock when I unlock the pid with select pg_advisory_unlock(#{target_pid}) – Smother

Answer 4

When a connection pooler for PostgreSQL is used (for example, PgBouncer, or in Yandex Cloud Odyssey),

if the connection pooler mode is 'transaction' add to database.yml:

prepared_statements: false
advisory_locks: false 

if the connection manager mode is 'session' add to database.yml:

prepared_statements: true
advisory_locks: false 

Maggie

Answer 5

I came here looking for a solution to the ActiveRecord::ConcurrentMigrationError but, in my case, no migration has stalled. Indeed, I have many EC2 instances running my Rails app and, whenever I deployed new code, some servers completed successfully while others didn't because of the aforementioned error.

If, like me, you came here for the same reason, see below the monkey patch I created to complete the migration.

# Base: https://github.com/rails/rails/blob/v7.0.1/activerecord/lib/active_record/migration.rb#L1424-L1439

# Everytime we merge our code, it gets deployed to some different servers.
# During deployment, the steps described in scripts/dependencies_install.sh
# get executed.
#
# Do notice that part of what that script does is running migrations. In our
# context (i.e., deploying the same code to multiple servers), it's problematic
# because if we had migrations running concurrently, we'd end-up with
# unexpected behavior.
#
# That's why Rails puts a "lock" within the database and raises an error if we
# try to run migrations concurrently.
#
# Below monkey patch was designed to bypass the
# ActiveRecord::ConcurrentMigrationError exception (raised when concurrent
# migrations are run).
#
# It works by retrying the migration during 120 seconds in case the
# ActiveRecord::ConcurrentMigrationError error gets raised.
#
# By retrying (instead of simply ignoring the error), we keep the old
# version of the system running while the new one will have to wait
# until the other server finishes running the migration. Once it
# happens, this task will realize it doesn't have to run migrations
# anymore - and thus the deployment script will simply proceed to restarting
# the server.
#
# This approach is important because, if we simply ignored the error,
# we could get in a situation in which the new version of the system
# would be incompatible with the DB schema - which would lead to breaks
# until the other server finished running migrations.

module ActiveRecord
  class Migrator # :nodoc:
    MAX_RETRIES = 120 # 2 minutes

    private

    def with_advisory_lock
      lock_id = generate_migrator_advisory_lock_id
      retry_count = 0

      with_advisory_lock_connection do |connection|
        got_lock = connection.get_advisory_lock(lock_id)
        raise ConcurrentMigrationError unless got_lock
        load_migrated # reload schema_migrations to be sure it wasn't changed by another process before we got the lock
        yield
      rescue ActiveRecord::ConcurrentMigrationError => e # main addition by the monkey patch
        sleep 1
        retry unless (retry_count += 1) >= MAX_RETRIES
        raise e
      ensure
        if got_lock && !connection.release_advisory_lock(lock_id)
          raise ConcurrentMigrationError.new(
            ConcurrentMigrationError::RELEASE_LOCK_FAILED_MESSAGE
          )
        end
      end
    end
  end
end

Intertwine