PostgreSQL advisory locks for application-level locking

Need application-level mutual exclusion without external tools like Redis, for operations like cron jobs or batch processing.

Use PostgreSQL advisory locks:

-- Session-level lock (held until session ends or explicitly released)
SELECT pg_advisory_lock(12345);      -- Blocks until acquired
SELECT pg_try_advisory_lock(12345);   -- Returns false if can't acquire
SELECT pg_advisory_unlock(12345);     -- Release

-- Transaction-level lock (released at end of transaction)
SELECT pg_advisory_xact_lock(12345);  -- Released on COMMIT/ROLLBACK

# Python pattern for job locking
def run_with_lock(conn, lock_id, fn):
    cur = conn.cursor()
    cur.execute('SELECT pg_try_advisory_lock(%s)', (lock_id,))
    acquired = cur.fetchone()[0]
    if not acquired:
        print('Another instance is running')
        return False
    try:
        fn()
        return True
    finally:
        cur.execute('SELECT pg_advisory_unlock(%s)', (lock_id,))

# Use consistent lock IDs
LOCK_DAILY_REPORT = 1001
LOCK_EMAIL_QUEUE = 1002
LOCK_CLEANUP_JOB = 1003

run_with_lock(conn, LOCK_DAILY_REPORT, generate_report)

Benefits over row locks:

• No table needed
• No deadlock risk (single lock)
• No lock contention on data tables
• Visible via pg_locks system view

Advisory locks use PostgreSQL's lock manager without locking actual rows, making them ideal for application-level coordination without external dependencies.

Applications needing coordination between processes using the same PostgreSQL database