PostgreSQL Advisory Locks with pg_advisory_lock

calendar Jun 8, 2026 / · 7 min read · pg_advisory_lock pg_locks advisory locks concurrency administration  ·
Share on: linkedin copy

PostgreSQL Advisory Locks with pg_advisory_lock

Two scheduled jobs fire at the same minute and both try to process the same nightly batch. Nothing in the data stops them — there is no single row to lock, because the work has not produced rows yet. Row and table locks are the wrong tool here. What you want is a named lock that the application agrees to respect, held for as long as the job runs. PostgreSQL provides this with advisory locks through pg_advisory_lock and its companion functions.

Purpose and Overview

Advisory locks are application-defined locks. The database tracks them and enforces mutual exclusion between sessions that request the same lock, but it attaches no meaning to them itself. They do not lock any table, row, or page. Your code decides what a given lock key represents — a batch job, a queue partition, a leader role — and every participant must agree to check the lock before doing the work.

Each advisory lock is identified by a key. The key is either a single 64-bit integer or a pair of 32-bit integers. The two-integer form is convenient when you want one integer to act as a namespace and the other as an object identifier.

Advisory locks come in two scopes. Session-level locks are held until you explicitly release them or the session ends. Transaction-level locks are held until the surrounding transaction commits or rolls back, after which PostgreSQL releases them automatically. Both scopes offer exclusive and shared modes, and both offer blocking and non-blocking acquisition.

Sample Code

 1-- Acquire a session-level exclusive advisory lock on key 12345 (blocks until granted)
 2SELECT pg_advisory_lock(12345);
 3
 4-- Non-blocking attempt: returns true if acquired, false if already held elsewhere
 5SELECT pg_try_advisory_lock(12345);
 6
 7-- Release the session-level lock
 8SELECT pg_advisory_unlock(12345);
 9
10-- Inspect every advisory lock currently held in the cluster
11SELECT pid, mode, granted, classid, objid
12FROM pg_locks
13WHERE locktype = 'advisory';

Notes: Advisory lock functions exist in all supported PostgreSQL versions. The single-key form takes one bigint; the two-key form takes two int4 values. Adjust the key numbers to a convention your application controls.

Code Breakdown

The functions above are the core of the advisory lock interface, plus the query that lets a DBA see what is held.

Key Argument Forms

pg_advisory_lock(12345) uses the single 64-bit key. There is also a two-argument form, pg_advisory_lock(1, 12345), where the first integer acts as a namespace and the second as an identifier. The single-key and two-key forms occupy separate lock spaces, so key 12345 and key (0, 12345) are not the same lock.

Blocking vs Non-Blocking

pg_advisory_lock(12345) blocks: if another session holds the lock, the call waits until it is free. pg_try_advisory_lock(12345) does not block. It returns true if the lock was acquired and false if it is already held. The non-blocking form is what you use when a second job should simply exit rather than queue up behind the first.

Releasing

pg_advisory_unlock(12345) releases one session-level lock and returns true on success. Session-level locks stack: if you acquire the same lock twice, you must unlock it twice. pg_advisory_unlock_all() releases every advisory lock the session holds in one call.

Inspecting in pg_locks

The pg_locks view exposes advisory locks with locktype = 'advisory'. The key is encoded across the classid and objid columns, and the mode column shows ExclusiveLock or ShareLock. This query is the audit step that tells you which backend (pid) holds which lock.

Key Advisory Lock Functions

Session-Level vs Transaction-Level

pg_advisory_lock() is session-level and lives until you unlock it or disconnect. pg_advisory_xact_lock() is transaction-level and is released automatically when the transaction ends. The transaction-level variant cannot be unlocked manually, which is a feature: it guarantees cleanup even if your code forgets or an error fires.

Shared vs Exclusive

The plain functions take an exclusive lock — only one holder at a time. The _shared variants (pg_advisory_lock_shared, pg_advisory_xact_lock_shared) allow many concurrent shared holders but block any exclusive request. Use shared mode when many readers may run together but a writer needs the field to itself.

The Try Variants

pg_try_advisory_lock, pg_try_advisory_xact_lock, and their _shared forms all return a boolean immediately instead of waiting. They are the building block for "run only if no one else is running" logic.

Releasing in Bulk

pg_advisory_unlock_all() drops every session-level advisory lock the current session holds. It is a safe reset to call at the end of a job or in a connection-cleanup routine.

Practical Applications

Advisory locks are the right tool whenever coordination happens above the row level.

Serialize a Scheduled Job

A cron job or a worker process calls pg_try_advisory_lock on a fixed key at startup. If it gets the lock, it runs. If it does not, another copy is already running, so it exits quietly. This prevents overlapping runs without a separate lock table.

Application-Level Leader Election

Several application nodes each try the same advisory lock. The one that wins becomes the leader and performs singleton work — for example, draining a queue. If the leader crashes, its session ends, the lock is released, and another node takes over.

Prevent Concurrent Migration Runs

A deployment pipeline that might run twice in parallel can wrap its migration step in a transaction-level advisory lock. The second run blocks until the first commits, so migrations never interleave.

Coordinate Work-Queue Consumers

When many consumers pull from a shared queue, a per-item advisory lock (using the two-key namespace form) lets a consumer claim an item without an UPDATE ... SKIP LOCKED pattern, releasing it automatically at transaction end. The namespace integer identifies the queue and the second integer identifies the item, so two queues never collide even when their item ids overlap.

Rate-Limit a Shared Resource

A shared-mode advisory lock (pg_advisory_lock_shared) lets many readers proceed together while a maintenance task waiting on the exclusive lock blocks until they finish. This gives a simple reader/writer gate around a resource that an exclusive lock alone would serialize too aggressively.

Version Compatibility

Advisory locks have existed since PostgreSQL 8.2. The transaction-level variants (pg_advisory_xact_lock and friends) and the shared-mode functions were added in PostgreSQL 9.1, so any modern server supports the full set.

One behavior worth remembering: session-level advisory locks are not affected by SAVEPOINT rollback. A lock acquired inside a subtransaction that later rolls back stays held, because session-level locks are tied to the session, not the transaction. Transaction-level advisory locks, by contrast, follow the transaction and are released on rollback. This difference is the usual reason to prefer the _xact_ functions unless you specifically need a lock to outlive its transaction.

Best Practices

  • Prefer transaction-level locks for automatic cleanup — pg_advisory_xact_lock releases on commit or rollback, so a crash or an unhandled error never leaks a lock.
  • Always pair lock and unlock for session-level locks — a session-level lock that is never released stays held until the connection closes, which can stall other jobs indefinitely.
  • Adopt a key namespace convention — use the two-integer form, or hashtext('job-name'), so different subsystems never collide on the same numeric key by accident.
  • Watch advisory locks during deadlock analysis — they appear in pg_locks with locktype = 'advisory' and can participate in deadlocks just like other locks.
  • Do not hold advisory locks across idle time — a session that grabs a lock and then sits idle in a transaction blocks every waiter; keep the protected critical section short.

References

Posts in this series