An advisory lock lets an application coordinate work using an identifier that need not correspond to a table row. For example, workers can agree that only one connection may refresh a particular report at a time. PostgreSQL manages ownership and contention, while the application defines what the identifier means and which work requires it.

When it breaks

The key is either one bigint or two integer values; there is no advisory-lock overload accepting a text name. Those two key spaces are distinct. If an application hashes names, it must define stable encoding and handle possible collisions; different computations can also give the same resource different keys. A documented integer namespace is often easier to reason about than an undocumented hash convention.

The lock is cooperative. A client that ignores it can still update the supposed protected rows. It does not replace a uniqueness constraint, transaction, or authorization check. Session locks survive rollback and require matching unlocks, or session termination. Transaction locks end with their transaction. In autocommit, a transaction lock obtained by one statement has already been released before a later statement starts, leaving that later work unprotected.

Check it quickly

Use two fresh psql -X -v ON_ERROR_STOP=1 sessions, A and B, connected to one disposable PostgreSQL 18 database. Reserve the example key pairs for this exercise and execute blocks in order. A creates a counter:

CREATE SCHEMA advisory_demo;
CREATE TABLE advisory_demo.counter (id integer PRIMARY KEY,n integer);
INSERT INTO advisory_demo.counter VALUES (1,0);

A tests an explicitly typed text key. The expected SQLSTATE is 42883, indicating no matching function:

\set ON_ERROR_STOP off
SELECT pg_advisory_lock('job-name'::text);
\echo text_key_state :SQLSTATE
\set ON_ERROR_STOP on

In A, acquire the same session lock twice inside a transaction, then roll back:

BEGIN;
SELECT pg_advisory_lock(8610,2);
SELECT pg_advisory_lock(8610,2);
ROLLBACK;

B cannot acquire that lock, but can update the counter without participating in the advisory protocol:

SELECT pg_try_advisory_lock(8610,2);
UPDATE advisory_demo.counter SET n=n+1 WHERE id=1 RETURNING n;

Expect false, followed by one. Both observations matter: rollback did not release A’s session lock, and the advisory lock did not prevent B’s ordinary write. In A, release one acquisition:

SELECT pg_advisory_unlock(8610,2);

The unlock returns true, but B still cannot acquire the lock:

SELECT pg_try_advisory_lock(8610,2);

A must release its second acquisition too:

SELECT pg_advisory_unlock(8610,2);

B can now obtain the session lock, and explicitly releases its own acquisition:

SELECT pg_try_advisory_lock(8610,2);
SELECT pg_advisory_unlock(8610,2);

Both calls return true. Successful repeated acquisitions are counted, so a single successful unlock does not prove that a session no longer owns the resource. In A, begin a transaction and acquire a different key using the transaction-scoped function:

BEGIN;
SELECT pg_advisory_xact_lock(8610,3);

B’s nonblocking attempt returns false while A’s transaction remains open:

SELECT pg_try_advisory_xact_lock(8610,3);

A rolls back:

ROLLBACK;

B can now obtain the transaction lock in autocommit:

SELECT pg_try_advisory_xact_lock(8610,3);

Immediately afterward, A can obtain it too:

SELECT pg_try_advisory_xact_lock(8610,3);

These two true results are sequential, not simultaneous ownership. Each statement’s implicit transaction ended and released the lock before the next statement began. Neither connection now protects a future critical section. Finally, A checks that the counter is one and removes the fixture:

SELECT n FROM advisory_demo.counter WHERE id=1;
DROP SCHEMA advisory_demo CASCADE;

An unsuccessful try-lock call adds no ownership to release. Only a successful acquisition contributes to the locks held by that session or transaction.

Match ownership to the operation

For database work that fits one transaction, acquire the transaction lock inside an explicit transaction, then read, decide, and write before ending it. A nonblocking acquisition returning false means the client did not obtain ownership; it must skip or reschedule the protected work, not continue as though it succeeded. Blocking variants can wait, so their timeout and retry policies belong to the operation budget.

Use a session lock only when its longer lifetime is required, and keep acquisition, protected work, and release on the same physical connection. Returning that connection to a pool can leave a lock owned by a backend that serves unrelated callers; transaction pooling is especially unsuitable for session ownership assumptions. A terminated backend releases session locks automatically, although detecting a lost network connection can take time. This mechanism also supplies no durable record that a job completed: a subsequent worker must inspect committed state before deciding whether repeating the work is appropriate.

Sources