Use Advisory Locks

You have a fleet of workers processing jobs from the same queue, and every once in a while two of them grab the same row and step on each other's toes. Row locks help, but what about a task that isn't tied to a row at all — like sending a daily digest or running a one-time migration? PostgreSQL advisory locks give you a lightweight, app-level coordination mechanism that doesn't bloat your schema or require a separate coordination service. In this lesson, you'll learn how to use advisory locks to coordinate distributed processes safely and efficiently.

The problem this lesson solves

Imagine you run a cron job on three servers, each firing the same maintenance script every midnight. Without coordination, all three might run the same vacuum or send the same email. Row-level locks don't help because there's no row to lock. You could use a pg_advisory_lock however, to ensure only one process runs at a time. Advisory locks solve a class of coordination problems that table locks can't: coordinating tasks that are not tied to a specific table or row.

Why care now? As your system grows from a single server to a cluster, you'll need a way to prevent duplicate work. Advisory locks are the PostgreSQL-native way to do this — no Redis, no Zookeeper, just SQL.

Core concept / mental model

Think of advisory locks as sticky notes on a shared whiteboard. Any process can write a note: "I'm working on task 42." Another process checks if the note exists before starting the same task. The whiteboard is the database, and the sticky note is a lock identified by a number (or text).

Advisory locks come in two flavors:

• Session-level: Held until you release it or the session ends. Like a sticky note stuck with strong tape — only you can remove it.
• Transaction-level: Held only for the duration of the current transaction. Like a sticky note that auto-destructs when the meeting ends.

There's also a shared vs. exclusive distinction:

• Exclusive lock: Only one session can hold it. Great for serializing tasks.
• Shared lock: Multiple sessions can hold it at once, but not concurrently with an exclusive lock. Useful for read-write coordination.

You can identify a lock by a bigint or two int4 keys, or by a text key (which is hashed to an int8 internally).

How it works step by step

Let's trace how advisory locks are used for coordination:

1. Acquire the lock: A process calls pg_advisory_lock(lock_key) (session-level) or pg_try_advisory_lock(lock_key) (non-blocking). If the lock is free, it's granted immediately. If not, the process waits (blocking) or gives up (try).
2. Do the work: Perform the task that must be serialized — sending emails, running migrations, etc.
3. Release the lock: Call pg_advisory_unlock(lock_key) or simply close the session. If you forget, the lock is released automatically when the session ends (or transaction ends for transaction-level locks).

For transaction-level locks, use pg_advisory_xact_lock(lock_key). It's released automatically at commit or rollback — no cleanup needed.

To avoid blocking forever, use pg_try_advisory_lock which returns true if acquired, false if not. This lets you implement a "if someone else is doing it, skip me" pattern.

Hands-on walkthrough

1. Basic session-level advisory lock

Open two connections (e.g., two psql sessions) and run:

-- Session 1
SELECT pg_advisory_lock(12345);
-- Returns void; lock acquired

-- Session 2
SELECT pg_try_advisory_lock(12345);
-- Returns false because Session 1 holds the lock

Now release in Session 1:

SELECT pg_advisory_unlock(12345);

Session 2 can now acquire:

SELECT pg_try_advisory_lock(12345);
-- Returns true

2. Transaction-level lock with auto-release

BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Do some work...
COMMIT; -- Lock released automatically

3. Non-blocking coordination pattern in a script

Here's a Python script using psycopg2 that only runs a job if no other process is already doing it:

import psycopg2
import time

conn = psycopg2.connect("dbname=mydb user=myuser")
cur = conn.cursor()

# Try to acquire a session-level advisory lock (non-blocking)
cur.execute("SELECT pg_try_advisory_lock(%s)", (42,))
acquired = cur.fetchone()[0]

if acquired:
    print("Lock acquired. Running job...")
    # Simulate work
    time.sleep(5)
    # Release the lock
    cur.execute("SELECT pg_advisory_unlock(%s)", (42,))
    conn.commit()
else:
    print("Another process is running this job. Skipping.")

cur.close()
conn.close()

Expected output when run twice concurrently:

Lock acquired. Running job...
Another process is running this job. Skipping.

Compare options / when to choose what

When to choose what?

• Use transaction-level locks when the work is atomic with the transaction — if the transaction fails, you don't want to keep the lock.
• Use session-level locks when you need to hold the lock across multiple transactions or while your app does complex logic.
• Use try locks when you don't want to wait — e.g., scheduled jobs that can skip if already running.
• Use row locks (SELECT FOR UPDATE) when you are modifying specific rows — they coordinate at the row level, not just a global task.

Troubleshooting & edge cases

Lock not released after transaction

If you use pg_advisory_lock inside a transaction and forget to unlock, the lock is held until the session ends, not the transaction. This can cause deadlocks or stale locks. Use pg_advisory_xact_lock for transaction-scoped work.

Lock key collision

If you use two int4 keys, make sure the combination is unique. Using text keys is convenient but be aware that text keys are hashed to int8 — very rare collisions are possible (same as any hash).

Blocking forever

pg_advisory_lock blocks indefinitely. If the lock holder disappears (e.g., network drop), the lock is released when the server detects the session end. But if the holder is stuck, you may need to cancel it with pg_terminate_backend(pid).

Viewing locks

To see who holds advisory locks, query the pg_locks view:

SELECT pid, locktype, objid, granted
FROM pg_locks
WHERE locktype = 'advisory';

What you learned & what's next

You now understand how to use advisory locks for coordination in PostgreSQL. You learned to:

• Explain the core idea behind advisory locks: app-level coordination without table locks.
• Acquire session-level and transaction-level locks, both blocking and non-blocking.
• Apply a non-blocking pattern to prevent duplicate job execution.
• Choose between advisory locks, row locks, and table locks based on your scenario.
• Troubleshoot common issues like lock leaks and blocking.

Next step: In the next lesson, you'll dive into transaction isolation levels and see how advisory locks complement SERIALIZABLE transactions to build robust concurrent systems. You'll also learn how to combine advisory locks with LISTEN/NOTIFY for real-time coordination.

Now go ahead and experiment: write a small script that uses pg_try_advisory_lock to ensure only one instance of a cron job runs. You'll appreciate the power of this simple SQL feature.