Advisory Locks in Postgresql

PostgreSQL provides various lock modes to control concurrent access to data in tables. Advisory locks provide a convenient way to obtain a lock from PostgreSQL that is completely application enforced, and will not block writes to the table.

Imagine you have a scheduled task that runs in the background, mutates the database and sends information off to another 3rd party service. We can use PostgreSQL Advisory Locks to guarantee that the program cannot cause any unexpected behavior if ran multiple times concurrently. Concurrency is fun, but surprise concurrency can be brutal! In our case it’s beneficial to have a guarantee that the program in question can never run concurrently.

Application Enforced Locks

Throughout this post we will explore Postgres advisory locks, which are application enforced database locks. Advisory locks can be acquired at the session level and at the transaction level and release as expected when a session ends or a transaction completes. So what does it mean to have an application enforced database lock? Essentially, when your process starts up, you can acquire a lock through Postgres and then release it when the program exits. In this way, we have a guarantee that the program cannot be running concurrently, as it will not be able to acquire the lock at startup, in which case you can just exit.

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

Here is the syntax for obtaining the lock:

SELECT pg_try_advisory_lock(1);

Now, there’s a few things happening in that statement so lets examine it.


So how would we go about using this in Ruby?

def obtained_lock? connection.select_value(‘select pg_try_advisory_lock(1);’) == ‘t’ end

We can grab our ActiveRecord connection and call #select_value in order get back a 't' or 'f' value. A simple equality check let’s us know whether or not we have obtained the lock, and if we haven’t we can choose to bail and exit the program.

class LockObtainer
  def lock_it_up
    exclusive do
      # do important stuff here


  def exclusive
    if obtained_lock?

  def obtained_lock?
    connection.select_value('select pg_try_advisory_lock(1);') == 't'

  def release_lock
    connection.execute 'select pg_advisory_unlock(1);'

  def connection

Additional Information

There are a few interesting things about Advisory Locks: