Advisory Locks for Application-Side Coordination

1. The difference from classic row and table locks

A classic lock in a relational database is always tied to a concrete data object: a row, a page, or an entire table. These locks arise implicitly through normal DML statements like UPDATE or explicitly through SELECT FOR UPDATE, and their purpose is to coordinate concurrent changes to the same data so the consistency of stored values is preserved.

An advisory lock, by contrast, is entirely decoupled from concrete data. It consists merely of a numeric or textual identifier freely chosen by the application, and carries no semantic meaning for the database itself. The database only guarantees that at any point in time at most one process can hold an exclusive advisory lock with the same identifier, regardless of what that identifier actually represents.

2. The core principle: an arbitrary identifier as a coordination point

Because an advisory lock has no connection to concrete table rows, it can be used for any kind of coordination problem the application itself defines: preventing duplicate batch processing, serializing a specific business operation across multiple server instances, or a simple distributed mutex for a critical initialization routine that must only run once.

The identifier itself is usually derived from a stable, meaningful value, for instance by hashing a job name or a tenant ID into an integer, so the same identifier is consistently reproducible on every call. Two different processes wanting to coordinate the same logical operation only need to agree on the same derivation logic for the identifier, not on a shared data schema.

-- Request an advisory lock via a numeric identifier (syntax illustrative)
SELECT pg_try_advisory_lock(hashtext('nightly-report-job'));

-- Alternative in other systems via a named lock
SELECT GET_LOCK('nightly-report-job', 10);
-- second parameter: maximum wait time in seconds

3. Practical use case: cron jobs that must not run twice

A classic problem in distributed systems is a scheduled job configured on multiple, redundantly deployed servers, but that should only actually run once per scheduled time, for instance a nightly report export or a billing batch. Without coordination, every server would start the job independently, leading to duplicate processing, inconsistent results, or duplicate emails being sent out.

An advisory lock solves this problem elegantly, without needing a separate coordination infrastructure such as a dedicated distributed lock service: every server tries at the start of the job to acquire the same advisory lock. Only the server that actually gets the lock runs the job, all others detect the failure immediately and terminate, without blocking or having to implement their own error handling for a duplicate run.

-- Non-blocking attempt to acquire the job lock
DO $$
BEGIN
    IF pg_try_advisory_lock(hashtext('billing-export-2026-08-08')) THEN
        -- Only this process runs the export
        PERFORM run_billing_export();
        PERFORM pg_advisory_unlock(hashtext('billing-export-2026-08-08'));
    ELSE
        RAISE NOTICE 'Export is already running on another instance';
    END IF;
END $$;

4. Session-level locks: manual release and connection binding

A session-level advisory lock stays in place until it is either explicitly released via an unlock command or the database connection that acquired it is closed. This binding to the connection, not to a single transaction, makes session-level locks suitable for coordination tasks that should hold across multiple transactions, for instance locking an entire multi-step batch operation that internally performs several commits.

The most important operational point here: if the connection is interrupted by a crash or an uncleanly terminated application without an explicit unlock, the database automatically releases the lock as soon as it detects the connection has ended. This behavior prevents a permanently stuck lock, but requires connection-pooling layers to be configured correctly so a connection is not mistakenly reused as active while the original process has already terminated.

5. Transaction-level locks: automatic release at the end of the transaction

A transaction-level advisory lock is automatically released as soon as the current transaction commits or rolls back, regardless of whether it was explicitly unlocked. This variant fits coordination problems tied exactly to the lifetime of a single transaction, for instance serializing a critical section within a single business operation, without the risk of forgetting a manual unlock call.

The advantage over session-level locks lies in the lower error proneness: a forgotten or exception-skipped unlock call can, with session-level locks, lead to a permanently blocked coordination point, while a transaction-level lock is reliably cleaned up by the guaranteed end of the transaction anyway, even if the application does not handle an error case cleanly.

-- Transaction-level advisory lock: automatic release on COMMIT/ROLLBACK
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('reindex-catalog'));
-- critical section
UPDATE catalog_state SET reindex_running = true;
-- ... processing ...
COMMIT;
-- the lock is released automatically at the latest here

6. Using blocking vs. non-blocking variants correctly

Most database systems offer both a blocking variant that waits until the lock becomes available, and a non-blocking variant that returns immediately with a success or failure value. For the cron-job use case, the non-blocking variant is almost always the right choice, because a process that does not get the lock should simply skip the job rather than wait idly and tie up resources.

Blocking variants, by contrast, fit cases where multiple processes actually should pass through the same critical section one after another, for instance a sequential processing pipeline with multiple workers. If the non-blocking variant is used here by mistake, workers abort immediately under contention instead of correctly waiting in line, undermining the intended serialization.

7. Common mistakes when using advisory locks in practice

A widespread mistake is identifier collision: if the same numeric range is used for different, logically unrelated coordination purposes, for instance because two teams use the same hashing approach without coordinating, collisions can occur where a lock falsely appears held even though it was meant for an entirely different purpose. A clear namespace, for instance a prefix before hashing, reliably avoids this problem.

A second common mistake is confusing session-level and transaction-level locks in environments with connection pooling: if a connection is returned to the pool after use without an explicit unlock of a session-level lock, the lock remains active from the database's point of view, even though the application logically considers it finished, until a new request happens to reuse the same pooled connection and unknowingly keeps holding the lock.

8. Observing and debugging active advisory locks in production

Because advisory locks have no connection to concrete table rows, they do not show up in the same diagnostic tools as classic row locks. Most systems offer a dedicated system view or function for this that lists currently held advisory locks along with their identifier and the holding session, which should be the first stop when troubleshooting a supposedly stuck coordination point.

For production systems, it is additionally advisable to use a timeout or a maximum wait time on blocking variants, so a stuck lock does not lead to an indefinitely waiting process chain. Combined with logging that records every acquisition and release of an advisory lock with a timestamp, a stuck coordination point can reliably be traced back to its originating process afterward.

-- List currently held advisory locks (syntax illustrative)
SELECT locktype, objid, pid, granted
FROM pg_locks
WHERE locktype = 'advisory';

9. When advisory locks are the wrong choice

Advisory locks are not a substitute for classic row locks or unique constraints when it is actually about the consistency of stored data. If the goal is to prevent two processes from conflictingly changing the same record at the same time, a regular data-level locking concept, such as optimistic locking with a version column or SELECT FOR UPDATE, is the more correct and more robust solution, because the database then enforces those guarantees directly on the affected data.

Advisory locks fit exactly where no concrete data object exists that could be locked, but coordination still needs reliable, distributed infrastructure, as is the case with purely process- or job-related coordination. This clear boundary drawn by use case, not by technical convenience, prevents advisory locks from being misused as generic replacement locking for problems that are actually data-related.

10. Summary

11. FAQ: Advisory Locks: Key Facts at a Glance