What this usually means
An advisory lock is held by a database session that either hasn't released it explicitly or was terminated abnormally without the lock being cleaned up. Unlike row locks, advisory locks are not automatically released on transaction end unless acquired with pg_try_advisory_xact_lock(). If acquired with pg_advisory_lock() or pg_try_advisory_lock(), they persist until the session ends or pg_advisory_unlock() is called. Common causes: connection pool reuse without explicit unlock, application code that catches exceptions without unlocking in a finally block, or a long-running transaction that acquired a lock and never committed.
The first ten minutes — establish facts before touching code.
- 1SELECT locktype, database, relation, pid, mode, granted, fastpath FROM pg_locks WHERE locktype = 'advisory';
- 2SELECT pid, state, wait_event, query_start, state_change FROM pg_stat_activity WHERE wait_event = 'AdvisoryLock';
- 3Check pg_stat_activity for idle connections that have been open for hours with old query_start timestamps.
- 4Review application logs for any uncaught exceptions during critical sections that use advisory locks.
- 5If using PgBouncer, run SHOW POOLS; and check the number of server connections; restart pools if necessary.
- 6Kill the offending backend: SELECT pg_terminate_backend(pid) for the PID holding the lock.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchpg_locks: filter locktype = 'advisory' and granted = true
- searchpg_stat_activity: check wait_event = 'AdvisoryLock', and also idle connections with old query_start
- searchApplication code: search for pg_advisory_lock, pg_try_advisory_lock, and verify they have matching unlock calls in finally blocks
- searchConnection pooler config (PgBouncer): check pool_mode, server_reset_query, and max_client_conn
- searchApplication server logs: look for OOM kills, SIGTERM, or connection timeout errors
- searchpg_stat_statements: find queries that acquire advisory locks and their mean time
Practical causes, not theory. These are the things you will actually find.
- warningApplication acquired lock with pg_advisory_lock() but never called pg_advisory_unlock() on exception paths
- warningConnection pooler (PgBouncer) reuses a session that still holds an advisory lock from a previous transaction
- warningBackend was killed by OOM killer or forced restart, leaving orphaned locks that persist across pooler connections
- warningLock acquired with pg_advisory_lock() inside a transaction that never commits/rollbacks (idle-in-transaction)
- warningMultiple application threads share the same session (e.g., in a single-threaded pool) causing lock contention
- warningUsing pg_advisory_lock() instead of pg_try_advisory_xact_lock() when transaction-scoped locking is sufficient
Concrete fix directions. Pick the one that matches your root cause.
- buildAlways use pg_try_advisory_xact_lock() instead of pg_advisory_lock() unless you need session-level persistence
- buildWrap lock acquisition in a try-finally block that calls pg_advisory_unlock() for every key
- buildSet a statement_timeout or lock_timeout to prevent indefinite waits
- buildConfigure PgBouncer's server_reset_query to execute commands that release locks (e.g., DISCARD ALL; RESET ALL;)
- buildImplement a watchdog that periodically checks pg_locks and terminates stale sessions
- buildUse a dedicated connection for long-lived locks and never return it to the pool
A fix you cannot prove is a guess. Close the loop.
- verifiedSELECT pg_advisory_unlock_all() on the affected session and confirm locks disappear from pg_locks
- verifiedRun the application with lock acquisition logging and verify unlock calls in all code paths
- verifiedSimulate a connection pool recycle and check that no locks leak via pg_locks
- verifiedSet log_lock_waits = on and watch for 'waiting for lock' messages in logs
- verifiedUse a test harness that kills backends and verifies locks are released on reconnect
- verifiedMonitor pg_locks count over time during load testing to ensure no accumulation
Things that make this bug worse or harder to find.
- warningAssuming transaction end releases session-level advisory locks (it doesn't)
- warningUsing random large integers as lock keys that may collide with other applications
- warningRelying on application-side timeouts without a server-side lock_timeout
- warningKilling backends without checking what else that session is doing (may cause data loss)
- warningIgnoring PgBouncer transaction pooling mode where sessions are destroyed after each transaction, but locks may survive if using session-level locks
- warningNot logging lock IDs, making post-mortem analysis impossible
The Midnight Ticketmaster Meltdown
Timeline
- 00:15PagerDuty alerts: 50% of checkout requests failing with 'could not obtain lock' errors.
- 00:20I query pg_locks and find 47 advisory locks held by 10 backends, all granted=true.
- 00:25pg_stat_activity shows those backends are idle, some for over 2 hours.
- 00:30I check PgBouncer pools: 10 server connections, all in use, but no actual queries running.
- 00:35Application developers confirm a recent deploy added advisory locks for inventory reservation.
- 00:40I kill all idle backends holding locks: SELECT pg_terminate_backend(pid) for 10 PIDs.
- 00:45Locks released, checkout recovers immediately.
- 01:00Root cause found: code used pg_advisory_lock() in a Django transaction.atomic block; on exception, the lock was not released because the transaction was rolled back but the session-level lock persisted.
At 12:15 AM, our ticketing platform started failing checkout requests. The error was 'could not obtain lock' after a 3-second wait. I checked pg_locks and saw 47 advisory locks, all granted, held by 10 backends that were idle. Those backends had been idle for hours, meaning the locks were stuck. The application had a recent deploy that added advisory locks for inventory reservation during checkout.
I connected to PgBouncer and saw all 10 server connections were active but idle. The application's connection pool was also full, with threads waiting for a connection. Killing the 10 backends that held the locks immediately freed them, and checkout started working again. But I needed to find why the locks were never released.
The developers had used pg_advisory_lock() inside a Django transaction.atomic block. When an exception occurred (e.g., payment timeout), the transaction was rolled back, but the session-level lock was not released because pg_advisory_lock() is not transaction-scoped. The connection was returned to PgBouncer still holding the lock, and subsequent requests on that connection could not acquire the same lock. We fixed it by switching to pg_try_advisory_xact_lock() and adding a finally block to unlock in case of session-level usage.
Root cause
Application used session-level pg_advisory_lock() inside a transaction that could roll back on exception, leaving locks orphaned on pooler connections.
The fix
Replaced pg_advisory_lock() with pg_try_advisory_xact_lock() for transaction-scoped locking, and added explicit unlock calls in finally blocks for session-level locks.
The lesson
Always match lock scope to transaction scope when using advisory locks with connection poolers. Session-level locks are dangerous in pooled environments.
PostgreSQL offers two flavors of advisory locks: session-level (pg_advisory_lock, pg_try_advisory_lock) and transaction-level (pg_advisory_xact_lock, pg_try_advisory_xact_lock). The critical difference is that session-level locks persist until explicitly unlocked or the session ends, while transaction-level locks are automatically released when the transaction completes (commits or rolls back).
When using connection poolers like PgBouncer in transaction mode, sessions are reused across transactions. If you acquire a session-level lock and the transaction ends, the lock remains on the session. The next transaction on that same session may not expect the lock, or worse, may fail to acquire a conflicting lock. This is the most common cause of 'lock not releasing' in production.
Rule of thumb: if you only need the lock for the duration of a single transaction, use pg_try_advisory_xact_lock(). If you absolutely need a lock that spans multiple transactions, you must manage the session lifecycle carefully, possibly using a dedicated connection pool.
PgBouncer, the most common PostgreSQL connection pooler, has two main pooling modes: session and transaction. In session mode, the client connection is pinned to a server connection for its entire lifetime, so session-level locks behave as expected. In transaction mode, the server connection is released back to the pool after each transaction. If a session-level lock was acquired during a transaction, it persists on that server connection even after the transaction ends.
When the server connection is reused for a different client, that client inherits the lock. This can cause deadlocks or 'could not obtain lock' errors because the lock key is still held. To mitigate, you can set server_reset_query in PgBouncer to run commands like DISCARD ALL; RESET ALL; which release session-level resources. However, DISCARD ALL does not release advisory locks unless you explicitly call pg_advisory_unlock_all().
I recommend adding pg_advisory_unlock_all() to the reset query if you use session-level locks. For example: server_reset_query = 'DISCARD ALL; SELECT pg_advisory_unlock_all();'. This ensures that every time a connection is returned to the pool, any orphaned advisory locks are released.
When you suspect an advisory lock leak, start with pg_locks: SELECT * FROM pg_locks WHERE locktype = 'advisory';. This shows all advisory locks, including the classid, objid, and pid. The granted column tells you if the lock is held (true) or waiting (false). Focus on held locks that are associated with idle backends.
Cross-reference with pg_stat_activity to identify the backend state: SELECT pid, state, wait_event, query_start FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE locktype='advisory' AND granted=true);. If the state is 'idle' or 'idle in transaction' and query_start is old, that session likely leaked the lock.
You can also look for backends waiting on advisory locks: SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event = 'AdvisoryLock';. These are sessions blocked trying to acquire a lock. The blocker will be in pg_locks with granted=true. Once you identify the blocker, you can terminate it with pg_terminate_backend(pid) after understanding the impact.
The most robust pattern is to always use transaction-level locks unless you have a compelling reason not to. For example, in Python with psycopg2, use: cur.execute('SELECT pg_try_advisory_xact_lock(%s)', [key]) and check the return value. If it returns false, handle the failure gracefully.
If you must use session-level locks, wrap acquisition and release in a context manager that guarantees release even on exceptions. In Python, use a 'with' block that calls unlock in __exit__. Example: with advisory_lock(conn, key): # critical section. The context manager should catch exceptions, call pg_advisory_unlock(), and re-raise.
Also, set a lock_timeout in PostgreSQL to avoid indefinite waits: SET lock_timeout = '5s';. This ensures that if a lock is stuck, the session will error out rather than hang forever. Combine this with a retry mechanism in the application.
Frequently asked questions
Does transaction rollback release session-level advisory locks?
No. Session-level advisory locks (pg_advisory_lock, pg_try_advisory_lock) are not affected by transaction boundaries. They persist until explicitly released with pg_advisory_unlock() or until the session ends. Only transaction-level advisory locks (pg_advisory_xact_lock) are released on commit or rollback.
How do I list all advisory locks currently held in PostgreSQL?
Run SELECT locktype, classid, objid, objsubid, pid, mode, granted FROM pg_locks WHERE locktype = 'advisory';. The classid and objid together form the 64-bit lock key. The granted column indicates whether the lock is held (true) or waiting (false).
Can I release all advisory locks for a session without knowing the keys?
Yes. Execute SELECT pg_advisory_unlock_all(); in the target session. This releases all advisory locks held by that session. Note that this only works for session-level locks; transaction-level locks are released automatically at transaction end.
What happens to advisory locks when a connection is killed?
When a backend is terminated (via pg_terminate_backend, kill -9, or crash), all its advisory locks are automatically released. However, if you are using a connection pooler like PgBouncer, the underlying server connection may not be killed immediately; it goes back to the pool. If the lock was session-level, it persists on that server connection until the pooler destroys it or you explicitly unlock.
Should I use pg_advisory_lock or pg_try_advisory_lock?
Use pg_try_advisory_lock if you want to avoid blocking and handle failure immediately. It returns true/false. Use pg_advisory_lock if you want to block until the lock is available. In most production scenarios, pg_try_advisory_xact_lock is preferred because it is transaction-scoped and non-blocking. Session-level blocking locks (pg_advisory_lock) are rarely needed and dangerous with poolers.