Skip to content

Using Postgres Advisory Locks from asyncio

When the work you need to serialise writes to Postgres, the database already has a lock manager, and advisory locks expose it for arbitrary application keys: "only one worker may process account 42", "only one replica runs the nightly rollup". They are fast, need no extra infrastructure, and come in two scopes with very different failure behaviour. Tested on PostgreSQL 17: a transaction-scoped lock held by one connection blocked a competitor and was released automatically at commit; a blocking acquisition with lock_timeout = 200ms gave up after 202 ms with LockNotAvailableError; and session-scoped locks taken on pooled connections behaved differently by driver — asyncpg's pool released the lock when the connection went back to the pool, while psycopg's pool kept it held, so the lock outlived the code that took it. This guide shows how to use each scope safely from asyncio.

Prerequisites

1. Prefer transaction-scoped locks

pg_try_advisory_xact_lock(key) takes a lock that lives exactly as long as the current transaction. Commit or rollback releases it — including when the process crashes and the connection drops:

import asyncpg


async def process_account(pool: asyncpg.Pool, account_id: int) -> bool:
    async with pool.acquire() as conn, conn.transaction():
        got = await conn.fetchval("select pg_try_advisory_xact_lock($1)", account_id)
        if not got:
            return False                              # another worker is on it
        await recompute_balance(conn, account_id)    # writes in the same transaction
        return True

Measured: while one connection held key 7 inside its transaction, a second connection's pg_try_advisory_xact_lock(7) returned false; after the first committed, the second got it. Because the lock and the writes share one transaction, there is no window in which a stale holder can write — a worker that loses its connection loses its transaction, lock and uncommitted writes together. That is stronger than any external lock can offer.

Keys are 64-bit integers (or two 32-bit integers). Map string identifiers with a stable hash and namespace them so features cannot collide: hashtext('rollup:' || $1) in SQL, or a fixed-width hash in Python.

Verify: two workers racing for the same account — one processes it, the other gets False immediately.

Advisory lock scopes compared A grid of 5 rows by 3 columns. Advisory lock scopes compared property xact lock session lock released by commit or rollback unlock or disconnect holder crashes released with the connection released with the connection pooled connection returned already released asyncpg: released, psycopg: kept open transaction needed yes, for the whole work no best for short work on the same DB long work, dedicated connection Measured on PostgreSQL 17 with asyncpg 0.31 and psycopg 3.3 with psycopg-pool 3.3.

2. Bound how long you wait for a lock

pg_try_advisory_* returns immediately. The blocking forms, pg_advisory_xact_lock and pg_advisory_lock, wait — by default forever. Bound them with lock_timeout scoped to the transaction:

async def with_lock_waiting(pool, key: int, work, wait_ms: int = 2000):
    async with pool.acquire() as conn, conn.transaction():
        await conn.execute(f"set local lock_timeout = '{int(wait_ms)}ms'")
        try:
            await conn.execute("select pg_advisory_xact_lock($1)", key)
        except asyncpg.exceptions.LockNotAvailableError:
            raise TimeoutError(f"advisory lock {key} busy for {wait_ms} ms") from None
        return await work(conn)

Measured: with lock_timeout = '200ms', the wait ended after 202 ms with LockNotAvailableError. set local confines the setting to this transaction, so it does not leak to the next user of the pooled connection. Prefer this server-side bound to wrapping the call in asyncio.timeout(): cancelling a query client-side leaves the backend to notice the cancel request, while lock_timeout ends the wait inside Postgres cleanly. The general approach is in setting per-attempt and total timeouts for retries.

Verify: while another connection holds the key, the call fails after the configured wait, not later.

3. Use session locks only on a dedicated connection

Session-scoped locks (pg_try_advisory_lock, released by pg_advisory_unlock or disconnect) suit long work that should not hold a transaction open — a migration runner, a leader that holds a lock for hours. They are tied to a connection, and that is where pools bite:

# measured behaviour when a session lock is taken on a pooled connection and the
# connection is returned WITHOUT unlocking:
#   asyncpg pool:  lock released (the pool resets connections on release)
#   psycopg pool:  lock still held by the idle pooled connection

asyncpg resets a connection when it is released to its pool, and that reset unlocks advisory locks — so the lock silently ends when your async with pool.acquire() block exits, even if you meant to keep it. psycopg's pool, in the same test, returned the connection with the lock still held: a lock that nobody's code is holding, blocking everyone until that idle connection is closed. Neither is what you want. Hold session locks on a connection you own outright:

class SessionLock:
    def __init__(self, dsn: str, key: int) -> None:
        self.dsn, self.key = dsn, key
        self.conn: asyncpg.Connection | None = None

    async def __aenter__(self) -> "SessionLock":
        self.conn = await asyncpg.connect(self.dsn)          # dedicated, not from the pool
        if not await self.conn.fetchval("select pg_try_advisory_lock($1)", self.key):
            await self.conn.close()
            raise RuntimeError(f"lock {self.key} is held elsewhere")
        return self

    async def __aexit__(self, *exc) -> None:
        try:
            await self.conn.execute("select pg_advisory_unlock($1)", self.key)
        finally:
            await self.conn.close()                           # disconnect releases it anyway

The dedicated connection also keeps the lock from consuming request-pool capacity. If the process dies, the connection drops and Postgres releases the lock.

Verify: query pg_locks where locktype = 'advisory' after your code releases; no lock remains, and none is held by an idle pooled connection.

A session lock on a pooled psycopg connection A sequence of 5 messages between 4 participants. A session lock on a pooled psycopg connection worker code psycopg pool Postgres other worker connection() pg_try_advisory_lock(43): true return connection (no unlock) idle connection still holds lock 43 pg_try_advisory_lock(43): false Measured: the lock outlived the code that took it. asyncpg's pool reset the connection and released it instead.

4. Debug who holds a lock

When a lock seems stuck, Postgres can tell you exactly who holds it:

HOLDERS = """
select l.objid as key, a.pid, a.application_name, a.state, a.query_start, left(a.query, 80) as query
from pg_locks l
join pg_stat_activity a on a.pid = l.pid
where l.locktype = 'advisory' and l.granted
"""


async def advisory_holders(conn) -> list[dict]:
    return [dict(r) for r in await conn.fetch(HOLDERS)]

Set application_name in each service's connection settings so the holder is identifiable. An entry whose state is idle and whose query is a long-finished statement is the pooled-connection leak from step 3. For keys built from two 32-bit integers, read classid and objid together. As a last resort, pg_terminate_backend(pid) drops the holder's connection and releases its locks — a blunt tool, appropriate only once you have identified the holder.

Verify: the holders query names your service and the expected key while a lock is held, and returns nothing after release.

5. Choose advisory locks or row locks

Advisory locks protect arbitrary keys. When the thing you are serialising is a row, the row lock is simpler and enforced by the data itself:

async def claim_next_job(conn) -> dict | None:
    return await conn.fetchrow("""
        select * from jobs
        where status = 'queued'
        order by created_at
        for update skip locked
        limit 1
    """)

FOR UPDATE SKIP LOCKED lets many workers claim different rows concurrently without blocking each other, which is the basis of building a durable job queue on Postgres with asyncio. Advisory locks earn their place when there is no single row to lock — "the nightly rollup", "migrations", "account 42 across five tables".

Verify: for each advisory lock, confirm there is no natural row lock that would protect the same thing.

Which Postgres lock fits? A decision on What are you serialising with 3 outcomes. Which Postgres lock fits? What are you serialising? specific rows FOR UPDATE (SKIP LOCKED) the data enforces it a key, short work xact advisory lock auto-release at commit a key, long-held session lock, own connection never from the pool Let the database do the locking; just keep the lock's lifetime and the connection's in step.

Verification

Advisory locking is safe when:

  • Short work uses transaction-scoped locks that release at commit or rollback.
  • Blocking acquisitions are bounded with set local lock_timeout.
  • Session locks run on dedicated connections, never on pooled ones.
  • pg_locks shows no advisory locks held by idle connections after work completes.

Diagnostic Hook: periodically run the holders query and export the count of advisory locks held by connections in the idle state for longer than a minute. Any non-zero value is a leaked session lock, almost always from the pooled-connection trap; the application_name identifies which service did it.

Pitfalls & edge cases

  • Session locks on pooled connections. asyncpg silently releases them on return; psycopg's pool silently keeps them.
  • Unbounded blocking acquisition. Without lock_timeout, a stuck holder blocks callers forever.
  • Colliding keys across features. Namespace keys before hashing them to integers.
  • Long transactions for long work. An xact lock keeps a transaction open; switch to a session lock on a dedicated connection.

Frequently Asked Questions

How do I use Postgres advisory locks with asyncpg?

Inside a transaction, call select pg_try_advisory_xact_lock(key); it returns true if you got the lock, and the lock is released automatically at commit or rollback. For long-held locks use pg_try_advisory_lock on a dedicated connection.

Are advisory locks released when a pooled connection is returned?

It depends on the driver. In testing, asyncpg's pool released session advisory locks on return because it resets connections, while psycopg's pool kept the lock held by the idle connection. Do not hold session locks on pooled connections.

How do I add a timeout to pg_advisory_lock?

Run set local lock_timeout = '2000ms' in the same transaction before the blocking call. Postgres then raises lock_not_available, LockNotAvailableError in asyncpg, after that wait.

Should I use advisory locks or SELECT FOR UPDATE?

FOR UPDATE when the thing you are serialising is a specific row; advisory locks when the key does not correspond to one row, such as a scheduled job or work spanning several tables.