Application-level locking with Postgres advisory locks

Originally published at https://medium.com/@codeflows/application-level-locking-with-postgres-advisory-locks-c29759eeb7a7

One useful feature of Postgres is advisory locking, which allows creating database locks using arbitrary keys which have special meaning for your application, but are not necessarily related to any database entity.

Let’s say we want to prevent users from making more than one simultaneous request to an API endpoint. We can achieve this by acquiring an advisory lock on the user’s id for the duration of an API request:

begin transaction;
-- Acquire lock for user with id 123456.
SELECT pg_advisory_xact_lock(123456);
-- Do other work in between
-- Lock is released here
commit;

The function pg_advisory_xact_lock(key) will block until it acquires the lock for the given key, and will release it automatically at the end of the transaction. Since you probably don’t want to wait indefinitely for a lock, be sure to set a lock timeout at the start of the transaction.

There’s also a non-blocking variant, pg_try_advisory_xact_lock(key), which immediately returns TRUE/FALSE indicating whether the lock was successfully acquired. It could be used in the example above to reject simultaneous requests immediately rather than waiting for previous ones to complete.

Small caveat here: the lock keys can only be (64-bit) integers. You can also pass in two (32-bit) integer values, which can be useful if you need to create locks based on different types of keys. For example, if you’d like to create locks based on both user ids and order ids, you can use the two-argument locking function to avoid id collisions:

-- Create locks for user ids under the "namespace" 10
SELECT pg_advisory_xact_lock(10, 123456);
-- Create locks for order ids under the "namespace" 20
SELECT pg_advisory_xact_lock(20, 123456);

The locking mechanism is quite rudimentary, but might come in handy if you’re already using Postgres.

References: