Advisory locks

Transactions, isolation and locking Senior Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

Two calls in the same session: what's printed?

SQL · PostgreSQL 17

SELECT pg_try_advisory_lock(42);
SELECT pg_try_advisory_lock(42);
SELECT pg_advisory_unlock(42), pg_advisory_unlock(42),
       pg_advisory_unlock(42);

Optionst, t, then t|t|ft, f, then t|f|ft, t, then t|f|fAn error on the second call

Hint 1 of 1

Is the lock re-entrant within one session?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A t, t, then t|t|f

Correct. Session advisory locks are re-entrant: taken twice, released twice; the third unlock has nothing to release.

Session-level advisory locks count how many times the session took them. Two acquisitions need two unlocks to release, and the third unlock returns false because nothing is held.

Why not the others?

B t, f, then t|f|f. Another session would get f, but the same session can take it again.
C t, t, then t|f|f. Each acquisition needs its own unlock.
D An error on the second call. try_ functions never wait or raise; they return a boolean.

Result on PostgreSQL 17 (psql)

pg_advisory_unlock	pg_advisory_unlock	pg_advisory_unlock
t	t	f

(1 row)

30-second answer

Advisory locks are locks on an application-chosen number rather than a row. The database enforces them but gives them no meaning, so you use them to serialise work that isn't one row: run a migration once, make sure only one instance runs a cron job, or rebuild one customer's cache at a time. pg_advisory_xact_lock(key) releases at transaction end; pg_try_advisory_lock returns false instead of waiting.

Practise saying it · 30 s. No recording, just a timer

When would you use an advisory lock?

Say your answer out loud, then check which of these points you covered:

Lock on an app-chosen key
Single runner for jobs and migrations
Prefer xact-level
Pooler pitfall

Deep dive

Session-level locks (pg_advisory_lock) survive commit and must be unlocked; with a connection pooler in transaction mode they can leak to another client.

Hash a string to a bigint key, for example with hashtext('job:nightly').

They live in shared memory; don't take millions of them.

For leader election across services, a lease with expiry and fencing tokens is safer than a lock tied to one connection.

Common mistakes

Using session-level advisory locks behind PgBouncer transaction pooling.
Forgetting that a crashed connection releases the lock, so a 'single runner' can start twice in sequence.
Picking keys that collide between unrelated features.

Senior take & follow-ups

Use them for coordination that isn't a row, prefer the transaction-scoped form, and know the pooler pitfall.

Follow-up questions

Think of your answer first, then open the hint.

How do you run a cron job once across 5 instances?
Show hint
Each tries pg_try_advisory_xact_lock; only the winner runs, and it records the run (say, a last_run row) in the same transaction, so a late instance can't repeat it once the lock is released.
What releases a transaction-level advisory lock?
Show hint
COMMIT or ROLLBACK.

Suggest a fix if something here is wrong or out of date.