Using PostgreSQL Advisory Locks for Application Coordination

When two or more workers that use PostgreSQL need to run the same job or update the same named resource, an advisory lock lets them coordinate. The application identifies the lock with a key. PostgreSQL makes workers wait when they request a lock that conflicts with one already held, or lets them check immediately whether the lock is available.

Use advisory locks when code needs to ensure that only one worker at a time handles a named task or resource, not to protect specific table rows. PostgreSQL does not tie an advisory lock to a row or require every code path to take it. For row-specific protection, use PostgreSQL’s row locks and transaction rules instead, as described in its documentation on explicit locking. The choice comes down to what needs protection: a named task or resource, or particular rows.

Choose advisory locks for application-defined work

Use an advisory lock when the work does not involve particular rows, such as making sure only one worker runs a maintenance job or rebuilds a customer’s summary at a time. Every worker or code path doing the same work must use the same key. PostgreSQL accepts either one 64-bit integer or a pair of 32-bit integers as the key; the two key formats use separate, nonoverlapping spaces, so applications should not treat them as interchangeable encodings. See PostgreSQL’s advisory lock functions.

Choose keys so unrelated jobs do not accidentally share one, and document the choices where each code path doing the work can follow the same convention. A shared lock allows multiple workers to hold the lock together while keeping an exclusive holder out. An exclusive lock allows only one holder at a time.

For a short stretch of database work that must not overlap, use a transaction-level lock. The lock lasts for the transaction, so it is usually the simpler choice. PostgreSQL releases it automatically when the transaction commits or rolls back:

BEGIN;

SELECT pg_advisory_xact_lock(42, 7);

UPDATE customer_summaries
SET refreshed_at = now()
WHERE customer_id = 7;

COMMIT;

The two-part key identifies the work the transactions coordinate. If another transaction holds a conflicting lock for that key, PostgreSQL waits. To skip instead of waiting, call pg_try_advisory_xact_lock(42, 7): it returns true when the transaction gets the lock and false when it does not. Keep the protected database work in the same transaction, because commit or rollback releases the lock.

Use session locks for work that spans transactions

A singleton job is work that only one worker should run at a time, even when the job uses several database transactions. A session-level lock can span those transactions, but it belongs to one PostgreSQL session, meaning one server connection. The worker must keep using that same connection while it holds the lock.

On a dedicated connection, a worker can try to get the lock, run the job only if PostgreSQL grants it, and release the lock during cleanup:

SELECT pg_try_advisory_lock(7001);

-- If the result is true, run the job on this same connection.

SELECT pg_advisory_unlock(7001);

The application should run the unlock in a finally-style cleanup path so errors during the job do not leave the lock held. PostgreSQL also releases a session lock when the session ends. A transaction rollback alone does not release it, and acquiring the same session lock repeatedly requires a matching release for each successful acquisition.

A connection pool reuses database connections, so its settings affect this choice. PgBouncer documents session-level advisory locks as incompatible with transaction pooling, which assigns a server connection only for a transaction. Use a pooling mode that preserves the session, or choose a transaction-level lock if its shorter lifetime covers the work.

Keep locks separate from data integrity

An advisory lock coordinates work only when every code path that needs the lock follows the same convention. If one worker takes the lock but another updates the data without taking it, PostgreSQL does not block that update because of the advisory lock. Protect rules about individual rows with row locks, such as SELECT ... FOR UPDATE. Use constraints and transaction logic to enforce rules on the data.

Keep the work protected by each lock short. While a transaction or session holds a lock, other workers that need a conflicting lock must wait or skip. If workers need several locks, require them to acquire the locks in the same order. Otherwise, transactions can wait for locks held by each other, creating a deadlock. PostgreSQL detects deadlocks and aborts one transaction, so code that runs transactions should handle retryable failures where appropriate. You can inspect current locks through PostgreSQL’s pg_locks view.

An advisory lock also does not guarantee that a job runs exactly once. It coordinates workers while the lock is held, but it does not make actions outside the database atomic with database work. If a worker stops partway through a job, the job still needs a safe retry or recovery strategy.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com