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: falseBy default the advisory_locks are set to true
and used in parts of application
when running migrations.
