PostgreSQL Deep dive: advisory locks

You need to prevent two workers from processing the same job. You could add a locked_by column to your table, UPDATE it when a worker picks up a row, and deal with the race conditions, the cleanup on crash, and the extra writes bloating your table. Or you could skip all of that and use a lock that exists only in memory, is automatically released when your session ends, and never touches your table at all.

That’s an advisory lock. PostgreSQL provides them as a cooperative locking mechanism. The database enforces the lock semantics, but your application decides what each lock protects. They’re one of the most underused features in PostgreSQL, and they solve a class of problems that MVCC, SELECT FOR UPDATE, and application-level flags all handle poorly.

What advisory locks actually are

Advisory locks are stored in PostgreSQL’s shared lock table alongside regular table and row locks. They use the same lock manager, the same conflict detection, and the same deadlock detector. The difference is that advisory locks aren’t tied to any database object. You identify them by a key, either a single bigint or a pair of integer values, and the database handles the rest.

The lock manager doesn’t know or care what your advisory lock “means.” It just knows that session A holds an exclusive lock on key 12345 and session B is waiting for it. Your application decides that key 12345 represents “job queue entry #12345” or “user account migration” or “rate limit bucket for API key 42.”

Session-level vs transaction-level

This is the distinction that trips people up.

Session-level advisory locks (pg_advisory_lock) survive transaction boundaries. Acquire one, roll back, and you still hold it. Acquire one in a function call, return, and the caller still holds it. The lock persists until you explicitly call pg_advisory_unlock, call pg_advisory_unlock_all, or your session ends (even if the client disconnects ungracefully; PostgreSQL cleans up).

This makes session-level locks useful for long-lived coordination: ensuring only one worker processes a queue, preventing concurrent cron jobs from overlapping, or holding a lease across multiple transactions in a connection pool.

-- Session-level: survives rollbacks and transaction boundaries
BEGIN;
SELECT pg_advisory_lock(12345);
ROLLBACK;  -- lock is STILL held

-- Explicit release needed
SELECT pg_advisory_unlock(12345);  -- returns true

Transaction-level advisory locks (pg_advisory_xact_lock) behave like regular PostgreSQL locks. They’re automatically released when the transaction ends, whether by COMMIT or ROLLBACK. There’s no explicit unlock function because the transaction boundary handles cleanup.

-- Transaction-level: released automatically on commit or rollback
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- do work
COMMIT;  -- lock released automatically

Transaction-level locks are safer for short-lived operations because they can’t accidentally persist past the scope of the work. If your transaction fails, the lock is released and another session can try. Session-level locks require disciplined cleanup code and a clear understanding of the session lifecycle.

The two levels can interfere with each other. A session-level lock on key X blocks a transaction-level lock request on the same key, and vice versa. If you’re mixing them in the same application, track which level you’re using for each key.

The two-argument key system

Every advisory lock function accepts either a single bigint or a pair of integer values:

-- Single bigint key
SELECT pg_advisory_lock(9007199254740993);

-- Two integer keys (composed internally into a bigint)
SELECT pg_advisory_lock(1, 42);

The two-argument form composes the values into a single lock identifier internally. The first argument typically represents a namespace (like a table OID or application identifier) and the second represents the specific entity within that namespace. This prevents key collisions when different parts of your application use advisory locks for different purposes.

A common pattern uses the table OID as the first argument and the primary key as the second:

SELECT pg_advisory_lock(
    'public.jobs'::regclass::int,  -- namespace: table OID
    $1                            -- entity: job ID
);

This is safe because regclass::int produces a stable integer for a given table, and the two-value space (4 billion × 4 billion) is large enough that collisions aren’t a practical concern.

If you use the single-argument form, be careful with key choice. Using an auto-incrementing integer ID is fine as long as you’re confident no other system in the same database uses the same integer values for a different purpose. The bigint space is 2⁶³, so in practice this rarely matters. But if you have multiple applications sharing a database, namespace your keys.

Exclusive vs shared modes

Like table locks, advisory locks support both exclusive and shared modes:

• pg_advisory_lock / pg_advisory_xact_lock — exclusive. Only one session can hold it. Other sessions (exclusive or shared) block.
• pg_advisory_lock_shared / pg_advisory_xact_lock_shared — shared. Multiple sessions can hold it simultaneously. Exclusive requests block until all shared holders release.

The shared mode is useful for read-heavy coordination: “multiple workers can read this resource, but block writers while readers are active.” Combined with exclusive locks for writes, you get a reader-writer lock pattern without any table-level locking overhead.

The non-blocking variants

Every advisory lock function has a pg_try_ counterpart that returns immediately instead of blocking:

-- Blocking: waits until the lock is available
SELECT pg_advisory_lock(12345);

-- Non-blocking: returns true if acquired, false if unavailable
SELECT pg_try_advisory_lock(12345);

This is the form you almost always want for job queues, rate limiting, and distributed coordination. Instead of having a worker block indefinitely waiting for a lock (which ties up a database connection, a connection pool slot, and potentially a transaction), you try to acquire the lock and move on if it’s not available.

-- Job queue worker pattern
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100;

-- For each candidate job, try to claim it
SELECT pg_try_advisory_xact_lock(
    'public.jobs'::regclass::int,
    $1  -- job ID
);
-- If true: this worker owns the job
-- If false: another worker got it first, skip

The pg_try_ variants pair naturally with transaction-level locks. If the lock attempt fails, you rollback (or just don’t commit anything) and try the next job. If the lock attempt succeeds and your work fails, the transaction rolls back and the lock releases automatically.

The LIMIT evaluation trap

The PostgreSQL documentation calls this out explicitly, and it causes real bugs in practice:

-- SAFE: WHERE id = (single row), lock acquired on exactly one row
SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345;

-- DANGEROUS: LIMIT may not be applied before the lock function executes
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100;

-- SAFE: lock only the rows selected by the subquery
SELECT pg_advisory_lock(q.id) FROM (
    SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q;

In the dangerous form, the planner may evaluate pg_advisory_lock(id) on rows that won’t be included in the final LIMIT result. Those locks are acquired by your session but your application doesn’t know about them. They’re dangling locks that won’t be released until the session ends. In a connection pool with long-lived sessions, this leaks locks silently.

The fix is always the same: materialize the rows you want in a subquery, then lock them in the outer query. The subquery produces a deterministic set of rows, and the outer query locks exactly those rows.

Deadlock ordering

Advisory locks participate in PostgreSQL’s deadlock detector. If session A holds lock X and waits for lock Y, while session B holds lock Y and waits for lock X, the deadlock detector will kill one of them.

But you can still create ordering problems even without a classic deadlock. If you always acquire locks in the same order (sorted by key), you avoid deadlocks entirely. If you acquire them in arbitrary order, you’re betting on the deadlock detector to save you, and it will, but at the cost of aborting one of the transactions and forcing a retry.

-- Deadlock-safe: always lock in key order
SELECT pg_advisory_xact_lock(least($1, $2));
SELECT pg_advisory_xact_lock(greatest($1, $2));

-- Potential deadlock: arbitrary order
SELECT pg_advisory_xact_lock($1);
SELECT pg_advisory_xact_lock($2);  -- might deadlock if another session
                               -- acquired them in reverse order

This matters most when you need to lock multiple resources atomically. Sort the keys first, then lock them sequentially. No deadlocks, no retries.

Shared memory limits

Advisory locks live in the same shared memory pool as regular locks. The pool size is determined by max_locks_per_transaction × max_connections. Each lock table entry occupies roughly a few hundred bytes of shared memory. The default max_locks_per_transaction is 64, and the default max_connections is 100, giving you roughly 6,400 lock entries, shared between all regular locks (table locks, row locks, transaction locks) and advisory locks.

If you exhaust the lock table, PostgreSQL cannot grant new locks at all. Not advisory locks, not table locks, not anything. The server returns “out of shared memory” errors and sessions start failing. This is recoverable (release some locks or restart), but it’s a bad situation.

In practice, advisory locks are small and short-lived for most workloads. The risk appears when you hold thousands of advisory locks simultaneously in long-running sessions, for example, locking every row in a batch job rather than locking a single batch identifier.

If you need to lock many items, lock a coarse-grained key instead:

-- Bad: one advisory lock per row
SELECT pg_advisory_xact_lock('public.items'::regclass::int, id)
FROM items WHERE batch_id = $1;

-- Better: one lock for the entire batch
SELECT pg_advisory_xact_lock(
    'public.batches'::regclass::int,
    $1  -- batch_id
);

pg_locks and monitoring

Advisory locks appear in pg_locks like any other lock. The distinguishing columns are:

• locktype = 'advisory'
• classid and objid — the two integer key components (or 0 and the bigint value for single-argument calls)
• objsubid = 1 (always, for advisory locks)

SELECT pid,
       locktype,
       classid,
       objid,
       mode,
       granted,
       waitstart
FROM pg_locks
WHERE locktype = 'advisory'
ORDER BY waitstart NULLS LAST;

This query shows you every advisory lock in the system, which sessions hold them, and which sessions are waiting. If you have stuck sessions or unexpected contention, this is where you look.

To find sessions holding the most advisory locks:

SELECT pid,
       count(*) AS advisory_lock_count,
       mode
FROM pg_locks
WHERE locktype = 'advisory'
  AND granted
GROUP BY pid, mode
ORDER BY advisory_lock_count DESC;

Advisory locks vs flag columns

The question comes up every time: why not just use a locked_at timestamp or a locked_by column?

Advisory locks have three concrete advantages:

Speed. Advisory locks are in-memory operations. No disk write, no WAL, no table bloat. A pg_advisory_lock call is roughly equivalent to a SELECT 1 in terms of I/O cost. Updating a flag column requires a row-level write, WAL logging, and potential index maintenance.

Automatic cleanup. When a session disconnects (gracefully or not), PostgreSQL releases all its advisory locks. A flag column stays set until something explicitly clears it. If your worker crashes mid-job, the advisory lock is released immediately. The flag column stays set until you add cleanup logic, typically a stale-lock timeout that adds latency and complexity.

No schema changes. Adding advisory locks requires no DDL. You don’t need to add columns, create indexes on those columns, or migrate existing data. You just call the function.

The tradeoff is that advisory locks don’t persist across server restarts (they’re in shared memory, not on disk). A locked_by column survives a crash and can prevent reprocessing after recovery. For most coordination patterns, the session-cleanup behavior of advisory locks is an advantage, not a limitation. But if you need crash-surviving locks, you need a flag column (or a durable lock service).

Real-world patterns

Job queue deduplication. Multiple workers polling the same queue can grab the same job. Advisory locks prevent this without changing the jobs table:

BEGIN;
SELECT pg_try_advisory_xact_lock('public.jobs'::regclass::int, $1);
-- If true, this worker owns job $1 for this transaction
UPDATE jobs SET status = 'processing' WHERE id = $1 AND status = 'pending';
COMMIT;

Rate limiting per tenant. Limit concurrent operations per customer:

SELECT pg_try_advisory_lock(42, customer_id);
-- If false: customer already has an operation running, reject

Preventing overlapping cron runs. If you run a periodic maintenance task and want to ensure only one instance runs at a time:

SELECT pg_try_advisory_lock(hashtext('daily_maintenance'));
-- If false: previous run still in progress, skip

Schema migration coordination. In a multi-instance deployment, ensure only one instance runs a migration:

SELECT pg_advisory_lock(hashtext('migration_' || $migration_name));
-- Blocks until any other instance running this migration finishes

Key takeaways

• Advisory locks are cooperative: the database enforces locking semantics, your application defines what each lock protects.
• Session-level locks (pg_advisory_lock) survive transactions and require explicit release. Transaction-level locks (pg_advisory_xact_lock) are released automatically on commit or rollback.
• Always use the pg_try_ variants for job queues and coordination to avoid blocking connections. Pair them with transaction-level locks for automatic cleanup.
• The LIMIT evaluation trap is real: always materialize candidate rows in a subquery before locking them.
• Lock in a consistent order (sorted by key) to avoid deadlocks. Don’t rely on the deadlock detector for multi-key locking patterns.
• Advisory locks share the same memory pool as regular locks. Holding thousands simultaneously can exhaust shared memory and block the entire server.
• They appear in pg_locks with locktype = 'advisory' — use this for monitoring and debugging contention.
• Advisory locks are faster and cleaner than flag columns for session-scoped coordination, but they don’t survive server restarts.

What’s next

Advisory locks solve application-level coordination. But what about the locks PostgreSQL acquires automatically? Table-level locks, row-level locks, and the conflict matrix that determines what blocks what. Understanding these is essential for diagnosing contention, deadlocks, and mysterious query stalls. Next time: the full PostgreSQL lock hierarchy.

---

Previous: Partial indexes, expression indexes, and covering indexes