PostgreSQL Advisory Locks: Patterns and Pitfalls

Two copies of a nightly job run at the same time because two app servers both have it in their crontab. A Rails deploy behind PgBouncer fails with ActiveRecord::ConcurrentMigrationError. A worker logs WARNING: you don't own a lock of type ExclusiveLock and nobody can say who holds the lock now. All three are advisory lock problems. PostgreSQL 18 has eight functions that take an advisory lock and three that release one, and the application decides what each lock means: one job, one customer, one migration. The server enforces nothing beyond the lock itself. This guide covers the session and transaction variants, key choice, the patterns that work, and the pitfalls that cause double runs and leaked locks.

Scope and review date: November 2026. This post covers PostgreSQL 18 (18.6 is the current minor release), with notes on PostgreSQL 19, whose general availability is planned for 19 November 2026, and PgBouncer 1.26.1. Function names, defaults, and behavior were checked against the PostgreSQL and PgBouncer documentation on 9 November 2026. The commands were not run in a lab, so rehearse them on staging.

What is an advisory lock in PostgreSQL?

An advisory lock is a lock on a number, not on a table or row. The PostgreSQL manual's section on advisory locks calls them locks "that have application-defined meanings": the server does not enforce their use, and it is up to the application to use them correctly. If one code path forgets to take the lock, nothing stops it.

The functions cover three choices: session or transaction level, exclusive or shared, and wait or not. The advisory lock function reference lists all of them.

Session locks are released with pg_advisory_unlock, pg_advisory_unlock_shared, or pg_advisory_unlock_all. Transaction locks have no release function. A shared lock conflicts only with an exclusive lock on the same key, so many readers can hold it together while one writer waits.

-- Session 1
SELECT pg_try_advisory_lock(42);   -- true: session 1 now holds key 42

-- Session 2
SELECT pg_try_advisory_lock(42);   -- false, returned at once
SELECT pg_advisory_lock(42);       -- blocks until session 1 releases key 42

Interpretation: the try variants return a boolean and never wait. The plain variants return void and wait as long as it takes, unless lock_timeout or statement_timeout cancels them. A waiting session shows wait_event_type = 'Lock' and wait_event = 'advisory' in pg_stat_activity.

Advisory locks are re-entrant: if a session already holds a lock, the manual says "additional requests by it will always succeed". Session-level requests stack, so three locks on key 42 need three releases.

Session-level or transaction-level: which should you use?

Transaction-level, unless the work cannot fit in one transaction. The manual puts the difference plainly: a session-level lock acquired in a transaction that is later rolled back "will still be held following the rollback". Transaction-level requests "behave more like regular lock requests" and are released automatically at the end of the transaction.

BEGIN;
SELECT pg_advisory_lock(42);
ROLLBACK;

SELECT count(*) FROM pg_locks
WHERE locktype = 'advisory' AND pid = pg_backend_pid();   -- 1: still held

SELECT pg_advisory_xact_lock(43);     -- autocommit: released when this statement ends

Interpretation: the first lock outlives its rollback, which is what the manual describes. The last line is a common bug: in autocommit mode each statement is its own transaction, so a transaction-level lock taken on its own protects nothing. Put BEGIN before it and the work after it.

The PgBouncer row is our reading of the PgBouncer feature map, which marks session-level advisory locks "Never" for transaction pooling and has no row for transaction-level ones. A transaction-level lock never outlives the transaction that pooling keeps on one server connection.

How do you pick an advisory lock key?

Each function takes either one bigint key or two integer keys. The function reference notes that "these two key spaces do not overlap". If one service locks 42 and another locks (0, 42), they hold two different locks and exclude nothing.

SELECT pg_advisory_lock(42);        -- one bigint key
SELECT pg_advisory_lock(0, 42);     -- two int4 keys: a different lock, granted at once
SELECT pg_advisory_lock(2001, 81234); -- namespace 2001, entity id 81234

Interpretation: the two-key form suits namespace plus id while ids fit in a 32-bit integer (up to 2,147,483,647). For bigint ids, use one key and a key range per job type.

Keys are also scoped to one database. The pg_locks reference says "advisory locks are local to each database", so two jobs connected to different databases never conflict on the same number.

Why hashtext() is a poor key source

Many examples turn a job name into a key with hashtext('job-name'). There are two problems. First, hashtext is not in the manual. It is an internal hash function, and Tom Lane wrote on the hackers list that the hash functions "are undocumented, have changed in the past, and are likely to change again in the future" (pgsql-hackers thread). The output did change between 8.3 and 8.4, as the pgvihash extension notes. If it changes again, processes on both sides of an upgrade can hold "the same" lock under two keys. Second, it returns a 32-bit integer: by the birthday bound, 10,000 names have roughly a 1.2% chance (10,000² ÷ 2³³) that two collide, and a collision makes unrelated jobs block each other.

-- Undocumented, 32-bit:
SELECT hashtext('billing:nightly');

-- Documented functions only, 64-bit, stable across versions:
SELECT ('x' || left(md5('billing:nightly'), 16))::bit(64)::bigint;

Interpretation: the second query reads the first 16 hex digits of an MD5 digest as a signed 64-bit number. Better still, keep a registry of fixed keys so everyone reading pg_locks agrees on what 7310001 means.

How do you make sure only one worker runs a job?

Have every worker try the lock without waiting. The one that gets true runs the job; the others log and exit. That covers crontabs on several app servers and several container replicas that start the same job.

If the job fits in one transaction, the transaction-level form needs no release step and works behind PgBouncer transaction pooling:

BEGIN;
SELECT pg_try_advisory_xact_lock(7310001) AS got_lock;
-- Application: if got_lock is false, ROLLBACK and exit.
-- ... the job's statements ...
COMMIT;   -- releases key 7310001

Interpretation: if the job fails, ROLLBACK releases the lock too. The cost is one transaction for the whole job.

For a job that commits in batches, use a session lock on a direct connection, as in the runbook below. If the job runs inside the database through pg_cron, you may not need a lock at all: the pg_cron README says it runs "only one instance of each specific job at a time" and queues an overlapping run.

What does a safe singleton job look like end to end?

Most double runs and stuck jobs come from the connection around the lock call. This is the order we would review for a session-level lock.

How do migrations, leader election, and per-entity work use advisory locks?

Three common variations change what the key means and how long the lock is held.

Schema migrations

Migration tools take an advisory lock so two deploys cannot migrate at once. Flyway's postgresql.transactional.lock setting defaults to true (transaction-level locks). Its docs say to set it to false, which switches to session-level locks, for statements such as CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block. That run then needs a direct or session-pooled connection. Rails takes a session-level lock, and Heroku's help article traces ActiveRecord::ConcurrentMigrationError behind PgBouncer to exactly that.

Rails 6 and later accept advisory_locks: false in database.yml. That removes the error and the protection against concurrent migrations, so run migrations from one pipeline step or over a direct connection instead.

Leader election

The leader holds a session lock on a dedicated connection; the others retry pg_try_advisory_lock every few seconds. Two facts limit this. First, the hot standby documentation says advisory locks are never WAL-logged and "relate only to the server on which they are acquired". After a failover the new primary holds none, and the first candidate to reconnect becomes leader. Second, if the leader's host dies without closing its socket, the server keeps the idle session and its lock until TCP reports the connection dead. With Linux defaults that takes about two hours of idle time plus nine probes 75 seconds apart (kernel tcp_keepalive_* defaults).

# postgresql.conf: notice dead clients in minutes, not hours
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6

Interpretation: after 60 seconds of silence the server sends up to 6 probes 10 seconds apart, so a dead client is dropped, with its locks, after roughly two minutes. client_connection_check_interval does not help, because it only checks while a query runs. The leader must also treat a lost connection as lost leadership and stop work at once.

Per-entity serialization

To let only one transaction at a time rebalance an account, lock the account's key inside the transaction. This also works when there is no row to lock yet, such as before a get-or-create insert.

BEGIN;
SELECT pg_advisory_xact_lock(2001, 81234);   -- 2001 = account rebalance, 81234 = account id
-- read balances, write transfers
COMMIT;

Interpretation: other transactions for account 81234 wait; other accounts do not. When one transaction locks two accounts, lock the lower id first. Advisory locks take part in deadlock detection, so a crossed order fails one transaction after deadlock_timeout (1 second by default).

Why do session advisory locks break behind PgBouncer transaction pooling?

In transaction pooling, PgBouncer lends a client a server connection for one transaction, and the configuration reference says server_reset_query is not used in that mode. A session lock stays on whichever server connection took it.

Two failures follow. The later release call lands on another server connection, returns false, and logs you don't own a lock of type ExclusiveLock, while the lock stays held. Worse, the next client handed the original connection can "take" the same key, because a session's repeated requests for a lock it holds always succeed. Two clients then both believe they hold it.

In session pooling the default server_reset_query is DISCARD ALL, and the DISCARD reference lists pg_advisory_unlock_all() among its steps, so a disconnecting client's locks are cleared. Send lock-holding work through a session-mode pool, which PgBouncer allows per database or per user:

; pgbouncer.ini
[databases]
app      = host=10.0.0.5 port=5432 dbname=app
app_jobs = host=10.0.0.5 port=5432 dbname=app pool_mode=session

Interpretation: web traffic keeps using app, and job runners connect to app_jobs, where a session lock stays on one server connection until the client disconnects. PgBouncer's own default pool_mode is session, but platforms and managed poolers set their own, so check the mode you actually run.

Why can a query with LIMIT take more locks than the rows it returns?

Because PostgreSQL does not promise to apply the LIMIT before it calls the lock function. The manual's own example:

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok

Interpretation: in the second query the lock function may run on rows before the LIMIT cuts them off. The application releases only the 100 ids it saw, so the extra locks dangle until the session ends. The third form fixes the row set first.

The same trap hides in queues written as WHERE pg_try_advisory_lock(id) ... LIMIT 10. For a queue, row locks are usually the better tool:

BEGIN;
SELECT id FROM jobs
WHERE status = 'ready'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process the rows, then mark them done
COMMIT;

Interpretation: each worker gets up to 10 rows that no other worker has locked, and the locks release at COMMIT or ROLLBACK.

How do you see who holds an advisory lock?

Query pg_locks for locktype = 'advisory'. A bigint key appears split across classid (high half) and objid (low half) with objsubid = 1. A two-integer key appears as classid and objid with objsubid = 2. Exclusive locks show mode ExclusiveLock and shared ones ShareLock.

Add how long each holder has been in its state, and who waits behind whom. The reference recommends pg_blocking_pids() over a self-join of pg_locks.

SELECT a.pid, a.application_name, a.client_addr, a.state,
       now() - a.state_change AS in_state_for,
       l.mode, l.granted, l.objsubid, l.classid, l.objid
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'advisory'
ORDER BY in_state_for DESC;

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock' AND wait_event = 'advisory';

Interpretation: a row with state = 'idle', granted = true, and a long in_state_for is the classic leak. For two-integer keys, a negative key shows as a large number because classid and objid are unsigned oid columns.

Only its own session can release a leaked session lock. Once application_name and client_addr confirm the session is stale, pg_terminate_backend(pid) ends it, releases all its locks, and rolls back any open transaction. Then fix the code path that skipped the release.

How many advisory locks can PostgreSQL hold?

Advisory locks share one shared memory pool with every table and index lock, sized by max_locks_per_transaction and max_connections. The manual warns that if it is exhausted "the server will be unable to grant any locks at all", and puts the ceiling for advisory locks "typically in the tens to hundreds of thousands depending on how the server is configured".

When the pool is full, any statement that needs a new lock, including ordinary queries, fails with ERROR: out of shared memory and the hint You might need to increase "max_locks_per_transaction". Locking one key per row of a large table is the usual cause.

SHOW max_locks_per_transaction;   -- 64 by default in PostgreSQL 18

SELECT pid, count(*) AS advisory_locks
FROM pg_locks
WHERE locktype = 'advisory'
GROUP BY pid
ORDER BY advisory_locks DESC
LIMIT 10;

Interpretation: one pid holding thousands of advisory locks is a design problem; lock a parent key (the batch or the customer) instead of every row. Raising max_locks_per_transaction needs a restart, and the lock management settings page says standbys must use the same or a higher value than the primary.

Frequently asked questions

Key takeaways

Review your PostgreSQL locking with JusDB

JusDB's PostgreSQL consulting team can review how your jobs, migrations, and workers use advisory locks, check them against your PgBouncer pool modes, and help plan monitoring for leaked locks and lock-table pressure.

Bring your PostgreSQL and pooler versions and the code paths that take advisory locks. Your team approves every production change.

Related reading

• pg_cron: scheduling automated jobs inside PostgreSQL
• PgBouncer connection pooling: transaction mode setup and tuning
• Zero-downtime schema changes in PostgreSQL: safe DDL without locking
• PostgreSQL documentation: Advisory Locks