Advisory Locks in Postgres: A Simple Tool That Deserves Respect

The first time advisory locks saved me was a billing job. Two workers picked up the same account after a deploy, both decided an invoice was missing, and both were about to create one. There was no single row that naturally represented "billing account 418 for May is being processed right now."

A row lock would have worked only if we invented a row to lock. A unique constraint would have caught the duplicate too late. The cleanest fix was a transaction-scoped advisory lock around the account-month pair.

That is the right mental model: advisory locks are for coordination the database cannot infer from normal row or table locks. They are not magic mutexes. PostgreSQL will hold them for you, show them in pg_locks, and participate in deadlock detection, but it will not know whether your application chose the right key.

The framework: lock the business invariant

I do not start with "should we use advisory locks?" I start with the invariant. What must not happen twice?

• One tenant billing run at a time.
• One migration worker per customer database.
• One cache rebuild for a report key.
• One background repair job for a logical object.

If the invariant maps to an existing row, use row locks. If it maps to a uniqueness rule, use a unique constraint. If it maps to a logical object that does not have a clean row to lock, advisory locks become reasonable.

Prefer transaction-scoped locks first

Transaction-scoped advisory locks release automatically at commit or rollback. That property prevents a whole class of production mistakes.

BEGIN;

SELECT pg_try_advisory_xact_lock(9001, $1::int) AS got_lock;

-- If got_lock is false, another worker owns this logical job.
-- If true, do the protected work inside this transaction.

COMMIT;

The two-integer form is useful because it lets you reserve a namespace. In this example, 9001 can mean "billing job" and the second key can be the account id. The exact values matter less than making the key scheme explicit and stable.

I reach for session-scoped locks only when the protected work truly spans transactions. That is rare. Session locks survive commit and rollback, which means they also survive application mistakes until the session ends or unlocks them.

Connection pools make session locks risky

Session locks and connection pools are an easy way to create a problem that looks impossible from application logs. Your request finishes. The code path forgets to unlock. The connection returns to the pool. A later request inherits a connection that still holds a lock it knows nothing about.

If you use session-scoped locks, wrap them like a scarce resource:

SELECT pg_try_advisory_lock(9001, 418);
-- protected work
SELECT pg_advisory_unlock(9001, 418);

Then test the failure path. Exceptions, timeouts, and canceled requests are where leaked locks come from.

Never hide advisory locks in casual SELECTs

Advisory lock functions are functions. If you call them in a query that also filters, limits, or sorts, be careful about evaluation order. The safe pattern is to choose the exact key first, then lock it.

WITH candidate AS (
  SELECT id
  FROM jobs
  WHERE state = 'ready'
  ORDER BY priority DESC, id
  LIMIT 1
)
SELECT pg_try_advisory_xact_lock(9001, id::int)
FROM candidate;

Do not scatter lock acquisition inside broad scans and hope the planner evaluates things in the order you imagined.

Deadlocks are still possible

Advisory locks are outside the normal data model, but they are still locks. If one worker locks customer A then customer B, and another locks B then A, you have created the same deadlock shape as row locks.

The rule is boring and effective: if a code path needs more than one advisory lock, acquire keys in a deterministic order. If you cannot explain the order, you have not designed the lock.

How I debug advisory locks

SELECT
  pid,
  locktype,
  classid,
  objid,
  mode,
  granted,
  waitstart
FROM pg_locks
WHERE locktype = 'advisory'
ORDER BY granted, waitstart NULLS LAST;

That query tells you whether the database sees the lock. It does not tell you what the key means. Keep a small key registry in code or docs. Six months later, classid=9001 should still mean something to the person on call.

When not to use advisory locks

• Do not use them to replace unique constraints. Constraints are the source of truth.
• Do not use them when SELECT ... FOR UPDATE naturally protects the row.
• Do not use them for long human workflows. Locks are for short coordination windows.
• Do not use them without a timeout or fallback behavior.

The pragmatic default is simple: transaction-scoped advisory locks are excellent for short, logical coordination. Session-scoped locks deserve extra scrutiny. The lock itself is easy. Owning the lifecycle is the real work.