patternsqlpostgresqlModerate
Advisory locks for distributed mutual exclusion without extra infrastructure
Viewed 0 times
advisory lockpg_try_advisory_lockdistributed lockmutexjob deduplicationhashtext
Problem
Application-level job queues, cron runners, and report generators running on multiple servers can execute the same work concurrently without a coordination mechanism, causing duplicate processing.
Solution
Use PostgreSQL advisory locks as a lightweight distributed mutex:
-- Session-level lock (held until released or session ends):
SELECT pg_try_advisory_lock(hashtext('nightly_report_job'));
-- Returns true if lock acquired, false if already held elsewhere
-- Transaction-level lock (auto-released at commit/rollback):
SELECT pg_try_advisory_xact_lock(42);
-- Release manually:
SELECT pg_advisory_unlock(hashtext('nightly_report_job'));
-- Application pattern:
DO $$
BEGIN
IF pg_try_advisory_lock(1234) THEN
-- do the work
PERFORM run_nightly_job();
PERFORM pg_advisory_unlock(1234);
ELSE
RAISE NOTICE 'Another instance is running';
END IF;
END;
$$;
-- Session-level lock (held until released or session ends):
SELECT pg_try_advisory_lock(hashtext('nightly_report_job'));
-- Returns true if lock acquired, false if already held elsewhere
-- Transaction-level lock (auto-released at commit/rollback):
SELECT pg_try_advisory_xact_lock(42);
-- Release manually:
SELECT pg_advisory_unlock(hashtext('nightly_report_job'));
-- Application pattern:
DO $$
BEGIN
IF pg_try_advisory_lock(1234) THEN
-- do the work
PERFORM run_nightly_job();
PERFORM pg_advisory_unlock(1234);
ELSE
RAISE NOTICE 'Another instance is running';
END IF;
END;
$$;
Why
Advisory locks are stored in shared memory and have virtually zero overhead. They are not tied to any table or row, so they work for coordinating any application-level operation.
Gotchas
- Session-level locks survive transaction rollback; always release explicitly or use xact variants
- Lock IDs are global per cluster; use namespacing with two bigint arguments or hashtext() to avoid collisions
- pg_try_advisory_lock() is non-blocking; pg_advisory_lock() blocks indefinitely
- Connection poolers that reuse sessions can leak unreleased advisory locks
Code Snippets
Inspect active advisory locks in pg_locks
-- List all currently held advisory locks:
SELECT pid, locktype, classid, objid, granted
FROM pg_locks
WHERE locktype = 'advisory';Context
Multi-process or multi-server applications sharing a single PostgreSQL database
Revisions (0)
No revisions yet.