Atomic Version Counters Without a Sequence — pg_advisory_xact_lock
PostgreSQL advisory locks cost nothing to create and release automatically with the transaction — a better fit than a per-tenant sequence for monotonic counters.
The problem
A polling endpoint needed a monotonically increasing version number per tenant. Simple enough: SELECT COALESCE(MAX(version), 0) + 1 FROM instructions WHERE target = :t, then INSERT with that value. Code review flagged it immediately: two concurrent POSTs to the same tenant can both read the same MAX before either inserts. Both rows get the same version. The poller sees one, skips the other forever.
The obvious fix — a dedicated sequence per tenant — means a DDL operation every time a new tenant appears. The slightly less obvious fix — a single global sequence — serializes all writes across all tenants. Neither felt right for what was effectively a per-tenant counter with low write volume.
The approach
PostgreSQL advisory locks are session-or-transaction-scoped mutex locks keyed by a bigint. They cost nothing to create and nothing to destroy — they exist only as a flag in shared memory. pg_advisory_xact_lock(key) acquires a transaction-scoped lock that automatically releases when the transaction commits or rolls back.
The implementation:
import hashlib as _hashlib
lock_key = int(_hashlib.md5(tenant.encode()).hexdigest()[:15], 16) & 0x7FFFFFFFFFFFFFFF
db.execute(sa_text("SELECT pg_advisory_xact_lock(:k)"), {"k": lock_key})
next_version = db.execute(
sa_text("SELECT COALESCE(MAX(version), 0) + 1 FROM instructions WHERE target = :t"),
{"t": tenant},
).scalar_one()
instr = Instruction(..., version=next_version)
db.add(instr)
db.commit()The MD5 hash maps the tenant identifier to a stable 63-bit integer (the mask keeps it in the signed bigint range Postgres expects). Two concurrent writes for "tenant-a" hash to the same key and queue behind each other. Two writes for "tenant-a" and "tenant-b" get different keys and run in parallel. The lock held at pg_advisory_xact_lock time is released automatically at db.commit() — no explicit unlock needed.
What I learned
The neat property here is that the lock and the counter live in the same transaction. If the INSERT fails and rolls back, the lock releases and the version number was never consumed. There's no tombstone, no gap in the sequence, no cleanup needed. Compare this to a separate sequence table where a rollback leaves a gap you then have to reason about in the polling filter.
The MD5 approach has one footgun: two different tenant names that happen to collide in the 63-bit hash space would serialize unnecessarily. At our current scale (fewer than 20 tenants) and with 63 bits of key space, a collision is not a realistic concern. At scale, a dedicated lookup table keyed by tenant name would be the right next step — but that's a schema change, not a locking strategy change.
The other thing worth noting: this pattern only works because the advisory lock and the MAX query are in the same transaction. If you split them — lock in one transaction, query in another — you've reproduced the race. Keep them together.