PostgreSQL Advisory Locks: Coordinating Distributed Workers Without a Queue

Introduction

Every system that runs background work eventually runs into the same bug: the same unit of work starts twice. The nightly reconciliation script fires on two application servers at once. A queue worker picks up a job while a cron job is already doing the same thing. A retry overlaps the original attempt. In development nobody notices. In production it means duplicated invoices, double-charged customers, or a report that contradicts itself.

The usual reflex is to install Redis and reach for a distributed lock library. That works, but it adds an entire stateful dependency to your infrastructure, along with a new class of failures: lock expiry, clock skew, and split-brain when the lock store is unreachable. For a surprisingly large number of problems, you do not need any of that. If your workers already talk to PostgreSQL, the database can be the coordination point — and it has a dedicated, well-tested primitive for exactly this job: PostgreSQL advisory locks.

An advisory lock is a lock that PostgreSQL manages on your behalf, but which has no connection to any row or table. You give it a key, the database guarantees that only one session in the cluster holds that key at a time, and nobody else has to know about it. There is no extra schema, no polling loop, and no TTL to tune. The trick is understanding the two variants — session-level and transaction-level — because choosing the wrong one is where most production incidents with advisory locks come from.

This guide is written for working developers who want to ship this correctly. We will cover how advisory locks actually behave inside PostgreSQL, why they vanish on failover, how to design lock keys that never collide by accident, and how to implement the pattern in raw SQL, Laravel, and Node.js. We will also cover the operational side: what shows up in pg_locks, how to debug a stuck lock, when advisory locks are the wrong tool, and how to avoid the connection-pooling trap that quietly breaks session-level locks in many containerized deployments.

By the end you should be able to decide in under a minute whether a given coordination problem is an advisory lock problem, a SKIP LOCKED problem, or genuinely a job for an external lock service.

Table of Contents

• Introduction
• Core Concepts
• How Advisory Locks Work Inside PostgreSQL
• Step-by-Step Guide
• Real-World Examples
• Production Code Examples
• Comparison Table
• Best Practices
• Common Mistakes
• Performance Tips
• Security Considerations
• Deployment Notes
• Debugging Tips
• FAQ
• Conclusion

Core Concepts

Before writing a single line of code, you need four ideas straight in your head. Everything else in this article is a consequence of them.

1. An advisory lock is a named mutex in shared memory

PostgreSQL runs a lock manager whose state lives in shared memory and is protected by the postmaster. Regular locks — the ones you never think about — are attached to tables, rows, or transaction IDs. Advisory locks are attached to nothing. You supply a numeric key, and the lock manager treats that key as a resource name. Two sessions asking for the same key conflict; two sessions asking for different keys do not.

That is the whole contract. The database does not care what the key means. It does not verify that the thing you are protecting exists. It is a convention shared between your processes: same key, same critical section.

2. There are two scopes: session and transaction

Every advisory lock function comes in two flavours, and this distinction is the single most important thing to get right.

• Session-level (pg_advisory_lock, pg_try_advisory_lock, pg_advisory_lock_shared): the lock stays held until you explicitly release it or the database connection closes. It is not tied to the transaction. If your transaction commits, rolls back, or fails, the lock is still yours.
• Transaction-level (pg_advisory_xact_lock, pg_try_advisory_xact_lock, pg_advisory_xact_lock_shared): the lock is released automatically at the end of the current transaction, whether it commits or rolls back. You cannot release it early.

In practice: session locks are for long-running work where the unit of protection is a connection lifetime. Transaction locks are for protecting a short critical section and are overwhelmingly safer in modern pooled architectures.

3. Keys come in two forms: one bigint or two int4

You can key a lock either with a single bigint (a 64-bit value) or with a pair of int4 values (two 32-bit values). PostgreSQL treats these as separate key spaces: a lock taken with a single bigint key and a lock taken with a two-integer key never conflict with each other. In pg_locks, the objsubid column tells you which form was used — 1 for the single-key form, 2 for the two-key form.

The two-integer form is often nicer for humans: you can dedicate one integer as a fixed namespace (for example 42 for "billing jobs") and derive the second integer from the resource identifier. That makes it obvious in pg_locks which subsystem owns a lock, and it dramatically reduces the chance of accidental collisions between unrelated features.

4. Lock modes: exclusive and shared

Most advisory lock functions take an exclusive lock, exactly like a write mutex. The _shared variants take a shared lock, so several sessions can hold the same key simultaneously while blocking anyone who wants it exclusively. Shared locks are useful for reader/writer style coordination — for example, letting multiple reporting jobs run in parallel while a schema migration waits for all of them to finish. Most use cases only need exclusive locks, but knowing the shared variants exist saves you from inventing an approximation later.

How Advisory Locks Work Inside PostgreSQL

Advisory locks are ordinary entries in the lock manager, which means they inherit all the good behaviour of PostgreSQL locking and a few limitations that come from shared memory.

They are acquired and released within the normal locking protocol, so deadlock detection works on them. If two sessions take two advisory keys in opposite order, PostgreSQL will notice the cycle and abort one of the transactions with a deadlock error, exactly as it would for row locks. This is a real benefit: a home-grown Redis lock has no deadlock detector, and a badly ordered pair of locks there becomes a support ticket.

Advisory locks are not WAL-logged. They exist only in the memory of the server instance that granted them. This has a consequence that surprises people at the worst possible time: advisory locks do not replicate to standbys. When a primary fails over, every advisory lock held on the old primary disappears. If a worker was holding the lock and the worker survives the failover, the worker believes it is still protected but the database no longer knows about the lock at all.

They are also re-entrant within a session. If the same connection calls pg_advisory_lock twice with the same key, the second call succeeds immediately, and you then need two matching unlocks to fully release it. This is a classic source of leaked locks: a helper function takes a lock, and the caller has already taken it earlier in the request.

Finally, lock slots are shared memory. The number of addressable lock entries is derived from max_locks_per_transaction multiplied by the number of connections and prepared transactions. Taking thousands of distinct advisory keys in one transaction is not free, and there is a hard ceiling. For a handful of named jobs per subsystem this is irrelevant; for per-tenant locking across thousands of tenants at boot, it matters.

The visibility story is excellent. Every advisory lock appears in the pg_locks system view with locktype = 'advisory', along with the holding PID, the mode, and whether it was granted or is waiting. That means an on-call engineer can answer "who is holding the report lock?" with one query instead of installing a lock-inspection CLI.

Step-by-Step Guide

Here is the workflow that keeps advisory locks boring in production. It is short, but each step exists because skipping it causes a specific failure.

Step 1: Decide what the lock protects

Write down the critical section in one sentence. "Only one process may rebuild the search index at a time." "Only one billing reconciliation may run per calendar day per tenant." If you cannot state it that simply, the lock is probably doing too much, or the real problem is idempotency rather than mutual exclusion.

Step 2: Pick the scope deliberately

Ask: should the lock survive a transaction boundary? If the protected work happens entirely inside a transaction, use pg_try_advisory_xact_lock. If the protected work spans multiple transactions, external HTTP calls, or long file processing, you need a session lock held by a dedicated connection for the duration.

Default to transaction scope. Choose session scope only when you can also guarantee that a single physical connection is used for the whole operation.

Step 3: Design the key namespace

Use the two-integer form and reserve the first integer as a subsystem constant. Then derive the second integer from the resource. For text identifiers, PostgreSQL's hashtext() returns an int4, which fits the two-key form directly. For the single-key form, hashtextextended(text, seed) returns a bigint and lets you change the seed if you ever need a fresh key space.

-- Namespace 4242 reserved for billing jobs.SELECT pg_try_advisory_xact_lock(4242, hashtext('billing:reconcile:tenant-9042'));

Note that hashing is not a substitute for uniqueness; collisions are possible and are theoretically more likely with hashtext than with a 64-bit hash. For genuinely independent jobs, a collision merely means two unrelated jobs occasionally serialise against each other. That is a performance annoyance, not a correctness bug — acceptable. It would not be acceptable if a collision could make two concurrent operations write the same data.

Step 4: Use try-variants, not blocking variants

pg_advisory_lock waits indefinitely (subject to lock_timeout). In a web request or a queue worker, waiting forever is almost always wrong: the second worker should back off and let the first finish. Use pg_try_advisory_lock or pg_try_advisory_xact_lock, which return a boolean immediately. Treat false as "someone else has this, exit cleanly", not as an error.

Step 5: Release in every code path

With transaction locks, the database does this for you. With session locks, you must unlock in a finally block or language-level equivalent, on every path including exceptions. On top of that, always set a lock_timeout or statement_timeout on the connection so a pathological wait cannot pin a connection forever.

Step 6: Verify with pg_locks

Run your new code, then inspect the lock from a second session. If you cannot see your lock in pg_locks while the work is running, you are not actually protecting anything, and it is much better to discover that in testing than after a duplicate charge.

SELECT pid, mode, granted, classid, objid, objsubidFROM pg_locksWHERE locktype = 'advisory'ORDER BY pid;

Step 7: Add a failure-mode test

Write an integration test that starts two workers against the same database and asserts that the second one exits without doing the work. Then write a second test that kills the first worker mid-flight and asserts that the second one proceeds on the next cycle. Both cases are where advisory locks actually earn their place.

Real-World Examples

Example 1: Guarding a scheduled sweep across many servers

A Laravel application runs on four app servers, each with a scheduler. The daily revenue summary is expensive and must run once. Wrapping the job body in a transaction lock keyed on the report name means four schedulers fire, one wins, three exit in milliseconds. No Redis, no leader election, no new infrastructure.

Example 2: Deduplicating webhook processing

A payment provider retries webhooks aggressively. Two identical deliveries can arrive within the same second on different pods. If the handler is INSERT ... ON CONFLICT DO NOTHING on an event ID, that already solves the duplicate row — but if the handler also calls a third-party API or sends email, you want the whole handler serialised. A transaction-level advisory lock keyed on the event ID makes the second delivery wait rather than duplicate the side effect. The lock is released automatically at commit, so there is no cleanup path to forget.

Example 3: One index rebuild at a time

A search index rebuild takes fifteen minutes and runs from an admin button as well as a nightly job. The work happens inside a job process, not a transaction, and uses an external HTTP API. Here a session lock held on a dedicated connection is correct: acquire with pg_try_advisory_lock, do the fifteen minutes of work, then unlock in a finally block. If the process crashes, the connection drops and the lock is released by the server automatically.

Example 4: Serialising per-tenant exports

Customers can trigger a CSV export of their data. Two clicks should not produce two parallel exports competing for the same database read bandwidth. Key the lock as (namespace, tenant_id) using the two-integer form, use a try-variant, and return "an export is already running" to the second request. Because the key includes the tenant, different customers never block each other.

Production Code Examples

Raw SQL: the safe default

This is the pattern to reach for first. It runs entirely inside one transaction, releases automatically, and returns a clean boolean.

BEGIN;-- Acquire without waiting. Returns true or false immediately.SELECT pg_try_advisory_xact_lock(4242, hashtext('billing:reconcile:daily')) AS acquired \gset-- In application code, branch on :acquired and ROLLBACK if false.-- The lock disappears at COMMIT or ROLLBACK either way.

Session lock with guaranteed release

For long-running work outside a transaction, hold this on a dedicated connection and never share it with pooled request traffic.

-- Dedicated connection, session-level lockSELECT pg_try_advisory_lock(4243, 9001) AS acquired;-- ... long-running work that spans multiple transactions ...-- Always release, and check the result:SELECT pg_advisory_unlock(4243, 9001);-- Returns true if this session held the lock, false otherwise.

A false from pg_advisory_unlock means the session did not hold that lock at that moment. It is not an error you need to throw, but it is worth logging, because it usually indicates a double-unlock bug or a lock that was never acquired.

Laravel: transaction-scoped lock inside a job

<?phpnamespace App\Jobs;use Illuminate\Bus\Queueable;use Illuminate\Contracts\Queue\ShouldQueue;use Illuminate\Foundation\Bus\Dispatchable;use Illuminate\Queue\InteractsWithQueue;use Illuminate\Queue\SerializesModels;use Illuminate\Support\Facades\DB;use Illuminate\Support\Facades\Log;class ReconcileDailyBilling implements ShouldQueue{    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;    /** Reserved advisory-lock namespace for billing. */    private const LOCK_NAMESPACE = 4242;    public function handle(): void    {        $ns = self::LOCK_NAMESPACE;        $resource = 'billing:reconcile:daily';        $acquired = DB::transaction(function () use ($ns, $resource) {            // hashtext() is an immutable PostgreSQL function.            $row = DB::selectOne(                'SELECT pg_try_advisory_xact_lock(?, hashtext(?)) AS acquired',                [$ns, $resource]            );            if (! $row->acquired) {                return false;            }            // Critical section: runs inside the same transaction,            // so the lock is held until commit or rollback.            $this->performReconciliation();            return true;        }, 3); // 3 attempts for deadlock/serialization retries.        if (! $acquired) {            Log::info('Billing reconciliation skipped; another worker holds the lock.');            $this->release();        }    }    private function performReconciliation(): void    {        // Bounded, transactional work belongs here.    }}

Two details matter. First, the lock and the work share one transaction, so the lock vanishes the instant the work commits. Second, the critical section must be short: this transaction holds a database connection and an advisory lock for its whole duration, so keep the expensive external calls outside it. If the reconciliation takes minutes of HTTP traffic, use a session lock on a dedicated connection instead.

Node.js with node-postgres

import pg from 'pg';const { Client } = pg;const NAMESPACE = 4242;/** * Run work under a transaction-scoped advisory lock. * The lock is released by PostgreSQL on COMMIT or ROLLBACK. */export async function withAdvisoryLock(resource, work) {  const client = new Client({ connectionString: process.env.DATABASE_URL });  await client.connect();  try {    await client.query('BEGIN');    const { rows } = await client.query(      'SELECT pg_try_advisory_xact_lock($1, hashtext($2)) AS acquired',      [NAMESPACE, resource]    );    if (!rows[0].acquired) {      await client.query('ROLLBACK');      return { acquired: false, result: null };    }    const result = await work(client);    await client.query('COMMIT');    return { acquired: true, result };  } catch (err) {    await client.query('ROLLBACK');    throw err;  } finally {    await client.end();  }}

A single Client is used deliberately. With pool.query() you cannot guarantee that the BEGIN, the lock acquisition, and the COMMIT land on the same backend connection — and advisory locks are per connection. Always check out one client or one connection for the whole transaction.

Session lock with an explicit safety timeout

<?phpuse Illuminate\Support\Facades\DB;function runWithSessionLock(string $resource, callable $work): void{    $ns = 4243;    $acquired = DB::selectOne(        'SELECT pg_try_advisory_lock(?, hashtext(?)) AS acquired',        [$ns, $resource]    )->acquired;    if (! $acquired) {        return;    }    try {        $work();    } finally {        DB::selectOne(            'SELECT pg_advisory_unlock(?, hashtext(?)) AS released',            [$ns, $resource]        );    }}

Caution: this helper is only correct if the connection that ran the acquire is the same one that runs the release. In a long-lived process with Laravel's database manager this normally holds, but if the connection is dropped and reconnected between the two calls — for example after a network blip — the session lock is silently gone. For critical paths, verify liveness or prefer a transaction lock over a long-lived session.

Comparison Table

Advisory locks are one of four tools developers reach for in this space, and they win in a specific niche: no new infrastructure, strong mutual exclusion, and automatic cleanup on connection loss. Here is how they compare for the common "only one worker should run this" problem.

The important distinction in that table is between mutual exclusion and work distribution. Advisory locks say "only one of you may do this." SKIP LOCKED says "take different work and don't queue behind each other." If you find yourself using an advisory lock per job row to spread load, stop — that is a queue, and PostgreSQL already has the right primitive for it.

Best Practices

• Prefer transaction-level locks. They cannot leak. Session locks should be the exception, justified by work that genuinely cannot live inside one transaction.
• Always use the try-variant in request and job paths. Blocking indefinitely inside a worker is how you turn a slow job into an outage.
• Reserve a namespace integer per subsystem. A constant like 4242 for billing and 4243 for indexing makes locks legible in pg_locks and prevents two features from colliding on the same derived hash.
• Keep the critical section minimal. Acquire late, release early. Do not hold a lock while sending email, waiting on a third-party API, or sleeping.
• Set lock_timeout on worker connections. Even with try-variants, other statements in the same transaction can wait on locks. A timeout converts a hang into an error you can retry.
• Log every failed acquisition at a low level. A steady volume of acquired = false is useful signal — it tells you a job is running more often than you thought or that a lock is stuck.
• Treat the lock as an optimisation, not the source of truth. The real guarantee comes from idempotent writes and unique constraints. The lock only reduces wasted work and duplicate side effects.
• Never share a session-locked connection with unrelated queries. A dedicated connection is part of the design, not an implementation detail.

Common Mistakes

Holding a session lock across a pooled connection

This is the most damaging mistake, and it hides well. If you run PgBouncer in transaction pooling mode, a session-level advisory lock is acquired on one backend and your next statement may run on another. The lock is still held by the earlier backend, invisible to you, until that connection is recycled. The practical rule: in transaction pooling, use only transaction-level advisory locks. If you truly need session locks, route those specific operations through a session-pooled connection.

Forgetting that the lock is not replicated

Advisory locks live in the memory of one server. After a failover, the new primary has no idea a lock was ever held. Work that assumes "if I hold the lock, nobody else can be doing this" breaks at the exact moment the system is least stable. Design the work to be safe if two processes run concurrently after a failover — the lock makes the overlap rare, not impossible.

Using advisory locks as a queue

Taking one lock per row to distribute work across workers means one lock slot per row, serialised acquisition attempts, and no way to know how much work is pending. Use ORDER BY ... FOR UPDATE SKIP LOCKED with a LIMIT instead. It uses a single row-lock mechanism, distributes work naturally, and lets you inspect the backlog with a plain SELECT.

Assuming the same key can be acquired once

A session can re-acquire its own advisory lock. The second acquisition succeeds, and the lock is only fully released after a matching number of unlocks. Wrapping lock acquisition in a reusable helper and calling it twice in one request leaves a lock behind that outlives the request's intent.

Blocking acquisition on the request path

pg_advisory_lock with no timeout will wait as long as it takes. In an HTTP request this consumes a PHP or Node process, a database connection, and a queue slot. Users see timeouts while the system looks healthy. Use try-variants plus a timeout, and return a clear "already running" response.

Ignoring deadlock ordering

Two workers taking two advisory locks in opposite order will deadlock. PostgreSQL detects it and kills one, which is correct but still costs you a rollback. If a code path takes more than one advisory lock, always acquire in a documented, deterministic order.

Performance Tips

Advisory locks are cheap — a lock acquisition is a shared-memory operation, not a disk write — but they are not free, and the surrounding transaction matters far more than the lock itself.

• Avoid taking locks inside hot loops. One lock per batch is almost always enough; one lock per item adds round trips and shared-memory contention for no benefit.
• Mind the lock table size. The number of lock slots available scales with max_locks_per_transaction and the number of connections. Ten thousand concurrent advisory locks is a capacity problem, not a code problem.
• Keep the protected transaction short. A held advisory lock does not add row contention, but the transaction holding it does hold a connection. Connection pressure is usually the real ceiling.
• Batch the work, then lock once. If you must reconcile fifty tenants, do not take fifty locks from fifty worker processes. Take one lock for the batch, iterate inside it, and commit progress in chunks so a failure does not lose everything.
• Do not poll with try-locks in a tight loop. The right reaction to false is to exit or reschedule with backoff. A while loop that retries every 50 ms turns a mutex into a busy-wait that burns CPU and connection slots.
• Measure acquisition latency before optimising it. Because the lock manager is shared memory, acquisitions in the microsecond range are normal. If you see slow acquisitions, look at transaction length and lock waits elsewhere, not at the advisory lock call.

Security Considerations

Advisory locks are not a security boundary, and treating them as one is a genuine risk. Keep these points in mind.

• Any connected role can take any advisory lock. There is no privilege model around advisory lock keys, so a compromised or untrusted tenant cannot be prevented from grabbing a key your application relies on. In multi-tenant clusters, do not expose lock keys to user-controlled input.
• Derive keys from internal identifiers, never from raw user text. If an attacker can choose a key, they can deliberately hold it and block legitimate work — a cheap denial of service against your background jobs.
• Never trust a lock as an authorisation check. Holding a lock proves only that no one else holds the same key. It says nothing about who the user is or what they may do. Authorise first, then lock.
• Beware of user-visible lock state. Exposing "job X is running" is usually fine; exposing internal namespaces and PIDs is unnecessary information disclosure. Keep diagnostics behind admin authentication.
• Protect the ability to inspect. pg_locks reveals PIDs and query text in adjacent views. Restrict monitoring access to the operational role rather than every application account.

Deployment Notes

Most advisory-lock failures in production are deployment-shaped, not code-shaped. Check these before shipping.

Connection pooling mode

If you run PgBouncer, PgBouncer Cloud, or an application-level pooler, confirm the pool mode. Transaction pooling is incompatible with session-level advisory locks. Either use transaction-scoped locks exclusively or configure a separate session-pooled server for the handful of operations that need a stable connection. Managed PostgreSQL providers that front connections with their own pooler have the same constraint.

Rolling deploys

During a rolling deploy you will briefly run old and new versions side by side. If a lock key changes between releases, the old and new code no longer exclude each other. Either keep keys stable across the deploy or accept a short window of possible overlap, and make the work idempotent enough to survive it.

Failover and restarts

Because advisory locks vanish on failover, build the recovery path explicitly. A scheduled job that crashed while holding a lock should be re-triggered by the scheduler, not by a human. A session-locked worker should treat the lock as best-effort and verify the work it is about to do has not already been done.

Container lifecycle

Ephemeral containers that are killed with SIGKILL still release their locks when the TCP connection dies, because the backend notices the disconnect. The gap is usually seconds, not minutes. Set tcp_keepalives_idle sensibly so dead clients are detected promptly on the server side.

Timeouts in configuration

Set lock_timeout and statement_timeout on worker connections in your database config rather than per query. It is one of the cheapest reliability improvements available and it makes a stuck lock fail loudly in logs instead of silently consuming capacity.

Debugging Tips

When a job is not running and you suspect a stuck lock, work through these queries in order.

1. List every advisory lock and who holds it

SELECT  l.pid,  l.mode,  l.granted,  l.classid,  l.objid,  l.objsubid,  a.state,  a.application_name,  a.query_start,  left(a.query, 120) AS queryFROM pg_locks lJOIN pg_stat_activity a ON a.pid = l.pidWHERE l.locktype = 'advisory'ORDER BY l.pid;

Remember the encoding: with the two-integer form, classid is your namespace and objid is your derived resource hash, with objsubid = 2. If you see your namespace but never your expected hash, your key derivation is producing something different from what you assumed.

2. Find what is blocking a specific PID

SELECT pg_blocking_pids();

This returns the PIDs currently blocking the given backend, and it works for advisory locks as well as table and row locks. It is the fastest way to answer "who is in the way."

3. Check for idle holders

SELECT pid, state, now() - state_change AS idle_forFROM pg_stat_activityWHERE state = 'idle'  AND pid IN (SELECT pid FROM pg_locks WHERE locktype = 'advisory');

An idle backend holding an advisory lock is the classic leaked session lock: the code that took it never released it and the connection went back to the pool. Note the PID and trace which code path ran on the application side.

4. Confirm the lock really is yours

If you are unsure whether a session holds a lock, call the unlock function and read the boolean. false means the session did not hold it, which usually means the acquire call ran on a different connection than the release — a sign of leaking pool behaviour rather than a database problem.

5. Reproduce locally with two sessions

Open psql twice. In the first, take the lock. In the second, call the try-variant and observe the boolean, then call the blocking variant and watch it wait. Nothing replaces seeing the behaviour directly; it also makes the difference between the two scopes immediately obvious.

6. Watch for deadlock errors in logs

If PostgreSQL logs deadlock detected involving advisory locks, the fix is ordering, not retries. Add a retry for resilience, but fix the acquisition order too, or the error will keep appearing under load.

FAQ

Are PostgreSQL advisory locks replicated to standby servers?

No. Advisory locks exist only in the shared memory of the server that granted them and are not written to the write-ahead log. After a failover, the promoted standby has no record of any advisory lock, so locks held before the failover disappear. Design protected work to remain correct if two processes run concurrently during that window.

Do advisory locks work with PgBouncer?

Transaction-level advisory locks work fine with any pooling mode, because they begin and end inside a single transaction, which always runs on one connection. Session-level advisory locks require session pooling. Under transaction pooling, a session lock can be acquired on one backend while your subsequent statements run on another, which makes the lock unreliable and prone to leaking.

Are advisory locks scoped to a single database or the whole cluster?

They are managed by the cluster's lock manager rather than by a table or schema, so their scope is the server instance whose memory holds them, not a single table or row. The practical implication is that any backend connected to that instance can contend for the same key, so use a namespace by convention to keep unrelated subsystems from colliding.

What happens to an advisory lock when a connection drops?

The server releases all locks held by that backend. Session-level locks are cleaned up when the backend terminates, and transaction-level locks are released when the transaction ends. This is why advisory locks are self-healing after a crashed worker — but note that the release happens when the server notices the disconnect, which can take a few seconds.

Can a single-key lock and a two-key lock conflict?

No. The single bigint form and the two-int4 form occupy separate key spaces, and pg_locks distinguishes them with objsubid (1 for single-key, 2 for two-key). Be consistent within a subsystem so it is obvious which form you are protecting with.

How many advisory locks can I create?

The limit is governed by the lock table in shared memory, which is sized from max_locks_per_transaction and the number of connections and prepared transactions. It is large enough for named subsystem jobs and far too small for one lock per row in a big table. If your design needs thousands of live advisory locks, you are probably modelling a queue.

Should I use advisory locks instead of a job queue?

No. Advisory locks provide mutual exclusion, not work distribution. Use SELECT ... ORDER BY ... FOR UPDATE SKIP LOCKED LIMIT n to hand out rows to workers, and use advisory locks only when you need to guarantee that a specific named operation runs once at a time.

Do advisory locks block normal table queries?

No. They are attached to a numeric key, not to a table, row, or transaction ID, so ordinary SELECT, INSERT, and UPDATE statements never wait on an advisory lock. They only conflict with other sessions requesting the same advisory key.

How do I release all advisory locks held by a session?

Call pg_advisory_unlock_all(), which releases every session-level advisory lock held by the current session. Transaction-level locks cannot be released early — they end with the transaction. If a session is stuck holding a lock you cannot reach, terminating that backend releases its locks immediately.

Can advisory lock acquisition deadlock?

Yes, if two sessions acquire two advisory keys in opposite order. PostgreSQL's deadlock detector covers advisory locks and will abort one transaction with a deadlock error. Always acquire multiple locks in a fixed, documented order, and add a bounded retry around the transaction for resilience.

Conclusion

PostgreSQL advisory locks solve a narrow but extremely common problem very well: guaranteeing that a named piece of work runs in only one place at a time, using infrastructure you already operate. They cost nothing to add, they appear in pg_locks where your on-call engineer can see them, they recover automatically when a worker dies, and they come with deadlock detection that home-grown locks rarely have.

The trade-off is that they demand precision. Choose transaction scope by default and session scope only with a dedicated connection. Use try-variants so workers exit cleanly instead of hanging. Namespace your keys so unrelated features never collide, and remember the two hard limits: advisory locks never replicate, and they are per connection, which makes them incompatible with transaction pooling at session scope.

Get those rules right and you can delete a distributed lock service from your architecture for a whole class of problems — nightly reports, index rebuilds, tenant exports, webhook handling — while keeping the guarantees you actually need.

If you are building a content or product system on Laravel or Node.js and want fewer moving parts in your infrastructure, start with the database you already have. For the next step, read our guide on PostgreSQL SELECT FOR UPDATE and SKIP LOCKED to cover the work-distribution half of the problem, then review preventing overlapping Laravel queue jobs to see how these patterns fit into a real queue pipeline. Pick one job in your system today, wrap its body in a transaction-scoped advisory lock, and write the two-worker test — you will feel the difference before the end of the week.