Advisory locks: locking ideas, not rows
Every lock so far protected a row or a table. But some things worth serializing aren't stored anywhere: "the nightly report is running", "this cron job", "migrations are in progress". PostgreSQL has a lock type for exactly this — "a means for creating locks that have application-defined meanings". You pick a number; PostgreSQL guarantees only one session holds it; what the number means is entirely your business.
Two deploy runners must not migrate the same database at once. They agree lock 42 means 'migration in progress'.
A> SELECT pg_advisory_lock(42);
pg_advisory_lock
------------------
(1 row)
B> SELECT pg_try_advisory_lock(42) AS got_it; -- an instant answer — no waiting
got_it
--------
f
(1 row)try-lock says no without waiting. A runner can also queue up for the lock:
B> SELECT pg_advisory_lock(42);
⏳ B is waiting for a lock…
A> SELECT pg_advisory_unlock(42) AS released;
released
----------
t
(1 row)
⏵ B resumes:
pg_advisory_lock
------------------
(1 row)Session-level locks ignore transaction boundaries entirely — COMMIT releases nothing.
A> BEGIN;
BEGIN
A> SELECT pg_advisory_lock(9);
pg_advisory_lock
------------------
(1 row)
A> COMMIT;
COMMIT
B> SELECT pg_try_advisory_lock(9) AS got_it;
got_it
--------
f
(1 row)
A> SELECT pg_advisory_unlock(9) AS released;
released
----------
t
(1 row)pg_advisory_xact_lock, by contrast, releases itself at COMMIT — there is no unlock function for it.
A> BEGIN;
BEGIN
A> SELECT pg_advisory_xact_lock(7);
pg_advisory_xact_lock
-----------------------
(1 row)
B> SELECT pg_try_advisory_lock(7) AS got_it;
got_it
--------
f
(1 row)
A> COMMIT;
COMMIT
B> SELECT pg_try_advisory_lock(7) AS got_it; -- freed by A's COMMIT alone
got_it
--------
t
(1 row)
B> SELECT pg_advisory_unlock(7);
pg_advisory_unlock
--------------------
t
(1 row)
B> SELECT pg_advisory_unlock(42);
pg_advisory_unlock
--------------------
t
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
pg_try_advisory_lock is the shape most jobs want: "someone else is already doing this — exit 0" beats a pile of cron runners queueing up to do the same work again.
Session locks vs. transaction locks
The scenario's second half is the part that pages people. A session-level lock, in the manual's words, "is held until explicitly released or the session ends" — COMMIT does nothing to it. It gets stranger: session-level advisory locks "do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback". pg_advisory_xact_lock restores the semantics you expect from everything else in this site: taken inside a transaction, released automatically at its end, no unlock function even exists.
These locks fit anywhere you need to serialize work rather than data: cron mutual exclusion, migration guards, one worker per tenant. Default to pg_advisory_xact_lock and let COMMIT clean up after you; reach for a session-level lock only when the protected work genuinely spans transactions, and then treat its explicit unlock as seriously as a finally block. Disconnecting releases everything a session holds, so a crashed holder can't strand a lock, and pg_try_advisory_lock stays the polite option for "skip if busy" work: it answers instead of queueing.
One sharp edge is worth a registry entry: the lock key is a global name. Numbers come in two separate spaces — a single 64-bit key, or a pair of 32-bit keys, which the manual notes "do not overlap" — but within whichever space you standardize on, two features that pick the same number will silently exclude each other. Keep a registry of who owns which key.