PostgreSQL Advisory Locks for Distributed Job Scheduling: Preventing Double-Execution Without a Queue

---
title: "PostgreSQL Advisory Locks: Distributed Job Safety Without a Queue"
published: true
description: "pg_try_advisory_lock prevents double-execution using only your existing database. Learn session vs transaction scopes, lock key hashing, and deadlock patterns for 10+ workers."
tags: postgresql, architecture, cloud, api
canonical_url: https://blog.mvpfactory.co/postgresql-advisory-locks-distributed-job-safety
---

What We Are Building

Let me show you a pattern I use in every project that needs distributed job coordination: PostgreSQL advisory locks. By the end of this tutorial, you will prevent double-execution across multiple workers — no Redis, no Sidekiq, no message queue infrastructure. Just your existing database doing more work.

We will cover:

• The difference between session and transaction-scoped locks (and why you almost always want the latter)
• A safe lock key hashing strategy that survives 50,000+ jobs per day
• The deadlock pattern that surfaces past 10 concurrent workers, and the canonical ordering fix

---

Prerequisites

• PostgreSQL 9.5+
• A jobs table with integer or UUID primary keys
• Basic understanding of SQL transactions

---

Step 1 — Understand the Lock Scope Choices

Here is the gotcha that will save you hours. PostgreSQL gives you two flavors of advisory locks:

Function	Scope	Released by
pg_advisory_lock(key)	Session	Explicit 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 self-clean on failure, require no explicit unlock logic, and compose naturally with your existing transaction management. Default to these.

Session-scoped locks are treacherous in pooled environments. If your pool (PgBouncer in transaction mode, for instance) recycles a crashed worker's connection, the lock vanishes before you intended. If it holds the connection open, the lock persists indefinitely. Neither outcome is what you want.

---

Step 2 — The Safe Acquisition Pattern

Here is the minimal setup to get this working:

-- 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;

pg_try_advisory_xact_lock is non-blocking. It returns true if it acquired the lock, false if another worker already holds it. Your worker skips cleanly — no waiting, no queuing.

---

Step 3 — Key Hashing That Does Not Bite You in Production

The docs do not mention this, but hashtext returns a 32-bit integer. With 10 concurrent workers running 50,000 jobs per day, the birthday paradox gives you roughly a 1-in-400,000 collision probability per acquisition. That adds up.

Use the two-part bigint variant instead, with a namespaced job type in the upper 32 bits:

-- Bit-shift namespace into upper 32 bits
SELECT pg_try_advisory_xact_lock(
  (job_type_id::bigint << 32) | (job_id::bigint & x'FFFFFFFF'::bigint)
);

If your job type is a string rather than an integer, derive a stable namespace from it:

-- Two-part key: string namespace + row ID
SELECT pg_try_advisory_xact_lock(
  ('x' || substr(md5('invoice_sync'), 1, 8))::bit(32)::int,
  job_id::int
);

The two-part bigint approach cuts collision probability by roughly a factor of 4 billion compared to a single hashtext call.

---

Gotchas

Deadlocks past 10 workers. When workers acquire multiple locks per job — say, 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). At high concurrency, this is expensive.

Fix it with 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;

Connection pool starvation. 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 others indefinitely. Size your pool to workers × max_locks_per_job + headroom.

Reaching for the blocking variant. Most teams instinctively use pg_advisory_lock (blocking) instead of pg_try_advisory_lock (non-blocking). The blocking variant under load causes worker pile-ups. Always prefer the non-blocking try variant and let workers skip and retry on their own schedule.

---

Conclusion

Advisory locks 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 rules before you ship this:

1. Default to pg_try_advisory_xact_lock over the session variant
2. Use two-part bigint keys with a namespaced job type in the upper 32 bits
3. Enforce canonical lock ordering across all workers acquiring multiple locks

Get connection pool sizing right too — workers × locks_per_job + headroom — or starvation will surface as symptoms that look nothing like the actual problem.

Further reading: PostgreSQL Advisory Locks documentation