Needed to ensure that at most one background worker processes any given user at a time. My first instinct was a SELECT ... FOR UPDATE on a row, or a separate locks table with an upsert. Both have drawbacks.

Postgres has pg_try_advisory_lock, which is an application-level lock identified by an integer (or two integers). No table needed, no row needed. Just claim a lock by ID.

SELECT pg_try_advisory_lock(hashtext($1)::bigint);

Returns true if you got the lock, false if someone else holds it. The hashtext($1)::bigint is the convention for locks keyed by string (user ID, resource name, whatever).

import psycopg

def with_user_lock(user_id: str, fn):
    with conn.cursor() as cur:
        cur.execute("SELECT pg_try_advisory_lock(hashtext(%s)::bigint)", (user_id,))
        got = cur.fetchone()[0]
        if not got:
            return None  # someone else is working this user
        try:
            return fn()
        finally:
            cur.execute("SELECT pg_advisory_unlock(hashtext(%s)::bigint)", (user_id,))

The lock is per-session. If the session dies, the lock is released. No stuck locks on crashed workers. No table bloat. No vacuum needed.

Caveats:

  • The integer key collides across different lock uses. If one code path takes advisory_lock(123) and another takes advisory_lock(123) for a completely different reason, they’ll block each other. Use two-int form (pg_advisory_lock(namespace_id, resource_id)) to separate concerns.
  • They don’t survive restarts. That’s a feature, not a bug, but worth knowing.
  • They’re held for the connection’s lifetime unless explicitly released.

Saved me from building my own “who’s working on this user” table. Hard to overstate how much nicer this is.