Use Advisory Locks for Application-Level Locking

Why This Matters

Advisory locks are application-level locks that don't block table access. They're perfect for coordinating work across multiple application instances, preventing duplicate processing, or ensuring only one process handles a task at a time.

Unlike table locks, advisory locks:

• Don't block other queries from accessing your tables
• Work across different tables and even different databases
• Are automatically released when the transaction ends
• Can be session-level or transaction-level

The Problem

Without advisory locks, multiple application instances can process the same work simultaneously:

-- Multiple workers can process the same job
BEGIN;
SELECT * FROM jobs WHERE status = 'pending' LIMIT 1;
-- Worker 1 and Worker 2 both see the same job
UPDATE jobs SET status = 'processing' WHERE id = 123;
-- Both workers process job 123 simultaneously!
COMMIT;

This causes duplicate processing, wasted resources, and potential data corruption.

The Solution

Use pg_try_advisory_lock() for non-blocking locks or pg_advisory_lock() for blocking locks:

-- Use advisory lock to coordinate job processing
BEGIN;

-- Try to acquire lock for job ID 123
SELECT pg_try_advisory_lock(123);
-- Returns true if lock acquired, false if already locked

SELECT * FROM jobs WHERE id = 123 AND status = 'pending';
-- Only one worker can process this job

UPDATE jobs SET status = 'processing' WHERE id = 123;
-- Process the job...

COMMIT;
-- Lock is automatically released on commit

For blocking locks that wait until available:

-- pg_advisory_lock waits until lock is available
SELECT pg_advisory_lock(123);
-- Blocks until lock is free, then acquires it

-- Always release explicitly in long-running transactions
SELECT pg_advisory_unlock(123);

Best Practices

• Use pg_try_advisory_lock() for non-blocking: Returns immediately with true/false. Perfect for queue workers that should skip locked items.
• Use pg_advisory_lock() for blocking: Waits until lock is available. Use when you must acquire the lock.
• Use transaction-level locks: Advisory locks are automatically released when the transaction commits or rolls back. No manual cleanup needed.
• Choose unique lock IDs: Use a hash of your resource identifier (e.g., job ID, user ID) to ensure consistent locking across instances.
• For session-level locks: Use pg_advisory_lock_session() if you need the lock to persist across transactions.

Example: Preventing Duplicate Email Sends

Use advisory locks to ensure emails are only sent once, even with multiple workers:

-- Function to safely send email with advisory lock
CREATE OR REPLACE FUNCTION send_email_safely(email_id BIGINT)
RETURNS BOOLEAN AS $$
DECLARE
  lock_acquired BOOLEAN;
BEGIN
  -- Try to acquire lock using email ID
  SELECT pg_try_advisory_lock(email_id) INTO lock_acquired;
  
  IF NOT lock_acquired THEN
    -- Another process is already sending this email
    RETURN FALSE;
  END IF;
  
  -- Check if already sent
  IF (SELECT sent_at FROM emails WHERE id = email_id) IS NOT NULL THEN
    RETURN FALSE;
  END IF;
  
  -- Send email and mark as sent
  UPDATE emails SET sent_at = NOW() WHERE id = email_id;
  -- ... actual email sending logic ...
  
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;