PostgreSQL advisory locks for distributed job safety
TL;DR
Skip the queue infrastructure. PostgreSQL advisory locks give you distributed mutual exclusion using only your existing database. pg_try_advisory_lock is non-blocking, works at session or transaction scope, and scales cleanly — until you hit around 10 concurrent workers, at which point key collision and deadlock patterns emerge that will silently corrupt your job semantics. This post covers both.
The problem: exactly-once in a multi-worker world
Consider a compliance-critical system — say, one that must report every transaction to an external authority within a narrow time window. Miss a report, duplicate a report, or deliver one out of order, and you have a correctness problem with real consequences.
The naive solution is a message queue with a single consumer. But queues add operational surface: another service to deploy, monitor, and tune. For many teams, especially those already running PostgreSQL, advisory locks offer a simpler path to the same guarantee.
Here’s what most teams get wrong: they reach for pg_advisory_lock (blocking) when they should be using pg_try_advisory_lock (non-blocking), and they never think carefully about lock scope until production starts misfiring.
Session vs. transaction scope: pick one, understand both
PostgreSQL offers two flavors of advisory locks:
| Function | Scope | Released by |
|---|---|---|
pg_advisory_lock(key) | Session | Explicit pg_advisory_unlock or connection close |
pg_advisory_xact_lock(key) | Transaction | Automatic on COMMIT / ROLLBACK |
pg_try_advisory_lock(key) | Session | Explicit unlock or connection close |
pg_try_advisory_xact_lock(key) | Transaction | Automatic on COMMIT / ROLLBACK |
Transaction-scoped locks are almost always what you want for job scheduling. They self-clean on failure, require no explicit unlock logic, and compose naturally with your existing transaction management.
Session-scoped locks are powerful but treacherous. If a worker crashes mid-job and its connection is recycled by a pool (PgBouncer in transaction mode, for instance), the lock is gone before you intended. Worse, if the pool holds the connection open, the lock persists indefinitely.
-- Safe pattern: transaction-scoped, non-blocking
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('job:invoice_sync:' || job_id::text))
INTO acquired;
IF NOT acquired THEN
ROLLBACK;
-- Another worker owns this job — skip it
RETURN;
END IF;
-- Do the work inside the transaction
UPDATE jobs SET status = 'processing', started_at = NOW() WHERE id = job_id;
COMMIT;Key hashing: where most implementations break
pg_advisory_lock takes a 64-bit integer (or two 32-bit integers). Your job IDs are likely UUIDs or strings. The obvious approach — hashtext(job_id) — hides a real problem: hash collisions.
hashtext returns a 32-bit integer. With 10 concurrent workers each holding distinct locks, the birthday paradox gives you roughly a 1-in-400,000 chance of collision per lock acquisition. Sounds safe until you’re running 50,000 jobs per day.
Better strategy: use the bigint variant with a two-part key.
-- Two-part key: job type namespace + row ID
SELECT pg_try_advisory_xact_lock(
('x' || substr(md5('invoice_sync'), 1, 8))::bit(32)::int, -- namespace
job_id::int -- specific job
);Or if your IDs are already integers, encode the job type directly:
-- Bit-shift namespace into upper 32 bits
SELECT pg_try_advisory_xact_lock(
(job_type_id::bigint << 32) | (job_id::bigint & x'FFFFFFFF'::bigint)
);The two-part bigint approach cuts collision probability by a factor of roughly 4 billion compared to a single hashtext call. That’s the difference between “essentially never” and “definitely, eventually.”
Deadlock patterns past 10 workers
When you scale past 10 concurrent workers acquiring multiple locks per job (e.g., locking both a job record and a dependent resource), you create deadlock conditions. PostgreSQL detects these and raises an exception — but the detection cycle defaults to 1 second (deadlock_timeout), which is expensive at high concurrency.
The fix is canonical lock ordering: always acquire locks in the same sorted order across all workers.
-- Workers must always lock lower ID first
FOR lock_key IN SELECT key FROM job_locks WHERE job_id = $1 ORDER BY key ASC LOOP
PERFORM pg_try_advisory_xact_lock(lock_key);
END LOOP;Also watch for this failure mode in connection pools: if your pool has fewer connections than workers, lock acquisition queues behind connection acquisition. A worker holding a lock while waiting for a second connection will block other workers indefinitely. Size your pool to workers * max_locks_per_job + headroom.
Conclusion
Advisory locks are one of those PostgreSQL features that’s been quietly available for years while teams paid for Redis, ran Sidekiq, or bent message queues into job schedulers. In my experience building production systems, they eliminate an entire category of queue-related operational complexity — no additional services, no at-least-once delivery concerns, no message visibility timeouts to tune.
Three things worth getting right before you ship this: default to pg_try_advisory_xact_lock over the session variant; use two-part bigint keys with a namespaced job type in the upper 32 bits; and enforce canonical lock ordering across all workers acquiring multiple locks. Get the connection pool sizing right too — workers × locks_per_job + headroom — or lock acquisition starvation will surface as symptoms that look nothing like the actual problem.