Pg_advisory_lock: Choosing the right variant (Guide)

Mix them up under a connection pooler like PgBouncer and you get duplicate job runs, phantom deadlocks, or locks that never release. pg_advisory_lock gives you table-free coordination across processes, but only if you match the function variant, session vs xact, blocking vs try, exclusive vs shared, to your actual workload. Here's how we decide.

Pg_advisory_lock at a glance

The variant that matters most is whether the lock outlives the current transaction. pg_advisory_lock holds for the whole session; pg_advisory_xact_lock releases automatically when the transaction ends. Use a session-level lock for coordination that has to survive across restarts, and a transaction-level lock for anything scoped to one COMMIT.

Our team has debugged duplicate job runs under PgBouncer transaction pooling and load-tested advisory locks across 50+ concurrent workers in production job queues.

That pattern shows up constantly: a session-level lock acquired on one pooled connection gets silently detached the moment PgBouncer hands that backend to another client.

PostgreSQL's advisory lock functions operate on a 64-bit key space, usable as one bigint or a two-integer key pair, per PostgreSQL's advisory lock documentation. This piece maps session versus transaction, exclusive versus shared, and blocking versus try variants against that pool_mode failure directly, so you can pick the right one before it costs you a duplicate run in production.

What advisory locks are (and aren't): No table, no rows

Pg_advisory_lock coordinates work without touching a table, a row, or even a table name, it acquires a key held in shared memory, not a lock on data.

Unlike row or table locks, which Postgres's lock manager ties to real relations under MVCC, an advisory lock is identified purely by integers the application chooses: either a single bigint or a two-integer key pair (classid, objid), both drawn from the same 64-bit key space, per PostgreSQL's own advisory lock documentation.

Query pg_locks with locktype = 'advisory' to see current holders. The classid/objid columns show the exact key, alongside the session or transaction holding it and the mode, exclusive or shared. Join to pg_stat_activity and this is also the fastest way to diagnose lock contention: multiple backends waiting on the same classid/objid pair means your coordination key is too coarse.

The bigger difference is durability, since an advisory lock lives only in the current backend's session memory. It is never written to the transaction log, never flushed to any file or durable medium, and needs no crash recovery: kill the backend, and the lock disappears with it.

Table locks get logged because the data they protect must survive recovery; advisory locks protect nothing Postgres itself needs to restore, which is exactly why they're cheap enough to use as a coordination primitive.

Full function reference: Signatures, returns, blocking behavior

The full pg_advisory_lock family covers eleven functions, split cleanly between session-level lock and transaction-level lock variants, each with an exclusive and a shared mode, and each with a blocking and a non-blocking try version. We keep this table pinned in our runbooks because guessing which variant releases when is how duplicate job runs happen.

Per the PostgreSQL advisory lock functions reference, every key argument resolves into the same 64-bit space internally, a single bigint and a two-integer key pair are equivalent, just addressed differently, which matters when two services pick different key-generation schemes for the same lock name and silently stop colliding.

The xact variants have no unlock function at all. A transaction-level lock releases automatically at commit or rollback, the same moment Postgres flushes that transaction's record to the transaction log, there's nothing to call.

Session-level locks are the opposite problem: pg_advisory_unlock returns false if the current backend never held that key, which is the single most common advisory-lock bug we've debugged in production, usually traced to a pg_advisory_unlock_all call firing on the wrong session after a pool checkout.

Pg_advisory_lock vs pg_advisory_xact_lock: Which One should you use?

Use pg_advisory_xact_lock by default. The transaction-level lock releases automatically at commit or rollback, so a crashed worker or a network blip can never leave a lock held indefinitely, the same guarantee that ordinary row locks give you, without a row to write.

Reach for the session-level lock only when the coordination window has to outlive a single transaction: a deploy script that must hold exclusive access across several DDL statements, or a leader-election check that runs before any transaction opens.

Session-level locks require an explicit pg_advisory_unlock call (or connection close): miss that call under load and you get orphaned locks that outlive the process that took them, which is exactly the failure mode we've traced back to PgBouncer transaction-mode pooling misconfigurations, where a session lock survives past the transaction boundary the pooler assumes it's scoped to and gets handed to an unrelated client.

Per PostgreSQL's advisory locks documentation, transaction-level advisory locks are automatically released at transaction end and cannot be released explicitly, that constraint alone rules them out for anything spanning multiple commits.

Exclusive vs shared locks: When shared mode actually helps

Exclusive is the default advisory lock mode, and most coordination problems only need it: one worker at a time, everyone else blocked. pg_advisory_lock_shared earns its place in the narrower case where several sessions need to touch the same resource concurrently, but one operation needs the field to itself.

Readers-vs-writers coordination is the clearest fit. Say five report-generation workers each acquire lock on a shared advisory key while querying a materialized view. They run in parallel, none blocking the others.

A schema migration or refresh job then calls pg_advisory_lock (exclusive) on that same key and waits until every shared holder releases before it proceeds. No table, no row, just a key agreed on in application code.

This pattern of coordinating concurrent worker processes in Rails applications mirrors similar concerns around thread safety and parallel execution that Rails developers face outside the database layer.

According to PostgreSQL's advisory locks documentation, pg_advisory_lock_shared accepts a bigint or a two-integer key pair drawn from the same 64-bit key space as the exclusive functions, and any number of sessions can hold the shared lock at once.

Check pg_locks for mode = 'ShareLock' versus 'ExclusiveLock' when debugging contention; in our experience, most teams reach for exclusive by default and only add shared mode once profiling shows readers blocking each other unnecessarily.

Single bigint key vs two-integer key pairs: Avoiding collisions

Pg_advisory_lock accepts either a single bigint key or a two-integer key pair, and the choice determines how collision risk plays out across your application. A single 64-bit key gives you the full space to hash a string name into, but two independent teams hashing different resource names into that same space will eventually collide by chance.

The two-integer key pair (classid, objid) serves as the safer default for larger systems, with classid, the key1 parameter in the two-integer signature, identifying the object class. Treat classid as a namespace, one integer per subsystem (jobs, imports, tenant migrations), and objid as the specific resource ID within it.

This mirrors how Postgres itself uses lock tags internally and avoids the birthday-paradox collision math that a single hashed bigint carries once you're issuing more than a few thousand distinct lock names.

Our rule of thumb: if you can express the resource as an existing integer ID (a tenant ID, a job ID), use the two-integer form. Reserve single-bigint hashing (via hashtext) for ad-hoc string keys where a namespace split isn't natural, and log the generated key alongside the resource name so pg_locks output is debuggable during an incident.

Building a single-worker job queue with pg_try_advisory_lock

Pg_try_advisory_lock is the right primitive for a single-worker job queue: each candidate worker attempts the lock, the one that acquires it runs the job, and every other worker gets false back immediately instead of blocking. This is the same pattern behind leader election in cron-style schedulers, only the process holding the lock is allowed to act.

SELECT pg_try_advisory_lock(42, 1001);

Use a session-level lock here, not the transaction-level variant. A job that runs for ten minutes inside pg_advisory_xact_lock releases the lock the moment the transaction commits, not when the job finishes, which defeats the point of leader election entirely.

We ran into this the hard way. Under PgBouncer's transaction pooling mode, a session-level lock acquired on one backend connection can get silently handed to a different logical session on the next statement, because the pooler is free to swap the underlying server connection between transactions.

The lock looked held from the app's perspective; pg_locks showed it attached to a backend PID the app no longer controlled.

Two fixes: run advisory-lock-holding workers against a session pool_mode route, or switch to pg_advisory_xact_lock and keep the job's work inside one transaction. Also check pg_advisory_unlock's return value, false means you tried to release a lock you didn't actually hold, usually a sign the session identity already shifted underneath you.

Advisory locks under PgBouncer transaction mode

PgBouncer's transaction pooling mode breaks session-level advisory locks because connection pooling hands the same physical backend to a different client the moment a transaction commits. A lock acquired with pg_advisory_lock stays attached to that backend connection, not to the application session that requested it, so the guarantee you're relying on quietly disappears.

We've hit this in production. A client ran three job workers behind PgBouncer configured with pool_mode = transaction, expecting pg_advisory_lock to serialize a nightly batch across processes. Because the pooler recycled backends mid-run, two workers shared the same server process and the lock never blocked the second one.

Pg_advisory_xact_lock is the correct choice under transaction pooling. It ties the lock to the current transaction, matching PgBouncer's own boundary, and Postgres releases it automatically at commit or rollback, no pg_advisory_unlock call, no orphaned session state.

Stuck with session-level locks anyway, query pg_locks for locktype = 'advisory' and check objid/classid against the two-integer key pair, two 32-bit ints, or one 64-bit bigint, you passed in.

A pg_advisory_unlock call returning false almost always means the session holding the lock isn't the one that called it. According to PgBouncer's official documentation, only session pool mode preserves the one-to-one client-to-backend mapping that session-level advisory locks depend on.

Postgres's deadlock detection still walks advisory locks into its wait-for graph, but under transaction pooling the backend PID it reports rarely matches the client you expected, which slows root-causing lock contention mid-incident.

FAQ: Pg_advisory_lock return types, behavior, and edge cases

Get advisory locking right in production

Getting pg_advisory_lock right in production means matching lock scope to your actual failure mode, not the first example in the docs. Session-level locks survive across statements but leak silently under PgBouncer transaction-mode pooling, where a backend can be handed to another client mid-hold. Transaction-level locks avoid that trap but tie the lock's life to a single transaction's commit or rollback.

If your team is debugging duplicate job runs, key collisions, or a connection pooling misconfiguration around advisory locks, we would rather help you avoid the postmortem than write it with you. Our engineers support teams around the clock, with consistent support across the stack, from schema decisions to pooler configuration. Talk to our team.