What are advisory locks in PostgreSQL and when would you use one?
Advisory locks are application-defined locks on an arbitrary number, managed by PostgreSQL but not tied to any table or row. Use them to make sure only one process runs a task, like a nightly report job, at a time.
Explain it simply
A "Meeting in progress" sign on a door has no power to stop anyone physically; it works because everyone agrees to respect it. Advisory locks are such signs. PostgreSQL stores and queues them for you, but only code that asks for the same lock number is affected. Tables and rows are not touched.
Example
-- Try to become the single runner of job 42
SELECT pg_try_advisory_lock(42); -- true if you got it, false if another session has it
-- ... run the nightly report ...
SELECT pg_advisory_unlock(42);
-- Transaction-scoped version: released automatically at COMMIT/ROLLBACK
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('nightly-report'));
-- work
COMMIT;Session-level locks stay until you unlock or disconnect, so with a connection pooler in transaction mode they can be left behind or taken on the wrong connection. Prefer the xact variants there.
What to say in the interview
"Advisory locks let an application lock an abstract key, for example to ensure one cron instance runs at a time. I use the transaction-scoped versions, especially behind PgBouncer, so locks are never leaked."
Common mistakes
- Using session-level advisory locks through a transaction-mode pooler.