Rails 6 adds support for disabling database advisory locks


What is an Advisory Lock?

Databases provide various lock modes to control concurrent access to tables and data in them.

Advisory locks provide a convenient way to obtain a lock, which is completely application enforced, and will not block write operations to the table.

What is application enforced database lock?

When an application process starts up, we can acquire a lock on a resource and then release it, when the program exits. In this way, we have a guarantee that the program is not running concurrently, and at the same time not really block the application accessing the database.

The benefit of this is that the tables are never actually locked for writing. The main application will behave normally, and users will never notice anything happening in the background.

More details about Advisory locks is available in this Postgresql documentation.

Before Rails 6

By default, Active Record uses database features like prepared statements and advisory locks.

Rails uses an advisory lock when attempting to run a migration. This is to ensure that concurrent migrations don’t end up running on the same database.

This creates some issues when using transaction pooling which are used to reduce load on PostgreSQL servers by using shared connections.

The advisory locks are not shared across the transactions, resulting in issues when using connection poolers like PgBouncer in transaction pooling mode. On Rails end, this results in an ActiveRecord::ConcurrentMigrationError when trying to run database migrations when using PgBouncer.

In Rails 6

An easy fix to this situation is disabling the advisory lock themselves. This allows to have more finer control over whichever external connection pooler we might like to use like PgBouncer.

To disable advisory lock all we need to do is toggle the advisory_locks config in database.yml

production:
  adapter: postgresql
  advisory_locks: false

By default the advisory_locks are set to true and used in parts of application when running migrations.