Advisory Locks for Concurrency Control

If you're using Postgres, advisory locks are a useful tool for concurrency control. They can be used in situations where coordinating row-level locks is cumbersome, e.g. at read committed isolation level, or whenever you need a distributed lock.

Basics

The simplest way to use an advisory lock is with the pg_advisory_lock function.

SELECT pg_advisory_lock(208889121, 0);

The lock here is on the 32-bit integer key pair (208889121, 0). (There's a separate keyspace for a single 64-bit integer.) Once the lock is acquired, any other session that tries to use it will wait until it's released.

SELECT pg_advisory_unlock(208889121, 0);

It's important to understand that even if a transaction rolls back or the application process raises an exception, these session-level locks will live on until they're explicitly released or the Postgres session ends.

If you'd rather use a non-blocking query, pg_try_advisory_lock returns immediately indicating whether the lock was acquired or not.

# SELECT pg_try_advisory_lock(208889121, 0);
 pg_try_advisory_lock
----------------------
 t
(1 row)

Transaction-level advisory locks

pg_advisory_lock gives you a session-level advisory lock. If you're worried about needing to manually release locks you can use transaction-level advisory locks with the _xact function variants. These locks are released when the transaction ends—they cannot be manually released. And you'll need to make sure you're in an open transaction, of course.

SELECT pg_advisory_xact_lock(208889121, 0);

For a non-blocking query:

SELECT pg_try_advisory_xact_lock(208889121, 0);

The advantage of transaction-level advisory locks is that the lock is released on commit/rollback even if your process dies or you have a bug. The tradeoff, of course, is you need to be able to run the critical path of the code entirely within an open transaction.

Serial execution in Sidekiq

Advisory locks can be used to implement additional flavors of concurrency control in a framework like Sidekiq. Let's say you have a job class MyJob and you want to ensure that jobs with the same arguments can only run one at a time. In other words, many jobs are running concurrently but jobs with the same args must run serially.

If MyJob takes some time to run and you need to be able to enqueue another instance with the same args during that time, Sidekiq Enterprise's unique_until: :start and unique_until: :success won't work. With unique_until: :start a second job could be enqueued and picked up as soon as the first job starts, and with unique_until: :success you wouldn't be able to enqueue a second job while the first is running.

Back to advisory locks. Simply acquire an advisory lock for the duration of the job and you're good to go. You'll need to use a stable key or keys derived from the job args. One way to do it would be to create a string from the args and hash the string to a 32-bit integer (e.g. using Zlib.crc32 similar to WithAdvisoryLock's approach) and then just use 0 as the second integer in the key pair.

If the job fails to acquire the lock, raise an error to leverage Sidekiq's retry functionality. You can tune how long to wait for the lock, if at all, by configuring lock_timeout for the blocking functions and retrying in application code for the _try variants. You can also still use unique_until: :start to prevent duplicate jobs from being enqueued.

This approach introduces some complexity and latency depending on the retry intervals but I'm not aware of a better way to do it with Sidekiq. (If you're using Kafka you could use single-threaded consumers, although you'd need to make sure your topic partitions are well designed.)

Shared advisory locks

Shared advisory locks enable even more complex use cases. Let's say MyJob takes two args a and b and has the following requirements:

• The lock key is only dependent on a
• b is used in some branching or other business logic
• There's a business constraint that if b is true you can run many jobs for the same a concurrently, but if b is false then it must be the only job running for a given a

In this case you can acquire a shared lock when b is true (pg_try_advisory_xact_lock_shared) and an exclusive lock when b is false (pg_try_advisory_xact_lock). Shared locks block exclusive locks and exclusive locks block shared locks, and you can have as many shared locks as you want.