postgres-impl-advisory-locks

PostgreSQL advisory locks coordinate scheduled jobs by attempting a lock with pg_try_advisory_lock, executing the job if successful, or skipping it if already locked by another instance. This ensures a job runs at most once.

Building a distributed mutex in PostgreSQL uses advisory locks to serialize critical operations across application instances when you cannot lock a table row, preventing duplicate processing for external API calls or cron jobs.

You need PostgreSQL 15, 16, or 17 for advisory lock functionality. These versions support session-level and transaction-level advisory locks required for distributed mutexes and job coordination.

Session-level advisory locks persist until explicitly released or the session disconnects, while transaction-level advisory locks automatically release when the transaction commits or rolls back, providing different scopes for mutex coordination.

Use PostgreSQL advisory locks instead of row locking when you need to serialize a critical operation that cannot be tied to a table row, such as ensuring a cron job runs only once across multiple instances or preventing duplicate external API processing.